CODEWITHSUNDEEP
c#linqlinq-to-sql
Hoody
Hoody

Reputation: 3381

Performance of Linq Contains

I need to filter a linq query using a list of filters and I plan on using the contains method to do so. So it will look something like this.

List<string> filter = new List<string>();
filter.Add("foo");
filter.Add("bar");
//Additional filters go here, max of about 10 filters
var test = dbcontext.books.Where(x => filter.Contains(x.name)).ToList();

The table behind this query has a lot of records (500,000), a PK identity field and an index on the field I'll be querying.

My questions is before going down this route would you expect the performance of this query to be acceptable or should I use a different approach on such a large dataset?

Upvotes: 1

Views: 2830

Answers (2)

JustinDanielson
JustinDanielson

Reputation: 3185

If all the data is already in memory for other reasons, I suppose you could use this approach. Otherwise, a database would probably be better as you won't be loading 500k records into memory at one time.

select * from Books b where b.name IN {"foo", "bar", ...}

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1062715

Yes, that should be fine; that will just translate to

select x.* from [books] x where x.name in (@p0,@p1)

with @p0 = 'foo' and @p1 = 'bar'; as long as name is indexed it should do fine. It gets more interesting, though, if filter gets very large. 10 items sounds fine. I'm also implicitly assuming it won't return all 500,000 - but will restrict to a sane number; if not, consider adding some .Take(number) limits.

If concerned, just measure it. It is pretty easy to run a SQL trace, or use a profiling tool.

Upvotes: 6

Related Questions