Lamda the difference between join and groupjoin in expression, how to implement the left join effect of SQL

For example, you can join a product table with a product category table to get the product name and its corresponding category name

Db. Products  . Join   (        db. Categories,        p = P.categoryid,        c = C.categoryid,        c) = = new {p,c.categoryname}  )  . Where (p = = P.categoryid = 1);  

This join corresponds to the inner join in SQL, which is easy to understand. The red C type in the above code is category

How do I write if I want to achieve the effect of a LEFT join in SQL? Take it for granted, change the join to GroupJoin,

Db. Products  . GroupJoin   (        db. Categories,        p = P.categoryid,        c = C.categoryid,        cs) = = new {P,cs. FirstOrDefault (). CategoryName})  . Where (p = = P.categoryid = 1);  

The Red CS type in the above code is IENUMERABLE<CATEGORY> Do you use FirstOrDefault () just to get a directory, in case the product left join has multiple directories?

So we need to use. SelectMany ienumerable<category> Each of the elements into a projection.

Be aware that adding Defaultifemtyp () is the left join, if not add this, or inner join

Db. Products.groupjoin (db. Categories,          (Product p) = P.categoryid,          (Category c) = C.categoryid,          (prod, cs) = = new {prod, cs} )//CS is ienumerable<category>          . SelectMany (prodcats = ProdCats.cs. DefaultIfEmpty (), (prodcats, c) = =                                new                                {                                    prodcats.prod                                    CategoryName = C.categoryname                                }) ;

The syntax for using LINQ query expressions is

            var qry = from            p in db. Products            joins C in Db.  Categories on  P.categoryid equals C.categoryid to            G from PC in G.defaultifempty ()            select new {                     prodId = P.prodid,                    prodcode = P.prodcode,                    prodname = p.prodname,                    CategoryName = G.firstordefault (). CategoryName            };

If I only use DefaultIfEmpty (), do not groupjoin, so write can achieve this effect?

            var qry = db. Products.selectmany            (                p = db). Categories.where (c = C.categoryid = = P.categoryid). DefaultIfEmpty (),                (p, c) = = new                {                    p.prodid,                    p.prodcode,                    p.prodname,                    c. CategoryName                }            );

This way, if the amount of data is large, the performance will be poor. Because it will load the products and categories into memory, match, not with SQL left Join

Reference article:

Https://stackoverflow.com/questions/584820/how-do-you-perform-a-left-outer-join-using-linq-extension-methods

Http://linqsamples.com/linq-to-objects/join/GroupJoin-linq

Https://docs.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins

Lamda the difference between join and groupjoin in expression, how to implement the left join effect of SQL