linq查询，其中包含类列表 [英] linq query where contains list of class

问题描述

我有一张桌子:

tblFildsVal
- id bigint;
- listId bigint;
- listFieldValue bigint;
- parrentId bigint;

tblFildsVal
- id bigint;
- listId bigint;
- listFieldValue bigint;
- parrentId bigint;

然后我有一个表示4个匹配条件的数组(或List):

Then I have an array (or List) representing 4 matching conditions:

myArr
- listId //参考listId
- rangeType //它有4个vlaue:1)精确2)上3)下4)之间
-来自//较低的值
-至//较高的值

myArr
- listId //refer to listId
- rangeType //it has 4 vlaue: 1)exact 2)upper 3)lower 4)between
- from //lower value
- to //upper value

可以将多个匹配条件链接在一起，以便它对 tblFildsVal 中的任何记录与任何给定条件集进行匹配.

Multiple matching conditions can be chained together such that it performs a match on any record from tblFildsVal against any of the given set of conditions.

例如:

myArr{{1,1,100,Null},{2,2,125,Null},{3,3,Null,175},{4,4,125,175}}  

这意味着我想知道每条记录(由parrentId分组)，其中任何一项:

It means I want to know every record (groupped by parrentId) where any of:

(1)listId == 1 && listFieldValue == 100  
(2)listId == 2 && listFieldValue > 125  
(3)listId == 3 && listFieldValue < 175  
(4)listId == 4 && (listFieldValue > 125 && listFieldValue < 175)

(继续到数组的其余部分)

我测试了(全部)& (包含)当我具有一维数组时，

I tested (all) & (contains) when I had 1D array,

var q = from flds in tblFildsVal  
        group flds by flds.parrentId into gFields  
        where myArr.All(i => gFields.Select(co => co.listFieldValue).Contains(i)) 

但在这种情况下我不知道！

but I have no idea in this case!

推荐答案

您的问题是您需要实现匹配逻辑.一个简单的全部"或包含"调用将不满足您的数组提供的匹配规则.我在下面实现了一个示例解决方案，该解决方案将显示您将如何编写查询以对照匹配逻辑进行检查.

Your problem is that you need to implement your matching logic. A simple All or Contains call will not satisfy the matching rules provided by your array. I've implemented an example solution below that will show how you would need to write a query to check against the matching logic.

class Program
{
    static void Main(string[] args)
    {
        var myMatchConditions = new int?[][]
            { 
                new int?[] { 1, 1, 100, null }, 
                new int?[] { 2, 2, 125, null }, 
                new int?[] { 3, 3, null, 175 }, 
                new int?[] { 4, 4, 125, 175 }
            };

        var myData = new MyTableItem[]
            {
                new MyTableItem { id = 1, listId = 1, listFieldValue = 150, parentId = 1 },
                new MyTableItem { id = 2, listId = 1, listFieldValue = 75, parentId = 1 },
                new MyTableItem { id = 3, listId = 2, listFieldValue = 150, parentId = 1 },
                new MyTableItem { id = 4, listId = 4, listFieldValue = 150, parentId = 1 },
                new MyTableItem { id = 5, listId = 5, listFieldValue = 150, parentId = 1 },
            };

        var matches = from d in myData
                      where myMatchConditions.Any(cond => (
                                (cond[0] == d.listId) && 
                                (cond[1] == 1 ? d.listFieldValue == cond[2] :
                                    (cond[1] == 2 ? d.listFieldValue > cond[2]  :
                                        (cond[1] == 3 ? d.listFieldValue < cond[3] :
                                            (cond[1] == 4 ? d.listFieldValue > cond[2] && d.listFieldValue < cond[3] : false)
                                        )
                                    )
                                )
                            ))
                      group d by d.parentId into g
                      select g;

    }

    class MyTableItem
    {
        public long id { get; set; }
        public long listId { get; set; }
        public long listFieldValue { get; set; }
        public long parentId { get; set; }
    }
}

要注意的重要事情是对数组进行逻辑检查.在这里，我通过Any调用和一些嵌套的三元运算符来完成此操作，这不是我建议的最终解决方案.

The important thing to note is the logic checking against the array. Here, I've accomplished it by an Any call, and some nested ternary operators, which isn't a final solution that I'd recommend.

如果您可以为您的匹配条件显式创建一个类，然后使用诸如bool MatchesMe(...)之类的方法可以对每个字段进行检查，则可以更加清晰易懂，从而可以从视觉上将匹配的逻辑与LINQ语句.

It could be made much more legible if you could explicitly create a class for your match conditions, then have some method like bool MatchesMe(...) which would check against each field, so that the logic for the matching could be separated visually from the LINQ statement.

注意:要绕开使用实体框架的限制，您可能需要将匹配条件填充到数据库中的表中，这样才能在匹配逻辑中将记录用作对象；或者，更简单地说，您可以将数据库表中的结果作为IEnumerable取回，然后执行与我的示例中几乎相同的匹配逻辑.

Note: To get around restrictions working with the entity framework, you would either need to populate your match conditions to a table in the database, so you can then have records to use as objects in your matching logic; or, much simpler, you can get the results from the database table back as an IEnumerable, and then perform the matching logic much the same as in my example.

我建议创建一个listId参数的列表，因此您只回退需要根据范围条件进行验证的数据库记录.因此，举例来说，假设您使用myMatchConditions中的listId值设置了List<int> myListIdFilters.然后，您只需对逻辑检查的第一行进行更改:

I would recommend creating a list of the listId parameters, so you are only pulling back database records that will need to be validated against the range condition. So, for example, lets say you setup a List<int> myListIdFilters with the listId values from myMatchConditions. You would then make a change just to the first line of the logic check:

var matches = from d in myData.Where(myDataItem => myListIdFilters.Contains(myDataItem.listId)).AsEnumerable()

其余的将保持不变. AsEnumerable调用将导致IQueryable对象被解析，因此其余逻辑将在EF(或LINQ to SQL)上下文之外执行.

And the rest would remain the same. The AsEnumerable call would cause the IQueryable object to be resolved, so the remainder of the logic would be performed outside of the EF (or LINQ to SQL) context.

这篇关于linq查询，其中包含类列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！