Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2208
  • Last Modified:

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
0
glenn_r
Asked:
glenn_r
  • 4
  • 3
1 Solution
 
Fernando SotoCommented:
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
 
Fernando SotoCommented:
First of all did the solution work for  what you wanted?
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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 SotoCommented:
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
 
Fernando SotoCommented:
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now