Linq to enable left outer join

Question

I'm struggling to bind linq to objects left by an outer join. I have two objects (tables):

Listings { ListingID, MakeID (nullable) } Makes { MakeID, Description } 

I want to write something like this in LINQ:

 select listings.listingID ,listings.makeid , IsNull(makes.Description, 'NA') from listings left outer join makes on listings.makeid = makes.makeid 

Answer 1

Anyone who advises you to use .DefaultIfEmpty () as part of an outer join in LINQ to Entities has not actually tried this on their own! Tt just doesn't work - at least as in .NET 3.5 SP1.

This blogger tells you how you should do it. Essentially, .NET makes external joins in LINQ for Entities by default, so you should leave the value .DefaultIfEmpty (). For multiple outer joins, you need to nest query groups so that their context is clear.

Answer 2

Below is your solution for reaching the left junction. For other resources, I really recommend trying the linq pad: http://www.linqpad.net/ This is a great Linq learning tool.

 // Listing class/container/table public class Listing { public string ListingID {get;set;} public Int32? MakeID {get;set;} } // Make class/container/table public class Make { public Int32 MakeID {get;set;} public string Description {get;set;} } public class Main { public static void LinqMain() { // Populate the listing table with data List<Listing> listings = new List<Listing>() { new Listing() { ListingID = "Test 1", MakeID = 1 }, new Listing() { ListingID = "Test 2", MakeID = 1 }, new Listing() { ListingID = "No Make", MakeID = null }, new Listing() { ListingID = "Test 3", MakeID = 3 }, new Listing() { ListingID = "Another Makeless", MakeID = null } }; // Populate the makes table with data List<Make> makes = new List<Make>() { new Make() { MakeID = 1, Description = "Make 1"}, new Make() { MakeID = 2, Description = "Make 2"}, new Make() { MakeID = 3, Description = "Make 3"}, new Make() { MakeID = 4, Description = "Make 4"} }; // Return the left join on Make Id var result = from l in listings // These two lines are the left join. join leftm in makes on l.MakeID equals leftm.MakeID into leftm from m in leftm.DefaultIfEmpty() // To ensure the select does not get bogged down with too much logic use the let syntax let description = m == null ? "NA" : m.Description select new { l.ListingID, l.MakeID, description }; } 

The resulting variable will contain:

• {ListID = "Test 1", MakeID = 1, description = "Make 1"}
• {ListID = "Test 2", MakeID = 1, description = "Make 1"}
• {ListingID = "No Make", MakeID = null, description = "NA"}
• {ListID = "Test 3", MakeID = 3, description = "Make 3"}
• {ListID = "Another Lifeless", MakeID = null, description = "NA"}

Answer 3

http://oddiandeveloper.blogspot.com/2008/12/testable-left-outer-join-in-linq-to.html

This should help, this blog post I made some time ago should still be relevant and perhaps also help in testing.

Also make sure that your foreign keys are set when you create the entity model, this will help customize your dependencies.

Answer 4

You should not check the dev machine, but is something like this possible?

 var x = from l in listings join m in makes on l.makeid equals m.makeid into g from ma in g.DefaultIfEmpty() select new { l.listingID, l.makeid, (ma.Description == null ? "NA" : ma.Description) }; 

If you have any problems with this, let me know and I will check my working computer.