Lambda方式左连接有Linq方式左连接

http://t.zoukankan.com/superfeeling-p-7530549.html

https://blog.csdn.net/qq_22325259/article/details/121545038

 

网上查到的直接使用Join+DefaultIfEmpty的方式是错误的，实际生成SQL是两表先内联接，然后再LEFT JOIN。经过查证，参考资料，最终得到如下两种方式的左连接写法：

 public void TestMethod1()
        {
            MettingEntities db = new MettingEntities();
            
            //LINQ方式
            var query = (from st in db.met_SysItemsClass
                        join cl in db.met_Shortcut on st.ClassID equals cl.ClassID into cls
                        from c in cls.DefaultIfEmpty()
                        select new TestModel
                        {
                            ClassID = st.ClassID,
                            ClassName = st.ClassName,
                            AdminID = c.AdminID
                        }).ToList();

            //Lambda 方式

　　　　　　　　　　var ww = _context.Movie.Join(_context.Cinema, m => m.Id, c => c.MovieId, (m, c) => new { m.Title, c.Name }).ToList();

　　　　　　　　　　var ww1 = _context.Movie.GroupJoin(_context.Cinema, m => m.Id, c => c.MovieId, (m, c) => new { Movie = m, Cinema = c })
　　　　　　　　　　　　　　　　.SelectMany(mc => mc.Cinema.DefaultIfEmpty(), (m, c) => new { Movies = m.Movie, Cinemas = c })
　　　　　　　　　　　　　　　　.Select(x => new { x.Movies.Title, x.Cinemas.Name }).ToList();

        }
    }

    public class TestModel
    {
        public int? AdminID { get; set; }
        public int ClassID { get; set; }
        public string ClassName { get; set; }
    }

两种方式生成的SQL语句完全相同，如下：

 

SELECT 
[Extent1].[ClassID] AS [ClassID], 
[Extent1].[ClassName] AS [ClassName], 
[Extent2].[AdminID] AS [AdminID]
FROM  [dbo].[met_SysItemsClass] AS [Extent1]
LEFT OUTER JOIN [dbo].[met_Shortcut] AS [Extent2] ON [Extent1].[ClassID] = [Extent2].[ClassID]