This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
One of the things I did to simply my LINQ queries in OpenAccess (primarly due to the lack of proper Left Join support), is to be re-write the queries to use reference or collection associations as part of the where and select clauses, which allows you to simpify the query and their LINQ engine would automatically put the left joins in for you. So just say I have a Customer entity, and a CustomerDiscount entity in a 0:Many association (each customer can have 0 or more CustomerDiscounts). Then I can write a LINQ query like this (association was renamed to simply Discounts in this case): var query = from c in uow.Customers I would have expected a left join to be created on the CustomerDiscount table, but instead I get this SQL which is wrong: SELECT I can re-write the query with an explicit left join (which is what did not work in OpenAccess!) and it will work: var query = from c in uow.Customers clearly this is a much longer and more complicated query (and subject to more potential errors, especially logic errors if someone else is trying to decipher the query). Although it is nice that Lightspeed properly handles left joins (and produces the SQL below), it would be nice if it could support using the reference or collection associations in where clauses like this as it can dramatically simplify the query, especially when you get lots of left joins going on. SELECT |
|
|
It looks like you're after the semantics of "are there any Discounts associated with the Customer," is that right? If so, use the LINQ Any operator: from c in uow.Customers A bonus of using Any() is that you can pass a predicate e.g. from c in uow.Customers This makes it much more flexible than a null check as well as more self-documenting. |
|
|
Great, I will give that a try.
|
|
|
One thing I have not been able to do at all is to use collection or reference associations in any of my select clauses. Is that not supported in Lightspeed? I always end up having to remove them, and do explicit joins on the tables to get what I need. |
|
|
Are you thinking of something like: select new { Id = e.Id, Bars = e.Bars } where we have a Foo entity which as a child collection of Bars? This will work but keep in mind that the projection will be executed client side so if Bars are not eager loaded then you would get an N+1 query result so you would want to look at using this kind of thing in conjunction with a named aggregate.
Jeremy |
|
|
No, more like: select new { Id = e.Id, BarName = e.Bar.Name, } which would generate a left join against the Bars table, and select out the name property to put into the BarName projection property. |
|
|
That is going to be effectively the same then, in that the projection would occur client side possibly triggering a lazy load of the Bar association and then fetching out the Name property. For actual projections you will need to use joins to allow you to explicitely select data from a joined table. What might possibly help you to understand why this occurs is to have a look at the querying api for LightSpeed. The LINQ provider is just a translator against this API so any query you write in LINQ has to be expressed in terms of the querying API to be executed. The querying API is entity centric so there isnt a notion of asking for a property of an associated entity as part of a selection, rather you can ask for a property of an entity involved in the query so to satisfy that you would need to be working with joins to have that entity joined in.
Jeremy
|
|
|
Ok. However this code was simply not working for me at all, and crashing with an exception. I have been routinely taking these out and going back to regular joins as I hit these. |
|
|
Was it was a NullReferenceException? If so it may just be a case where the associated entity is null. If not then if you can fire through a repro we can have a look at what caused the exception.
Jeremy |
|
|
I think it was a different exception, but I have already re-written the code. I will pop something up here when I hit it the next time (still got some code to translate). |
|