1

From what I am aware doing a join on in-memory data basically loads the entire data set in, e.g.

var ids = new int[] { 1, 2, 3}
var data = context.Set<Product>().Join(ids, x => x.Id, id => id, (x, id) => x)

This will cause the entire 'Product' table to be loaded into memory. In my current case this is not an option as it contains millions of records.

The common solution I have seen is to use Contains(), e.g.

var ids = new int[] { 1, 2, 3}
var data = context.Set<Product>().Where(x => ids.Contains(x.Id));

which generates an SQL IN clause like WHERE Id IN (1, 2, 3)

But what if the in-memory list is very large? Does this create any issues if say you have an IN clause with 1000's of values? or 10's of thousands?

Is there some alternative query that creates different (better?) SQL?

Mark
  • 2,041
  • 2
  • 18
  • 35
user380689
  • 1,766
  • 4
  • 26
  • 39
  • http://stackoverflow.com/questions/1069415/limit-on-the-where-col-in-condition – Craig Selbert Mar 08 '16 at 02:36
  • Unfortunately `Contains` (generating SQL `IN`) is the only option. And yes, it does create performance problems if the value list is big. But again, this is what you have, there is no alternatives (in EF). – Ivan Stoev Mar 08 '16 at 08:37

0 Answers0