绩效:.加入vs.包含-对实体的Linq [英] Performance: .Join vs .Contains - Linq to Entities

问题描述

我正在使用Linq对实体进行查询，以查询数据库以获取int列表以进行进一步处理.我有两种获取列表的方法，如下所示:

I am using Linq to entities to query the database to get the list of int for further processing. I have two ways to get the list as below:

第一个是:

List<int> lstBizIds = new List<int>() { 1, 2, 3, 4, 5 };
List<int> lstProjectIds = context.Projects.Where(x => lstBizIds.Contains(x.businessId)).Select(x => x.projectId).ToList();

第二个是

List<int> lstBizIds = new List<int>() { 1, 2, 3, 4, 5 };
List<int> lstProjectIds = context.Projects.Join(lstBizIds, p => p.businessId, u => u, (p, u) => p.projectId).ToList();

现在我的问题是，哪种方法在性能上更好?如果第一个列表(即lstBizIds)的大小增加，还会影响性能吗?如果会降低性能，请向我提出其他实施方式.

Now my question is which one of the methods above is better performance wise? Also does it affect the performance if the first list i.e. lstBizIds grows in size? Suggest me other ways of implementation as well if that are performance reducing.

推荐答案

您应该使用Contains，因为EF可以产生更有效的查询.

You should go with Contains, because EF can produce a more efficient query.

这将是SQL连接:

SELECT Id
FROM Projects
INNER JOIN (VALUES (1), (2), (3), (4), (5)) AS Data(Item) ON Projects.UserId = Data.Item

这将是SQL包含的内容:

This would be the SQL Contains:

SELECT Id
FROM Projects
WHERE UserId IN (1, 2, 3, 4, 5, 6)

IN比JOIN更有效率，因为DBMS可以停止寻找IN的第一个匹配项. JOIN始终会完成，即使在第一个比赛之后也是如此.

IN is more efficient than JOIN because the DBMS can stop looking after the first match of the IN; the JOIN always finishes, even after the the first match.

您可能还想检查哪些查询实际发送到了数据库.您总是必须比较SQL，而不是LINQ代码(显然).

You might also want to check which queries are actually sent to the DB. You always have to compare the SQL, not the LINQ code (obviously).

这篇关于绩效:.加入vs.包含-对实体的Linq的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！