left join?

Sorry about this question but Im still "getting" this. How could I go about doing a "left join"? Is there a way to "combine" parent and child objects into one queryable object? Or will I have to query for parent object then query the children of the found set? if so, how?

No, you can't do a left join to get parent and child objects in "one queryable object."  A LightSpeed entity corresponds to exactly one row in one table (or view).

However, if you use eager loading, LightSpeed does batch up queries against multiple tables (for multiple entity types) into a single database operation.  For example, if the Parent.Children association is marked as [EagerLoad], then whenever you query for Parent entities, their Child objects will be brought back as part of the same database operation.  You don't get a combined ParentWithChildren object, but you do get all the data in a single go.

Note, however, that this doesn't support query logic or filtering across the child objects.  For example, we don't have a way of saying "get me all the Parents and their Children where the Child's Age is greater than 17."  We either eager-load the entire collection, or none of it.  You can of course query the loaded Children collection using LINQ to Objects, but this will run on the full in-memory collection, not on the database.

You could create a ParentWithChildren view which does the left join, but this will have to map to a ParentWithChildren entity, not a graph of Parent and Child entities -- the Parent data would be repeated in each ParentWithChildren (per the one-entity-equals-one-row rule).  The designer doesn't support dragging of views so you would need to build such an entity manually, either in the designer or in code.

Hi Ivan, do you plan on supporting the Join operation in the next version? Thanks Mike

It is on the list of things we'd like to do for 3.0, and I think it is likely to be included, but at this stage we can't promise anything.

Hi Ivan,

 

Is the join supported in the LS 3.0? I am trying to do perform left joins and getting the error

"This expression type is not supported in LightSpeed 3.0".

FYR below is the query i am trying to execute

var results = from et in uow.SmsExpenseTypes

                           join cem in uow.SmsEacatExptypMaps

                            on et.Id equals cem.ExpenseTypePkId into JoinedExpType

 

                           from x in JoinedExpType.DefaultIfEmpty()

                           join ec in uow.SmsEacategories

                           on x.EaCategoryPkId equals ec.Id into JoinedExpTypeCat

                           from y in JoinedExpTypeCat.DefaultIfEmpty()

 

                           join tp in uow.SmsTemplatePolicies

                            on

                            new { ExpenseTypePkId = et.Id, TemplatePkId = templateID }

                                equals

                            new { ExpenseTypePkId = tp.ExpenseTypePkId, TemplatePkId = tp.TemplatePkId }

                            into policy

                           from z in policy.DefaultIfEmpty()

                           select new

                           {

                               CategoryName = y.EaCategoryName == null ? "Misc" : y.EaCategoryName,

                               et.ExpenseTypeName,

                               ExpenseTypePkId = et.Id,

                               EaCategoryPkId = y.Id,

                               z

                           };

 

I checked my query using LINQPad and it executes properly, but when I try with LS i run into the error. Any ideas? Suggestions?

~ Sidharth

Simple joins are supported.  Joins on composite types (in your case, the join on an anonymous type at the top of the third 'paragraph') are not currently supported.

I'll ask our join wizard if he can suggest a workaround, but he's overseas at the moment so it may take a little while for him to get back to you.

Hi Sidharth,

Just to follow up on what Ivan mentioned in the earlier reply, currently joins using anonymous types are not supported, we can have a look at adding this in however its likely to be a more complicated addition to the LINQ provider so I cant promise any specific times around this.

The workaround you could look at currently would be to fetch the results in two chunks and then join the two result sets using the anonymous type by using L2O (in memory LINQ query), or to join on a single column and then use a where clause to apply the other filter if this is equivilant in the context of your query.

 

Jeremy

Hi Jeremy,

You mention the workaround for left join, can you post an example for us? We need to solve it now.

Steven

Hi Steven,

The workaround is no longer needed as we have added in functionality to the LINQ provider so that you can join on anonymous types. If you were specifically interested in the workaround however, what I was suggesting was something along the lines of:

 

from e1 in unitOfWork.Entity1s.AsEnumerable()

join e2 in unitOfWork.Entity2s.AsEnumerable()

on new { Field1 = e1.Field1, Field2 = e1.Field2 } equals new { Field1 = e2.FieldX, Field2 = e2.FieldY }

 

So the main difference for the query is you are bringing the sets into memory so you can join using L2O rather than at the server.

 

Jeremy