CODEWITHSUNDEEP
c#asp.netlinqlinq-to-sql
frenchie
frenchie

Reputation: 51927

using .Contains() with linq-to-sql

I have the following query that receives a list of ints as a parameter:

public int GetMostRecent(List<int> TheIDs)
{
 ...using MyDC...

   var TheMostRecentID = (from d in MyDC.Data
                           where TheIDs.Contains(d.ID)
                           orderby d.DateTime
                           select d.ID).LastOrDefault(); 
}

Is this the best way to match a list within a parameter collection to data in the database or is there a better way than using the .Contains() method in linq-to-sql.

Thanks.

Upvotes: 3

Views: 6251

Answers (3)

Marcel Perju
Marcel Perju

Reputation: 202

You should be careful with such queries with list.Contains() inside of linq query. Because for each list element it will create a param in sql statement.

And there's a limited number of params allowed in sql statement, <= 2100. So if your TheIDs will contains more than 2100 elements it will fail.

If you want to use in this way, you should at least check your TheIDs count and if more then 2100 dived it in pieces with less then 2100 elements.

Upvotes: 4

Roy Dictus
Roy Dictus

Reputation: 33139

This will translate into efficient SQL, so there's no need to use anything else.

Upvotes: 1

Adam Robinson
Adam Robinson

Reputation: 185643

What you have is correct. This will be translated into an IN clause in SQL with the values supplied in the collection.

On an unrelated note, you should try ordering the query by date descending and use FirstOrDefault(). As it is now, you're going to bring back the entire result set and throw away every row but one.

Upvotes: 9

Related Questions