SQL转换为LINQ到实体

本文关键字：实体 LINQ 转换 SQL | 更新日期: 2023-09-27 18:13:00

如何将此SQL语句转换为LINQ to Entities ?我找不到用WHERE子句

联合表的方法。SQL: 

Declare @DID int, @key varchar(50)
SET @DID = 3
SET @key = ''
SELECT TBL.GID, TBL.GName, TBL.DID FROM     
(
  SELECT TOP 1 (@DID*10000) AS [GID], '' AS [GName], @DID AS [DID] 
  FROM dbo.Employees E
  WHERE E.DepartmentID=@DID 
  AND (E.GroupID IS NULL OR E.GroupID = 0)  
  UNION ALL  
  SELECT G.GroupID AS [GID], G.GroupName AS [GName], G.DepartmentID AS [DID] 
  FROM dbo.Groups G
) TBL 
WHERE TBL.DID IN (@DID) 
AND TBL.GName IN 
(
  SELECT (CASE WHEN E.GroupID = 0 or E.GroupID IS NULL THEN '' 
          ELSE (SELECT G.GroupName FROM Groups G WHERE G.GroupID=E.GroupID) END) 
  FROM Employees E 
  WHERE E.DepartmentID = @DID 
  AND (E.FirstName + ' ' + E.LastName) LIKE '%' + @key + '%'
) 
ORDER BY TBL.GName DESC

LINQ to entities:

var Details = (from a in Context.Employees
               where a.DepartmentID == DepartmentID
               && (a.GroupID == null || a.GroupID == 0)
               select new
               {
                 GID = Convert.ToInt32(DepartmentID * 10000),
                 GName = "",
                 DID = a.DepartmentID
               }
               ).Concat(
                 from a in Context.Groups
                 select new
                 {
                   GID = a.GroupID,
                   GName = a.GroupName,
                   DID = DepartmentID
                 }
               );

SQL转换为LINQ到实体

在每个查询的结果中创建的匿名类型是不同的类型，因此您不能对它们进行Concat()。您可以像这样定义一个常见的Group类…

class Group
{
  public int ID { get; set; }
  public int DepartmentID { get; set; }
  public string Name { get; set; }
}

然后取出子查询并将结果创建为上述类的实例…

int deptID = 99;
string key = "foo";
var r1 = (from a in Context.Employees
          where a.DepartmentID == deptID
          && (!a.GroupID.HasValue || a.GroupID == 0)
          select new Group()
          {
            ID = a.DepartmentID * 10000,
            Name = string.Empty,
            DepartmentID = a.DepartmentID
          }
         ).Take(1);
var r2 = (from a in Context.Groups
          where a.DepartmentID == deptID
          select new Group()
          {
            ID = a.GroupID,
            Name = a.GroupName,
            DepartmentID = a.DepartmentID
          }
         );
var results = r1.Concat(r2); // UNION ALL the above 2 sets of results

然后创建过滤器集，最后使用它过滤上面的结果…

var filter = (from e in Context.Employees
              join g in Context.Groups on g.GroupID equals e.GroupID into eg
              from subgrp in eg.DefaultIfEmpty()
              where e.DepartmentID == deptID
              && (e.FirstName + " " + e.LastName).Contains(key)
              select e.GroupID.HasValue && e.GroupID != 0 ? subgrp.GroupName : string.Empty 
             ).Distinct().ToList();
results = from a in results
          where filter.Contains(a.Name)
          orderby a.Name descending
          select a;
相关文章：