时间戳

首页 > 解决方案 > 将 Sql 语句转换为 Entity Framework Core

c# - 将 Sql 语句转换为 Entity Framework Core

问题描述

如何将此 sql 查询转换为实体框架核心中的 linq？

Select a.Id,a.Url,c.Title,a.ParentId,b.totalSubCats 
From [Tbl_Menu] As a 
LEFT OUTER JOIN
  ( Select ParentId, Count(*) As totalSubCats From [Tbl_Menu] Group By ParentId ) As b
On a.Id=b.ParentId
LEFT OUTER JOIN [Tbl_Menu] As c On a.Id = c.Id
ORDER BY a.Id

我们有这些列Menu_Tbl

Id, Url, Title, Description,ParentId

因为Left Join我写了这个查询但不能运行

 var query = (from m1 in _context.Menu_Tbl 
                     join m2 in _context.Menu_Tbl on m1.Id equals m2.Id 
                     into m3 from m in m3.DefaultIfEmpty() select m1);

标签： c#sqlentity-frameworklinq

解决方案

这个应该可以的。只需将大查询除以小块（子查询）

var grouped = 
   from m in ctx.Tbl_Menu
   group m by new { m.ParentId } into g
   select new 
   {
       g.Key.ParentId,
       totalSubCats = g.Count()
   };

var query = 
   from a in ctx.Tbl_Menu 
   join b in grouped on a.Id equals b.ParentId into gj
   from b in gj.DefaultIfEmpty()
   join c in ctx.Tbl_Menu on a.Id equals c.Id into ggj
   from c in ggj.DefaultIfEmpty()
   select new 
   {
       a.Id,
       a.Url,
       c.Title,
       a.ParentId,
       b.totalSubCats 
   };

推荐阅读