linq-to-entity List/Array .Contains compiles a query using "WHERE (... OR ...)" instead of "WHERE ... IN (...)"

Question

EntityFramework v6.0

IBM.Data.DB2.EntityFramework v10.5

.NET Framework v4 / C# v4.0 (yes, working on upgrading)

Example:

List<long> myIds = new List<long>{ 12345, 23456, 34567 };

IQueryable<myModel> myCollection = (from a in context.TBL_ONE
                                    join b in context.TBL_TWO on a.ENTY equals b.ENTY
                                    select new myModel()
                                    {
                                         LINE_ID = a.LINE_ID
                                    }).AsQueryable();

var results = myCollection.Where(x => myIds.Contains(x.LINE_ID)).ToList(); // here is the issue...

The "myCollection" will be very large, and so will the "myIds" collection.

So would prefer it to compile to

    SELECT ... WHERE LINE_ID IN (12345, 23456, 34567);

however, it compiles to

    SELECT ... WHERE LINE_ID = 12345 OR LINE_ID = 23456 OR LINE_ID = 34567;

I have tried HashSet, List, and Array - as well as large or small, they all give the same compiled style. Is this due to outdated linq or .net version?

Please let me know if I can provide more information or formatting, I am still a beginner. Thank you

Answer 1

The in clause has limitations, so you won't be able to stuff 50k into it, you'll just get an error.

You can solve your problem using temp tables in the following way:

1. Create a temp table
2. Bulk/Batch insert your IDs into a temp table
3. Join your temp table with primary

For inserting data into your table you can use SqlBulkCopy, if you're using SQL Server or find any other bulk copy solutions for your DB engine.