Enhance OrmLite with common data usage patterns
Add better support for Joins and fetching related records.
Support for AutoQuery has been added in v4.0.23 which enables instant querying support on RDBMS tables behind clean self-describing APIs, full docs for AutoQuery is available at:
https://github.com/ServiceStack/ServiceStack/wiki/Auto-Query
Support for joins have been added to typed SqlExpressions in v4.0.22:
https://github.com/ServiceStack/ServiceStack/blob/master/release-notes.md#typed-sql-expressions-now-support-joins
Going to mark off this broad feature as completed as we’ve just completed what we set out to do for this. Please open up new feature requests for more specific features.
Thanks!
-
Bastien commented
I still want to append another requirement related to my former needs and correct a grammar in my former post in the last sentence: "not A" should mean "not D"
It would be ok when the GET scenario would return D BUT then I should be able to inject/use somehow (lambda? ) a filter on the D-collection/query because I only want all D entites where a certain property is true. :) It seems to me the attributes are too less flexible. Probably you somehow go with fluent style ;-)
-
Bastien commented
Hello Demis,
I would like to have more flexible Reference attributes.I have the entity A.cs with 3 generic lists B.cs C.cs and D.cs. All annotated with the Reference attribute.
I have a SAVE and GET scenario.
In the SAVE scenario I do this:
A.listB.AddRange(listB);
A.listC.AddRange(listC);
A.listD.AddRange(listD);
con.Save(A, references: true); Now it should save everything.In the GET scenario when I fetch data via LoadSingleById<A>(id)
I get all B, C and D collections of A. But I only want to retrieve B and C in the GET scenario not A.Please consider this for the future data usage :-)
-
Well it's not doable now, as in hasn't been implemented yet - but it's doable in that we have all the pieces to start implementing it :).
Once v4 is out we'll start knocking out the most requested features from top to bottom that are a good fit for SS.
As for any complicated corner-cases like hierarchal querying, it's best to just do those in SQL which is pretty DB provider specific, rather than try to invent a fragile/leaky typed abstraction over it.
-
Matthew Cowan commented
Thanks for the awesome write-up. I like the general approach, especially the expression visitor concept. The conventions would work too (makes "ServiceStack sense"), I just wonder how "cluttered" the DTOs would get to accommodate generic needs (my problem really, not the framework's, but since I'm using T4 templates anyway, shouldn't be a problem). And in the end, sounds like I could always translate/map a generic contextual query DTO of mine to a number of generated request DTOs that could be used to populate the SqlExpression in series. Anyway, I like where you're going with it, is some of what you describe do-able right now?
For V4 work, my vote for what it's worth is to first enhance the join and fetch story on relations, and address the batching and field ignore scenarios Joe and Chris mentioned (maybe others too that people bring up), then visit this generic querying capability so that it can take advantage of the aforementioned. One final story that would really be awesome (granted for me, selfishly, and most likely out of scope) IMO is a hierarchical query capability (for those databases that support CTEs anyway for example (wish MySql could get with it, aargh!!)) ... These are useful in scenarios where larger applications have nested groups and roles for example, and permissions/attributes assigned at any given one (a scenario I've run into many times, in which case I typically fallback to stored procedures and functions at the moment).
-
Matthew: Yeah I was thinking about what the best way to approach generic querying was, but I don't believe inventing a query language is the right approach as it adds internal coupling to fields that transcends the service contract (i.e. internal symbol references in strings) making it fragile and hard to know when you're introducing a breaking change. I also generally dislike filters that partially populate fields as you can't make guarantees of the state of the DTO without the context of the query that it was called in.
With that said, I believe the right approach is one that looks transparent so its indistinguishable from the surface area that you're using generic querying functionality. e.g. it would make use of normal DTO properties, keep a flat structure and basically work exactly like any other service.
So for Northwind products you could have the expected query:
/products?Ids=1,2,3,4&skip=20&take=10
So the Request DTO would look like normal:
public Products {
public List<int> Ids { get; set; }
public int Skip { get; set; }
public int Take { get; set; }
}and in your service you could combine the Request DTO with the OrmLite metadata table that returns a populated SqlExpression (aka ExpressionVisitor) which you can further introspect and add custom filters to before passing it to OrmLite as an Expression or use ToSql() and pass the raw SQL instead, e.g:
//Short version:
var products = db.Select<Product>(sqlExpr => sqlExpr.PopulateWith(requestDto));//Long version
var sqlExpr = db.CreateExpression<Product>(requestDto);
sqlExpr.Where(p => p.IsActive);var products = db.Select<Product>(sqlExpr);
If you wanted to include full details (e.g. to include Orders), my preference is to expose it at a different resource which returns the full dataset, e.g:
/products/detail?Ids=1,2,3,4&skip=20&take=10
We could also include some conventional heuristics (that's also configurable) that could analyze the DTO fields and determine what the right operand was, e.g. if you used a 'Before' or 'LessThan' prefix we could infer that to mean '<', so we could call a service with the Request DTO:
public Products {
...
public DateTime? BeforeCreatedDate { get; set; }
}like:
/products?beforeCreatedDate=2013-10-10
and the SqlExpression would be behave like:
var products = db.Select<Product>(p => p.CreatedDate < requestDto.BeforeCreatedDate);
-
Joe: We could extend the Ignore attribute to specify finer-grained levels using Enums, e.g. ignore on both Updates and Inserts with:
[Ignore(Operation.Update | Operation.Insert)]
public string Name { get; set; } -
Matthew Cowan commented
Related, but a little different, would love to see queries get extended to a generic service, an opinionated ServiceStack URL CRUD api (similar to odata, but ormlite specific: i.e.: not iqueryable)... Even control which query apis are available per domain dto/entity with annotations/attributes possibly.
Once databases are registered in the db factory, you could easily call them with /db/ for the main db, and /db/<named_db_in_container>/ for any named db factories in the container.
Would probably be implemented as a feature (like the Auth feature), except it's generic for db access...
The api could be a service with an interface like:
/db/northwind/products?select=productid,orders.id&include=orders&filter=productid:eq:1,2,3,4
There could also be an API that you could access to simply parse: ParseUriToQuery, that would allow to do validation/authentication/authorization as needed on the query before hitting the db... Just a thought... -
Joe Kampschmidt commented
- Perhaps attributes to decorate properties to ignore specific fields on insert vs update. Something like [IgnoreOnUpdate] not the right way but conveys the idea
-
Chris Mcvittie commented
Yes, please!
Also, support for batching multi-queries. (dare I say something like nhibernate futures....)
But I find for the scenario here (http://stackoverflow.com/questions/16465206/servicestack-ormlite-query-multiple) we are best falling back to dapper + plain sql at present.An option for Insert/Save to automatically set the id (in autoincremenet/identity scenarios) would also be handy. (e.g. return Db.Insert(dto);)