Performance: .Join vs .Contains - Linq to Entities

Question

I use Linq for entities to query the database to get an int list for further processing. I have two ways to get the list as shown below:

First up is:

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();

Second:

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();

Now my question is: which of the above methods works best? Does it also affect performance if the first ie lstBizIds list grows in size? Suggest me other implementation methods if this is a performance hit.

Answer 1

You have to go with Contains, because EF can create a more efficient request.

SQL:

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

SQL :

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

IN , JOIN, IN; JOIN , .

, . SQL, LINQ ().

Answer 2

, , , , . , Where (n1 * n2 * n3 * n4)

Join , , .. -, , , join

Answer 3

, .
, .

Answer 4

, LINQ, .

ICollection ~ 10k sql- , "join" "Contains" .

, , , Contains .