C# – Linq – Left outer join with dot notation

clinqlinq-to-entities

How would I do a left outer join in linq using dot notation?

Here's the query expression:

var query = from u in db.Users
            join d in db.Defects on u.userID equals d.userID into defectsGroup
            from d in defectsGroup.DefaultIfEmpty()
            select new { u, d };

Here's what I tried:

var query2 = db.Users.GroupJoin(db.Defects.DefaultIfEmpty(), 
                u => u.userID, 
                d => d.userID, 
                (user, defect) => new { user, defect });

But defect is showing as IEnumerable<Defect> rather than just Defect. I also tried:

var query2 = db.Users.GroupJoin(db.Defects, 
                u => u.userID, 
                d => d.userID, 
                (user, defect) => new { user, defect.DefaultIfEmpty() });

Which simply doesn't compile. All the online examples seem to use the (clearer) query syntax.

Best Answer

I think you want this:

var query2 = db.Users.GroupJoin(db.Defects,
                                u => u.userId,
                                d => d.userID,
                                (u, defectsGroup) => new { u, defectsGroup})
                     .SelectMany(z => z.defectsGroup.DefaultIfEmpty(),
                                 (z, d) => new { z.u, d });

See my Edulinq blog post on query expressions for more details.

Related Solutions

C# – When to use .First and when to use .FirstOrDefault with LINQ

I would use First() when I know or expect the sequence to have at least one element. In other words, when it is an exceptional occurrence that the sequence is empty.

Use FirstOrDefault() when you know that you will need to check whether there was an element or not. In other words, when it is legal for the sequence to be empty. You should not rely on exception handling for the check. (It is bad practice and might hurt performance).

Finally, the difference between First() and Take(1) is that First() returns the element itself, while Take(1) returns a sequence of elements that contains exactly one element.

C# – Join/Where with LINQ and Lambda

I find that if you're familiar with SQL syntax, using the LINQ query syntax is much clearer, more natural, and makes it easier to spot errors:

var id = 1;
var query =
   from post in database.Posts
   join meta in database.Post_Metas on post.ID equals meta.Post_ID
   where post.ID == id
   select new { Post = post, Meta = meta };

If you're really stuck on using lambdas though, your syntax is quite a bit off. Here's the same query, using the LINQ extension methods:

var id = 1;
var query = database.Posts    // your starting point - table in the "from" statement
   .Join(database.Post_Metas, // the source table of the inner join
      post => post.ID,        // Select the primary key (the first part of the "on" clause in an sql "join" statement)
      meta => meta.Post_ID,   // Select the foreign key (the second part of the "on" clause)
      (post, meta) => new { Post = post, Meta = meta }) // selection
   .Where(postAndMeta => postAndMeta.Post.ID == id);    // where statement

Best Answer

Related Solutions

C# – When to use .First and when to use .FirstOrDefault with LINQ

C# – Join/Where with LINQ and Lambda

Related Topic