“Contains” with LINQ to Entities

By James at April 22, 2011 21:40
Filed Under: Entity Framework

This post is mostly so I don’t forget, but if it helps you, then that’s groovy too.

In SQL you can use the IN clause to find a bunch of files where say the Id number is IN(1,2,3,4,5,6). However there isn’t that capability using LINQ to Entities.

I need this functionality for a project I’m working on and ran across some Bing results which gave me some ideas. Here’s how I did it.


var ressFileList = _db.RessFiles.AsEnumerable().Where(db => _ressFileIds.Contains(db.Id)).ToList<RessFile>();


Where _ressFileIds is a list I’ve generated elsewhere. The key is to use AsEnumerable() when calling the table you want to find the Id values in. Works like a champ.


Comments (2) -

4/23/2011 9:02:39 PM #


The problem with .AsEnumerable in the chain is that the entire table will be pulled to the client and the .Where filter will be executed as a Linq-to-Objects query on the entity objects. That can be costly if your table is large, both DB-side (have to read the entire table), network-wise (have to transfer all that data to the client) and client-side (has to materialize entity objects for each record and then filter out the unwanted ones).

If you're using EFv4 (EF under .net 4.0) then .Contains is supported and will translate into a SQL WHERE ... IN (... clause. There may be some edge cases due to data type, but it should work for int/long/string/guid etc. Make sure the array or list is declared as the appropriate (and matching the underlying entity member's) type too, if _ressFileIds is a List<int> or int[] it should work fine, but if it is a List<object> it won't.

If you're using EFv1 (EF under .net 3.5), then this workaround from Alex James can be used to generate a query with a pile of OR instead of a SQL IN: blogs.msdn.com/.../...-using-linq-to-entities.aspx

KristoferA Thailand | Reply

4/24/2011 2:03:51 PM #


Ok, I changed my LINQ to this:

var ressFileList = from rfl in _db.RessFiles
                   where _ressFileIds.Contains(rfl.Id)
                   select rfl;

and it works. I was munging up the syntax, trying to use where rfl.Id.Contains, not thinking about doing it the way you suggested.

Thanks Kristofer, you're my new go to guy for Entity questions. Smile

James United States | Reply

Add comment

  Country flag
  • Comment
  • Preview

About the author

James James is a five time and current Microsoft MVP in Client App Development, a Telerik Insider, a past Director on the INETA North America Board, a husband and dad, and has been developing software since the early days of Laser Discs and HyperCard stacks. As the Founder and President of the Inland Empire .NET User's Group, he has fondly watched it grow from a twice-a-month, early Saturday morning group of five in 2003, to a robust and rambunctious gathering of all types and sizes of .NET developers.

James loves to dig deep into the latest cutting edge technologies - sometimes with spectacular disasters - and spread the word about the latest and greatest bits, getting people excited about developing web sites and applications on the .NET platform, and using the best tools for the job. He tries to blog as often as he can, but usually gets distracted by EF, LINQ, MVC, ASP, SQL, XML, and most other types of acronyms. To keep calm James plays a mean Djembe and tries to practice his violin. You can follow him on twitter at @latringo.

And as usual, the comments, suggestions, writings and rants are my own, and really shouldn't reflect the opinions of my employer. That is, unless it really does.

James Twitter Feed

Recent Comments

Comment RSS

Month List