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.
You can use LINQ to perform a left outer join by calling the DefaultIfEmpty method on the results of a group 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.
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 .
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With