Entity Framework Contains turn to super slow cast

Question

EF function

public IEnumerable<PromoteRequestRelationship> GetDependentsByRequestIds(List<long> requestIds, List<string> dependencyTypes) 
{
    return (
        from pbd in Session.All<promote_build_depends>()
        join pr in Session.All<promote_request>() on pbd.DEP_PARENT_REQ_ID equals pr.REQ_ID
        join pr2 in Session.All<promote_request>() on pbd.DEP_DESC equals pr2.REQ_ITEM
        where requestIds.Contains(pr.REQ_ID) && dependencyTypes.Contains(pbd.DEP_TYPE)
        select new PromoteRequestRelationship {
            ...
        }
    );
}

The where clause is translated like this:

WHERE ([Extent2].[REQ_ID] IN (CAST(2751163 AS bigint), CAST(2752582 AS bigint), CAST(2752585 AS bigint))) 
  AND ([Extent1].[DEP_TYPE] IN (N'Interdependent', N'Dependent')) 
  AND ([Extent1].[DEP_TYPE] IS NOT NULL)

The query took me almost 10 mins to be completed.

However, if I remove the cast part.

WHERE ([Extent2].[REQ_ID] IN (2751163, 2752582, 2752585)) 
  AND ([Extent1].[DEP_TYPE] IN (N'Interdependent', N'Dependent')) 
  AND ([Extent1].[DEP_TYPE] IS NOT NULL)

This runs in only 12 seconds, how could I prevent the EF to cast to bigInt?

OK. Before run your query, convert to int and the problem is solved. — Alireza Ahmadi, Jun 02 '21 at 04:30

score 1 · Accepted Answer · answered May 28 '21 at 23:07

1

You are passing in a list of long which translates to sql's bigint datatype, try passing in a list of int instead. What is the equivalent of bigint in C#?

answered May 28 '21 at 23:07

Cristian Franco

56
3

Entity Framework Contains turn to super slow cast

1 Answers1