左联接LINQ并使用按位比较 [英] Left Join LINQ and using Bitwise Comparisons

问题描述

左联接LINQ并使用按位比较.

Left Join LINQ and using Bitwise Comparisons.

我有一个可以描述为的问题(感谢David B的澄清): 目标是从左表中的每个OID返回1行，其中左表中记录的计数等于右表中匹配行的计数.当在FLAGS中为行设置OID，RID和FLAG时，一条记录匹配.

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 Count of the records in the left table is equal to the Count of the matching rows in the right table. A record matches when the OID, RID, and the FLAG is set in 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;
}

对象a填充了以下数据.

The objects a 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);

要查看是否设置了标志，请进行按位比较，即cs [0]. rs [0] .Flag> 0为TRUE，cs [0] .Flags& rs [0] .Flag = 0为FALSE

To see if a flag is set you do a bitwise comparison, i.e. cs[0].Flags && rs[0].Flag > 0 is TRUE, cs[0].Flags & rs[0].Flag = 0 is FALSE

我有一个常规查询，该查询将获取行，其中cs中的OID计数= rs中的匹配OID计数.现在，我需要对其余规则进行修改的查询.我们检查标志是否在特定行匹配的标志中.

I have this general query that will get me the rows where the count of OID in cs = the count of matching OID in rs. I need a modified query now where the rest of the rules are applied. Where we check if the Flag is in the Flag for the specific row match.

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

如何扩展此范围以应用其他过滤条件?我正在苦苦挣扎的是在我需要它们以建立适当的过滤条件的地方有可用的色谱柱.例如，我需要比较Flags&&旗帜.但是，如何获得标志和标志来访问附加过滤器呢?

How can I extend this to get the additional filter conditions applied? What I am struggling with is having columns available where I need them to build the proper filter conditions. For example, I need to compare Flags && Flag. But how do I get so I have access to Flag and Flags to do the additional filter?

展开.我主要使用TSQL，因此我试图模仿一种可以轻松应用于TSQL的逻辑流程.如果我使用TSQL进行此操作，它将看起来像这样(请注意0的特殊情况):

To Expand. I work mostly with TSQL, so I'm trying to mimic a logic flow I can easily apply in TSQL. If I was doing this with TSQL, it would look like this (note special case for 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.

With that statement, and the data above, the result would be 1, 3, 3.

如果我要注释Roads的最后一个添加项，并取消注释下一行，将Flag更改为16，那么结果将是: 空值 如果您需要更多信息，请发表评论.

If I were to comment the last add to Roads, and uncomment the next line, changing the Flag to 16, then the result would be: NULL Please comment if you need more info.

推荐答案

我认为这应该等效于您的SQL语句，但是由于您的SQL使用RS.Check，它不会产生您提到的结果，但是您没有在示例代码中分配任何Check值(您使用过r.OID).我继续将r.Check = 1;添加到每个Road对象中，并能够获得您提到的结果.

I think this should be equivalent to your SQL statement, however it didn't produce the result you mentioned since your SQL uses RS.Check but you didn't assign any Check values in your sample code (you used r.OID). I went ahead and added r.Check = 1; to each Road object and was able to get the result you mentioned.

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

这篇关于左联接LINQ并使用按位比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！