实施"不"在LINQ逻辑(又名"不存在") [英] Implement "not in" (aka "not exists") logic in LINQ
问题描述
设置
- 我有两个
列表< T>
的 - 的数据是未归,并从不同的来源,解释了所需的逻辑卷积
- 在数据的非正式的复合键是FIELDA,fieldB ,fieldC
- 在田都是字符串 - 引用类型 - 所以它们的值可能为空。我想放弃,他们可以在空进行匹配的记录。我得到的,在C#空引用会匹配,但在SQL他们不这样做。添加
!string.IsNullOrEmpty()
是很容易的。 - 这是不是一个关于DB设计或关系代数的问题。
- 我有涵盖其他标准的其他逻辑。不建议减少显示,这样它可能会拓宽结果集的逻辑。见上文第5。
问题
我想找到的在listA的记录不在数组listB基于正规钥匙。然后我想根据部分关键比赛进一步细化为listA结果
问题的SQL版本:
选择
listA.fieldA,listA.fieldB,从为listA
matching.fieldC
左加入数组listB键列表上
listA.fieldA = keyList.fieldA和
listA.fieldB = keyList.fieldB和
listA.fieldC = keyList.fieldC
内部联接上
listA.fieldA = matching.fieldA和$数组listB匹配b $ b listA.fieldB = matching.fieldB
,其中
keyList.fieldA为空
的 SQL到LINQ(案例7 - 通过使用和NOT IN子句筛选数据)
注意: IN和NOT IN使用相同的函数在LINQ查询,但它只是使用! (不)符号吧。下面是图示:
您使用其中,<列表>。载(小于项目>)
我的产品变种=从db.Products
p其中productList.Contains(p.ProductID)
器选择p;
或者你可以有一个列表预先定义为这样的:
VAR IDS = {1,2,3};
VAR的查询=从项目中context.items
,其中ids.Contains(item.id)
选择项;
有关的不的情况下,只需添加!运算符之前的'包含'语句。
Setup
- I have two
List<T>
's. - The data is un-normalized and from different sources which explains the convolution in the desired logic
- An informal compound key in the data is fieldA, fieldB, fieldC.
- The "fields" are strings - reference types - so their values could be null. I want to drop records where they may be matching on null. I get that null references in C# will match, but in SQL they do not. Adding a
!string.IsNullOrEmpty()
is easy enough. - This is not a question about DB design or relational algebra.
- I have other logic which covers other criteria. Do not suggest reducing the logic shown such that it might broaden the result set. See # 5 above.
The Problem
I want to find the records in listA that are not in listB based on the informal key. I then want to further refine the listA results based on a partial key match.
The SQL version of the problem:
select
listA.fieldA, listA.fieldB, matching.fieldC
from listA
left join listB keyList on
listA.fieldA = keyList.fieldA and
listA.fieldB = keyList.fieldB and
listA.fieldC = keyList.fieldC
inner join listB matching on
listA.fieldA = matching.fieldA and
listA.fieldB = matching.fieldB
where
keyList.fieldA is null
SQL to LINQ ( Case 7 - Filter data by using IN and NOT IN clause)
Note: IN and NOT IN use the same function in the LINQ query, but it just use a ! (not) symbol for it. Here is the graphical representation:
You use, where <list>.Contains( <item> )
var myProducts = from p in db.Products
where productList.Contains(p.ProductID)
select p;
Or you can have a list predefined as such:
var ids = {1, 2, 3};
var query = from item in context.items
where ids.Contains( item.id )
select item;
For the 'NOT' case, just add the '!' operator before the 'Contains' statement.
这篇关于实施"不"在LINQ逻辑(又名"不存在")的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!