Feature Request: - For SqlServer generate join on table variable instead of generating WHERE IN clause when .Contains is used in the EF query

[yahorsi]

Hi Guys!

Sometimes people need to check if the database column value is in a list of predefined values, and so we do something like filter.FunctionIds.Contains(x.FunctionId). On the Microsoft SQL Server that get's translated into the TSQL WHERE IN clause.

Problem 1.
If a list of values changes, that will result in the query text being different. Once the query is different - SQL server must parse/compile it and in that case, it can't use a cached plan. Sometimes, especially if the list passed to .Contains is big, it might take a lot of time, but even if it's small, compiling quires is something that takes time. In our case, we observe about 20ms for the relatively small query with a small, but changing list used in .Contains.

Problem 2.
Sometimes, people don't think a lot and pass really big lists to the .Contains and that kills perf.

Solution
Make it possible to utilize Sql Server Table Variable feature, so, all the dynamic values could be passed as a parameter and instead of generating WHERE IN EntityFramework could generate join in the table variable. At least for the Microsoft SQL Server. Open question here is what type should be used to declare table variable type but that I believe has many different solutions.

Thanks

[ErikEJ]

There are many duplicates of this issue

[yahorsi]

Any chance this could be implemented?
I was thinking if there is something EF team does not want to implement, then, maybe TSQL generation phase could be customized? And so, we will do it ourselves?

[ErikEJ]

@yahorsi We have been using a (slightly modified) version of this with great success:

https://github.com/divega/ContainsOptimization/blob/master/ContainsOptimization/CollectionPredicateBuilder.cs

[yahorsi]

@ErikEJ Could you please point to any sample that utilizing it?

[smitpatel]

Related #15593 #13617
Blocked on #13239

[yahorsi]

Holly cow, I just have realized that this is actually already possible to achieve just by changing EF query to use FromSql & table valuated parameter.

[smitpatel]

Duplicate of #13239