using .Contains() with linq-to-sql

Question

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.

Why are you looking for a better way? What do you think is bad about what you're doing now? — svick
– svick, Commented Jan 27, 2012 at 14:39

Adam Robinson · Accepted Answer · 2012-01-27 14:26:42Z

9

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.

answered Jan 27, 2012 at 14:26

Adam Robinson

186k36 gold badges294 silver badges351 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Luke Over a year ago

LastOrDefault will even throw an error that this linq is not able to translate it into sql.

Marcel Perju · Accepted Answer · 2018-10-16 17:31:47Z

4

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.

edited Oct 16, 2018 at 17:31

answered Mar 13, 2015 at 8:30

Marcel Perju

2022 silver badges11 bronze badges

Comments

Roy Dictus · Accepted Answer · 2012-01-27 14:28:18Z

1

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

answered Jan 27, 2012 at 14:28

Roy Dictus

33.3k9 gold badges64 silver badges77 bronze badges

Collectives™ on Stack Overflow

using .Contains() with linq-to-sql

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related