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
glenn_rAsked:
Who is Participating?
 
Fernando SotoRetiredCommented:
In the context of Linq to Entity Framework you can not use a reference type as the default because the query is converted to a T-SQL equivalent statement and SQL server does not know how to convert to a default type that is defined in your application. That you would have to do in your applications by assigning your default values to all gp that are null's.
0
 
Fernando SotoRetiredCommented:
Hi glenn_r;

This should give you what you want.

Dim records = From z In MyEntities.Albums 
              Group Join p In MyEntities.Picutres  On z.AlbumID Equals p.AlbumID Into Group
              From gp In Group.DefaultIfEmpty()
              Select z, gp

Open in new window

0
 
glenn_rAuthor Commented:
Can you explain to me how this translates from a SQL LEFT JOIN to what you've suggest. Please break down and explain.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Fernando SotoRetiredCommented:
First of all did the solution work for  what you wanted?
0
 
glenn_rAuthor Commented:
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.
0
 
Fernando SotoRetiredCommented:
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.
0
 
glenn_rAuthor Commented:
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.Picutre'. Only primitive types ('such as Int32, String, and Guid') are supported in this context."

Is it possible to return a default object that I create and if so how is this method/option used?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.