LINQ Left Join with Grouping and Having Convert this TSQL

The following TSQL provides an example of how I might solve this problem with SQL. The goal is to return 1 row per OID from the Left Table, where the Count of the records in the left table is equal to the Count of the matching rows in the right table.

SELECT cs.OID, Count(cs.OID) AS CarCount, Sum(RS.Check) AS RoadCount  
   FROM Cars AS cs  
LEFT JOIN Roads AS RS  
  ON CS.oid = RS.OID  
   AND cs.RID = RS.RID  
  GROUP BY cs.OID  
  HAVING Count(cs.OID) = Sum(RS.Check)  

Using object setup below, is there an equivalent LINQ query that can be constructed, or is this not possible? Note the default value given for check in the declaration of the Road class. In the setup example below the re开发者_开发问答sult should be zero matching. Adding one more road with proper values will have it return just one. At least that is what is ideal.

The problem I have run into is that it seems this type of TSQL code is just too complex for LINQ. I haven't found an obvious or non-obvious solution to achieve a similar behavior. Thus I believe perhaps the solution is to stop trying to copy SQL and do something different. With not enough LINQ experience, I wouldn't know where to start.

public class Roads : List<Road>{}  
public class Road  
{  
    public int RID;  
    public int OID;  
    public int check = 1;  
}  
public class Cars : List<Car> { }  
public class Car  
{  
    public int RID;  
    public int OID;  
}  

private void CheckCheck()  
{  
    Roads rs = new Roads();  
    Cars cs = new Cars();  

    Car c = new Car();  
    c.OID = 1;  
    c.RID = 1;  
    cs.Add(c);  
    c = new Car();  
    c.OID = 1;  
    c.RID = 2;  
    cs.Add(c);  
    c = new Car();  
    c.OID = 1;  
    c.RID = 3;  
    cs.Add(c);  

    Road r = new Road();  
    r.OID = 1;  
    r.RID = 1;  
    rs.Add(r);  
    r = new Road();  
    r.OID = 1;  
    r.RID = 2;  
    rs.Add(r);  

    // Results should be :  
    // OID where Count of OID from C = Count of OID from R  
}  

• Your HAVING clause would filter out any cars which do not match roads. This makes the left join into an inner join.
• You have COUNT(cs.OID) which says it's counting cars, but it doesn't. You might have meant COUNT(DISTINCT cs.OID)

Here's a literal translation:

from c in Cars
join r in Roads on new {c.OID, c.RID} equals new {r.OID, r.RID}
group new {Car = c, Road = r} by c.OID into g
let carCount = g.Count()  //did you mean g.Select(x => x.Car.OID).Distinct().Count()
let roadCount = g.Sum(x => x.Road.Check)
where carCount = roadCount
select new {OID = g.Key, CarCount = carCount, RoadCount = roadCount}

The goal is to return 1 row per OID from the Left Table, where the Count of the records in the left table is equal to the Count of the matching rows in the right table.

Based on this description, I'd write:

var carLookup = Cars.ToLookup(c => c.OID);
var roadLookup = Roads.ToLookup(r => r.OID);

from x in carLookup
let carCount = x.Count()
let roadCount = roadLookup[x.Key].Count()
where carCount = roadCount
select new {OID = g.Key, CarCount = carCount, RoadCount = roadCount}

I'm not sure I completely understand what the desired results are but here's my shot at it. The check field isn't really necessary with LINQ as it doesn't serve any purpose other than for counting.

I misunderstood the importance of the join and what the HAVING clause did for the query. It's now back to the original TSQL version but with the changed RoadCount calculation. I believe this is what you were asking for.

var results = from Car in cs
              join road in rs
                  on new { Car.OID, Car.RID } equals new { road.OID, road.RID }
                  into Roads
              group roads by Car.OID into cars
              let CarCount = cars.Count()
              let RoadCount = cars.Sum(roads => roads.Count())
              where CarCount == RoadCount
              select new
              {
                  OID = cars.Key,
                  CarCount,
                  RoadCount
              };