Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Linq - Left outer join with dot notation

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.

like image 675
Adam Rackis Avatar asked Mar 30 '11 14:03

Adam Rackis


People also ask

Can we use left join in Linq?

You can use LINQ to perform a left outer join by calling the DefaultIfEmpty method on the results of a group join.

Is GroupJoin left join?

This is actually not nearly as crazy as it seems. Basically GroupJoin does the left outer join, the SelectMany part is only needed depending on what you want to select.

What is GroupJoin in Linq?

The group join is useful for producing hierarchical data structures. It pairs each element from the first collection with a set of correlated elements from the second collection. For example, a class or a relational database table named Student might contain two fields: Id and Name .

Is Linq join inner or outer?

One commonly used feature of Language-Integrated Query (LINQ) is the facility to combine two sequences of related data using joins. The standard join operation provides an inner join but with a minor modification can be changed to give a left outer join.


1 Answers

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.

like image 102
Jon Skeet Avatar answered Oct 23 '22 09:10

Jon Skeet