Join LINQ on the left and use bitwise comparisons

Question

On the left, join LINQ and use bitwise comparisons.

I have a problem that can be described as (thanks to David B for clarifying this): The goal is to return 1 row per OID from the left table, where the number of records in the left table is equal to the number of matching rows in the right table. A record matches when OID, RID, and FLAG are set to FLAGS for a row.

The objects we are comparing have the following structure:

public class Roads : List<Road>{}
public class Road
{
    public int RID;
    public int OID;
    public int Check = 1;
    public long Flag;
}
public class Cars : List<Car> { }
public class Car
{
    public int RID;
    public int OID;
    public long Flags;
}

Objects a are filled with the following data.

        Roads rs = new Roads();
        Cars cs = new Cars();

        Car c = new Car();
        c.OID = 1;
        c.RID = 1;
        c.Flags = 31; // 11111
        cs.Add(c);
        c = new Car();
        c.OID = 1;
        c.RID = 2;
        c.Flags = 31; //11111
        cs.Add(c);
        c = new Car();
        c.OID = 1;
        c.RID = 3;
        c.Flags = 4; //00100
        cs.Add(c);

        Road r = new Road();
        r.OID = 1;
        r.RID = 1;
        r.Flag = 8; //00010
        rs.Add(r);
        r = new Road();
        r.OID = 1;
        r.RID = 2;
        r.Flag = 2; //01000
        rs.Add(r);
        r = new Road();
        r.OID = 1;
        r.RID = 3;
        r.Flag = 4;  //01000
        rs.Add(r);
      //  r = new Road();
      //  r.OID = 1;
      //  r.RID = 3;
      //  r.Flag = 16;  //00001
      //  rs.Add(r);

To find out if the flag is set, you perform bitwise comparisons, that is, cs [0] .Flags && & & rs [0] .Flag> 0 - TRUE, cs [0] .Flags and rs [0] .Flag = 0 - False

, , OID cs = OID rs. , . , .

    var carLookup = cs.ToLookup(cb => c.OID);
    var roadLookup = rs.ToLookup(rb => r.OID);

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

, ? , , - , , . , && . , ?

. TSQL, , TSQL. TSQL, ( 0):

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  
 AND (CS.FLAGS & RS.FLAG > 0
      OR (CS.FLAGS=0 AND RS.FLAG=0))
GROUP BY cs.OID  
HAVING Count(cs.OID) = Sum(RS.Check)   

1, 3, 3.

Roads , 16, : , .

Answer 1

, SQL, , , SQL RS.Check, Check ( r.OID). r.Check = 1; Road .

var query = from car in cs
            from road in rs
            where car.OID == road.OID
                && car.RID == road.RID
                && ((car.Flags & road.Flag) > 0 || (car.Flags == 0 && road.Flag == 0))
            group new { car, road } by car.OID into grouping
            let CarCount = grouping.Count()
            let RoadCount = grouping.Sum(o => o.road.Check)
            where CarCount == RoadCount
            select new { OID = grouping.Key, CarCount, RoadCount };