CODEWITHSUNDEEP
c#linq
Y.Solanki
Y.Solanki

Reputation: 45

Linq join between IQueryable and List<int> is taking too long to be executed

I have this simple join query which is taking too long to execute.

filteredTags = (from ft in filteredTags
                join at in activeTagIds on ft.ID equals at
                select ft).ToArray();

Here filteredTags is the Iqueryable and activeTagIds is the List.

When I tried to see the built query it is doing an UNION ALL to all the ids in activeTagIds, which is what making my query execution slow.

....
UNION ALL
    SELECT 
    5620 AS [C1]
    FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]
UNION ALL
    SELECT 
    5621 AS [C1]
    FROM  ( SELECT 1 AS X ) AS [SingleRowTable3]
.... so on..

How do I avoid this extra long query and make it simpler for faster execution time?

Upvotes: 3

Views: 1171

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236188

Just check whether filtered tag id is in active tag ids:

filteredTags = filteredTags.Where(ft => activeTagIds.Contains(ft.ID)).ToArray()

Or if you prefer mix of query and method syntax:

filteredTags = (from ft in filteredTags
                where activeTagIds.Contains(ft.ID)
                select ft).ToArray();

Both will generate IN SQL query.

Upvotes: 2

Related Questions