T-SQL to LINQ query that has a case statement and a subquery in it

Question

I'm a complete beginner in LINQ and I would like to convert this T-SQL query in LINQ

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

I've seen this topic but I don't really understand the answer

The only thing I could do so far is this, and now I'm completly lost

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()

Use `LinqPad` ... that's easier than asking a question here – Rahul May 17 '17 at 13:29 — Rahul, May 17 '17 at 13:29

score 0 · Answer 1 · answered May 17 '17 at 14:49

Instead of get count of group

group d.DeviceID by d.DeviceID).Count()

you should save result in variable

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

and then you should do something like this:

//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
                }
            });

I hope this helps you

That helps me ! I finally understand the logic and I think I could do it now. Thank you :) — Clara BELLIN, May 18 '17 at 08:00

T-SQL to LINQ query that has a case statement and a subquery in it

1 Answers1