linq to sql“全部”运算符类型查询

发布于 2024-12-05 10:30:54 字数 565 浏览 1 评论 0原文

String[] codes = new[] {"C1", "C2" }

下面是 SomeEntity 的架构，

fkey  code
--------------
f1    C1
f1    C2
f2    C2
f2    C3
f2    C4
f3    C5

我必须编写可以根据 2 个条件获取所有实体的查询 -

1）该实体是否具有任何代码 2）该实体是否具有我尝试使用 All 运算符编写第二个条件的所有代码

Below is what i wrote for 1st condition
-----------------------------------------
from f in dc.GetTable<SomeEntity>
where codes.Contains(f.code)
select f;

，但“All”运算符对于 linq to sql 无效。

问题：如何编写一个仅返回具有所有代码的实体的查询？

原文

String[] codes = new[] {"C1", "C2" }

Below is the schema for SomeEntity

fkey  code
--------------
f1    C1
f1    C2
f2    C2
f2    C3
f2    C4
f3    C5

I have to write queries that can get all the entities based on 2 condition -

1) does the entity have any of the codes
2) does the entity have all the codes

Below is what i wrote for 1st condition
-----------------------------------------
from f in dc.GetTable<SomeEntity>
where codes.Contains(f.code)
select f;

I tried to write 2nd codition by using All operator but "All" operator is not valid for linq to sql.

Question: How to write a query that will return only those entities that have all the codes ?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦中楼上月下 2024-12-12 10:30:54

var codeCount = dc.GetTable<SomeEntity>.Distinct(e => e.Code);
var matching = (from f in dc.GetTable<SomeEntity>
   group f by f.Key into grouped
   select new { f.Key, values = grouped}).Where(v => v.values.Count() == codeCount);

可能是一个可怕的查询，映射到数据上下文上的函数的存储过程可能是一个更好的解决方案，老实说

您可能可以将 codeCount 作为 let 绑定在查询中并进行 linq 语句，而不是关闭到 .Where称呼。不确定它是否会提高性能，但会节省额外的数据库访问

var codeCount = dc.GetTable<SomeEntity>.Distinct(e => e.Code);
var matching = (from f in dc.GetTable<SomeEntity>
   group f by f.Key into grouped
   select new { f.Key, values = grouped}).Where(v => v.values.Count() == codeCount);

Probably a hideous query, a stored proc mapped to a function on the data context might be a better solution to be honest

You could probably do the codeCount as let binding in the query and carry on the linq statement rather than close out to the .Where call. Not sure if it'd make it more performant but would save an extra trip to the DB

回复收藏 0 原文

最后的乘客 2024-12-12 10:30:54

试试这个。

按 fkey 分组（这将创建不同的 fkey）
过滤其中的组
- 代码列表中的所有成员都包含在该组的代码中

请参见下文

var matching = dc.GetTable<SomeEntity>()
    .GroupBy(e => e.fkey)
    .Where(group => codes.All(c => 
        group.Select(g => g.code).Contains(code)))
    .Select(group => group.Key);

Try this.

Group by fkey (which will create distinct fkeys)
Filter the groups where
- All members of the list of codes are contained within the codes of that group

See below

var matching = dc.GetTable<SomeEntity>()
    .GroupBy(e => e.fkey)
    .Where(group => codes.All(c => 
        group.Select(g => g.code).Contains(code)))
    .Select(group => group.Key);

回复收藏 0 原文

~没有更多了~