首页 / 知识

如何使用LINQ to SQL处理IN子查询？

2023-04-14 15:55:00

How can you handle an IN sub-query with LINQ to SQL?

我对此有些困惑。基本上，我想在LINQ to SQL中执行以下SQL查询：

1
2
3
4
5
6
7

SELECT f.*
FROM Foo f
WHERE f.FooId IN (
SELECT fb.FooId
FROM FooBar fb
WHERE fb.BarId = 1000
)

任何帮助将不胜感激。

谢谢。

在LINQ to SQL中实现IN的一般方法

1
2
3
4
5
6

var q = FROM t1 IN table1
let t2s = FROM t2 IN table2
WHERE <Conditions FOR table2>
SELECT t2.KeyField
WHERE t2s.Contains(t1.KeyField)
SELECT t1;

在LINQ to SQL中实现EXISTS的一般方法

1
2
3
4
5
6

var q = FROM t1 IN table1
let t2s = FROM t2 IN table2
WHERE <Conditions FOR table2>
SELECT t2.KeyField
WHERE t2s.Any(t1.KeyField)
SELECT t1;

看看这篇文章。基本上，如果要获得IN的等效值，则需要先构造一个内部查询，然后使用Contains()方法。这是我的翻译尝试：

1 2	var innerQuery = FROM fb IN FoorBar WHERE fb.BarId = 1000 SELECT fb.FooId; var RESULT = FROM f IN Foo WHERE innerQuery.Contains(f.FooId) SELECT f;

1
2
3
4
5
6
7
8
9

FROM f IN Foo
WHERE f.FooID ==
(
FROM fb IN FooBar
WHERE fb.BarID == 1000
SELECT fb.FooID

)
SELECT f;

尝试使用两个单独的步骤：

1
2
3
4
5
6
7
8
9

// CREATE a Dictionary / SET / Collection fids FIRST
var fids = (FROM fb IN FooBar
WHERE fb.BarID = 1000
SELECT NEW { fooID = fb.FooID, barID = fb.BarID })
.ToDictionary(x => x.fooID, x => x.barID);

FROM f IN Foo
WHERE fids.HasKey(f.FooId)
SELECT f

//首先创建一个Dictionary / Set / Collection fids

查找其他关节炎

1
2
3
4
5
6
7
8

var fids = (FROM fb IN FooBar
WHERE fb.BarID = 1000
SELECT NEW { fooID = fb.FooID, barID = fb.BarID })
.ToDictionary(x => x.fooID, x => x.barID);

FROM f IN Foo
WHERE fids.HasKey(f.FooId)
SELECT f

1
2
3
4
5
6

FROM f IN foo
WHERE f.FooID equals model.FooBar.SingleOrDefault(fBar => fBar.barID = 1000).FooID
SELECT NEW
{
f.Columns
};

//首先创建一个Dictionary / Set / Collection fids

查找其他关节炎

1
2
3

var fids = (FROM fb IN FooBar WHERE fb.BarID = 1000 SELECT NEW { fooID = fb.FooID, barID = fb.BarID }) .ToDictionary(x => x.fooID, x => x.barID);

FROM f IN Foo WHERE fids.HasKey(f.FooId) SELECT f