Linq2sql 优化左连接以获取仅存在于 1 个容器中的项目
我想从一个容器中获取另一个容器中不存在的物品。一个容器是IEnumerable，另一个容器是DB中的实体。例如，
IEnumberable<int> ids = new List<int>();
ids.Add(1);
ids.Add(2);
ids.Add(3);
using (MyObjectContext ctx = new MyObjectContext())
{
var filtered_ids = ids.Except(from u in ctx.Users select u.id);
}
这种方法有效，但我意识到底层sql类似于SELECT id FROM [Users]。那不是我想要的。更改它以
var filtered_ids = ids.Except(from u in ctx.Users
where ids.Contains(u.id)
select u.id);
改进底层查询并添加 WHERE [id] IN (...) 这似乎是一种更好的方法。
我有 2 个问题：
是否可以进一步提高该查询的性能？
据我所知，
IN中可以包含的参数数量是有限制的。如果超出限制（这种情况不太可能发生，但最好做好准备），我当前的查询会起作用吗？
I want to get items from one container that don't exist in another. One container is IEnumerable, and another is an entity in DB. For example
IEnumberable<int> ids = new List<int>();
ids.Add(1);
ids.Add(2);
ids.Add(3);
using (MyObjectContext ctx = new MyObjectContext())
{
var filtered_ids = ids.Except(from u in ctx.Users select u.id);
}
This approach works, but I realized that underlying sql is something like SELECT id FROM [Users]. That is not what I want. Changing it to
var filtered_ids = ids.Except(from u in ctx.Users
where ids.Contains(u.id)
select u.id);
improves underlying query and adds WHERE [id] IN (...) which seems a way better.
I have 2 questions:
Is it possible to improve performance any further for this query?
As far as I remember there is a limit on how many parameters can be in
IN. Will my current query work if I exceed the limit (which is not very likely to happen, but it's better to be prepare) ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果有适当的索引/主键，该查询应该没问题。
sql server 接受的 sql 参数上限为 2100 左右，如果超过该限制，将会得到 sql 异常，而不是结果。
That query should be fine, provided proper indexes/primary keys are in place.
The upper limit on sql parameters accepted by sql server is around 2100. If you exceed the limit, you will be met with a sql exception instead of results.