glenn_r
asked on
vb.net 2012 LINQ entity framework left join
I have 2 tables albums and pictures. The FK/PK in a column named AlbumID. The multiplicity is 1/M. I can inner join the two tables please give me an example of how to left join the tables so all the rows from albums display and only related rows in pictures display
Dim records = From p In MyEntities.Picutres Join z In MyEntities.Albums On p.AlbumID Equals z.AlbumID
Select z, p
Dim records = From p In MyEntities.Picutres Join z In MyEntities.Albums On p.AlbumID Equals z.AlbumID
Select z, p
ASKER
Can you explain to me how this translates from a SQL LEFT JOIN to what you've suggest. Please break down and explain.
First of all did the solution work for what you wanted?
ASKER
Yes it produces the equivalent of a SQL LEFT JOIN but I'm a bit confused as to what is actually happening. Please explain why it works like this.
Hi glenn_r;
The first line of the query,
From z In MyEntities.Albums
Iterates through the collection records in Albums and assigns each record to the local variable z one at a time. Then the next line of the query,
Group Join p In MyEntities.Picutres On z.AlbumID Equals p.AlbumID Into Group
Finds all Picutres records that match the z record and places then into its own collection called Group. The next line in the query,
From gp In Group.DefaultIfEmpty()
iterates through the Group collection which matches the z record of the first From clause above and returns one record for each iteration. And this is the part that gives the left outer join, if the collection is empty it returns a default type for the object where in this case is a null. The last line of the query,
Select z, gp
returns a flatten result set for each z and a matching record that is stored in local variable gp. If no gp then just returns the value in z.
The first line of the query,
From z In MyEntities.Albums
Iterates through the collection records in Albums and assigns each record to the local variable z one at a time. Then the next line of the query,
Group Join p In MyEntities.Picutres On z.AlbumID Equals p.AlbumID Into Group
Finds all Picutres records that match the z record and places then into its own collection called Group. The next line in the query,
From gp In Group.DefaultIfEmpty()
iterates through the Group collection which matches the z record of the first From clause above and returns one record for each iteration. And this is the part that gives the left outer join, if the collection is empty it returns a default type for the object where in this case is a null. The last line of the query,
Select z, gp
returns a flatten result set for each z and a matching record that is stored in local variable gp. If no gp then just returns the value in z.
ASKER
OK good explaination. On last thing. The .DefaultIfEmpty is overload where you could return a default value. If I create a new object of type picture before I run the query and specify that to be returned in the DefaultIfEmpty it displays an exception "Unable to create a constant value of type 'ConsoleApplication1.Picut
Is it possible to return a default object that I create and if so how is this method/option used?
Is it possible to return a default object that I create and if so how is this method/option used?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This should give you what you want.
Open in new window