Linq to SQL conversion...unable to add second COUNT

I'm trying to convert my SQL statement to a Linq statement and I'm not sure how to add the second COUNT to it. This is my SQL statement

SELECT l.Campus_Name, Labs = COUNT(*), LabsWithSubnets = COUNT(s.Lab_Space_Id)
FROM vw_Lab_Space l
LEFT JOIN vw_Subnet s on l.Lab_Space_Id = s.Lab_Space_Id
GROUP BY l.Campus_Name
ORDER BY 1

and this is my LINQ statement so far:

from l in Vw_Lab_Space
from s in Vw_Subnet
            .Where(s => s.Lab_Space_Id == l.Lab_Space_Id)
            .DefaultIfEmpty()   // <=- triggers the LEFT JOIN
group l by new { l.Campus_Name } into g
orderby g.Key.Campus_Name
select new {
    Campus_Name = g.Key.Campus_Name,
    Labs = g.Count()
}

So I have everything but the LabsWithSubnets part in there. I'm just not sure how to add that in as I can't just do an s.Lab_Space_id.Count() in the select statement.

If you need table structure and sample data please see Need help creating an OUTER JOIN to count spaces.

1 answer

  • answered 2018-04-14 20:45 derloopkat

    In SQL counting a field COUNT(s.Lab_Space_Id) is equivalent to count not null values. So the equivalent would be LabsWithSubnets = g.Count(e=>e.Lab_Space_Id != null).

    Full code including test data sample:

    var Vw_Lab_Space = new Lab[] { new Lab { Lab_Space_Id = 1, Campus_Name = "n1" }, new Lab { Lab_Space_Id = 2, Campus_Name = "n2" }, new Lab { Campus_Name = "n2", Lab_Space_Id = null }, new Lab { Lab_Space_Id = null, Campus_Name = "n10" }, new Lab { Lab_Space_Id = 3, Campus_Name = "n3" } };
    var Vw_Subnet = new Subnet[] {  new Subnet { Lab_Space_Id = 10 }, new Subnet { Lab_Space_Id = 3 }, new Subnet { Lab_Space_Id = 4 } };
    var result =
        from l in Vw_Lab_Space
        from s in Vw_Subnet
                    .Where(s => s.Lab_Space_Id == l.Lab_Space_Id)
                    .DefaultIfEmpty()   // <=- triggers the LEFT JOIN
        group l by new { l.Campus_Name } into g
        orderby g.Key.Campus_Name
    select new
    {
        Campus_Name = g.Key.Campus_Name,
        Labs = g.Count(),
        LabsWithSubnets = g.Count(e=>e.Lab_Space_Id != null)
    };
    

    And test classes are:

    class Lab
    {
        public Nullable<int> Lab_Space_Id { get; set; }
        public string Campus_Name { get; set; }
    }
    
    class Subnet
    {
        public Nullable<int> Lab_Space_Id { get; set; }
    }
    

    e.g. for CampusName "n2", LabsWithSubnets is 1 and Labs is 2