T-SQL 到 LINQ 查询，其中包含 case 语句和子查询

Question

我是 LINQ

的完全初学者，我想在 LINQ 中转换这个 T-SQL 查询

SELECT 
CASE 
    WHEN D.IsBaseloadDefined = 1 
    THEN COUNT(D.DeviceID) 
    ELSE 
         (SELECT COUNT(DORG.DeviceID) 
          FROM DeviceOrganization DORG 
          INNER JOIN Organization ORG ON DORG.OrganizationID = ORG.OrganizationID
          INNER JOIN BaseloadOrganization BO ON ORG.BaseloadOrganizationId = BO.OrganizationID
          INNER JOIN Baseload BL ON BO.BaseloadID = BL.BaseloadID
          WHERE DORG.DeviceID = D.DeviceID
          AND BL.RecursUntil >= GETDATE()
          GROUP BY DORG.DeviceID)
END AS [Nb of devices]

FROM DeviceOrganization DO 
INNER JOIN Device D ON DO.DeviceID = D.DeviceID
LEFT JOIN BaseloadDevice BD ON D.DeviceID = BD.DeviceID
LEFT JOIN Baseload B ON BD.BaseloadID = B.BaseloadID AND B.RecursUntil >= GETDATE()
INNER JOIN OrganizationHierarchy OH ON DO.OrganizationID = OH.SubOrganizationID
WHERE OH.OrganizationID = 6
AND D.IsActive = 1
group by D.DeviceID, D.IsBaseloadDefined

我看过这个话题，但我真的不明白答案

到目前为止，我

唯一能做的就是这个，现在我完全迷失了

from deviceO in _context.DeviceOrganizations join d in _context.Devices on deviceO.DeviceID equals d.DeviceID join bd in _context.BaseloadDevices on d.DeviceID equals bd.DeviceID join b in _context.Baseloads on bd.BaseloadID equals b.BaseloadID join oh in _context.OrganizationHierarchies on deviceO.OrganizationID equals oh.SubOrganizationID where oh.OrganizationID == OrganizationId where d.IsActive == true where b.RecursUntil <= DateTime.Now group d.DeviceID by d.DeviceID).Count()

Answer 1

而不是获取组的计数

组 d.设备标识

由 d.设备标识(。计数((

您应该将结果保存在变量中

var data = from deviceO in _context.DeviceOrganizations
    join d in _context.Devices on deviceO.DeviceID equals d.DeviceID
    join bd in _context.BaseloadDevices on d.DeviceID equals bd.DeviceID
    join b in _context.Baseloads on bd.BaseloadID equals b.BaseloadID
    join oh in _context.OrganizationHierarchies on deviceO.OrganizationID equals oh.SubOrganizationID
    where oh.OrganizationID == OrganizationId
    where d.IsActive == true
    where b.RecursUntil <= DateTime.Now

然后你应该做这样的事情：

//group by 2 properties
var result = data.GroupBy(d => new { d.DeviceID, d.IsBaseloadDefined })
                 .Select(g =>
                {
                //for each group we get IsBaseloadDefined property
                var IsBaseloadDefined = g.Key.IsBaseloadDefined;
                if (IsBaseloadDefined == 1)
                {
                    return g.Count();
                }
                else
                {
                    // here another select that return count:
                    //(SELECT COUNT(DORG.DeviceID) 
                    //FROM DeviceOrganization DORG
                    //    INNER JOIN Organization ORG ON DORG.OrganizationID = ORG.OrganizationID
                    //INNER JOIN BaseloadOrganization BO ON ORG.BaseloadOrganizationId = BO.OrganizationID
                    //INNER JOIN Baseload BL ON BO.BaseloadID = BL.BaseloadID
                    //WHERE DORG.DeviceID = D.DeviceID
                    //AND BL.RecursUntil >= GETDATE()
                    //GROUP BY DORG.DeviceID)
                    //in this query you should return Count() of group
                    return 1; //return group.Count() instead 1
                }
            });

我希望这对你有帮助