1

In SQL I can use the IN operator:

select * from project where id IN (select f.id_project from funds)

How can I convert this statement to LINQ?

Controller.Project.Where(p => p.id == id_funds); // I can assign only one id funds
Martin Liversage
  • 104,481
  • 22
  • 209
  • 256
Robert
  • 2,571
  • 10
  • 63
  • 95

1 Answers1

2

Use the Contains method:

Controller.Project.Where(p => id_funds.Contains(p.id));

Don't think it applies to your question as it appears id_funds links to a db table but...
Note that if id_funds is an object (i.e. a simple array or list etc.), not something in the database and you're not using Linq-To-Objects you may encounter issues with this as i don't think Linq-To-Entities before v 4.0 supports it (see here for a workaround), and Linq-To-SQL has problems if id_funds is a very large list (more than around 2000 items).

Community
  • 1
  • 1
George Duckett
  • 31,770
  • 9
  • 95
  • 162
  • From the question it looks like funds is also a table, so there should not be any problems. – Magnus Dec 15 '11 at 09:14
  • id_funds is int, so we cannot use Contains with this variable – Robert Dec 15 '11 at 09:51
  • It would be `Controller.Funds.Any(f => f.id = p.id)` then i guess – George Duckett Dec 15 '11 at 10:02
  • Ok, sorry, I did List id_funds = Controller.Funds.Select(...) and now works very well. Thanks – Robert Dec 15 '11 at 10:10
  • 1
    Don't put the Funds Id's into a local list, you can do this entirely on the DB using a single query. – Magnus Dec 15 '11 at 10:20
  • But what is better ? I think that linq is faster than executing query in DB ? There are several positions only in these lists. – Robert Dec 15 '11 at 12:16
  • @Robert: Querying in the database is definately going to be faster. If you don't then you have to wait for the database to give you every single record so you can get it in memory as a list, then you filter out what you don't want. If you run it on the database you only get what you want. Databases are built to perform fast queries so will be better than linq to objects if you're already going to the database for something anyway. – George Duckett Dec 15 '11 at 12:21
  • Yes but I have already loaded Funds nad Projects. And I have to only use Linq, and thats all. And to quering DB I have to open db, execute reader, load all records to list and close db. And I've mentioned in projects and funds will be max 20-30 records. – Robert Dec 15 '11 at 13:48
  • @Robert: Read up on linq to sql and linq to entities. If you are using these, then all queries will be on the database (the linq method calls are translated into SQL or whatever). If you are using linq on in memory objects then just do that. What i'm saying is, that you want the database to return the relevent results straight away, if you already have some results that were used for something else too, then by all means don't query the DB. – George Duckett Dec 15 '11 at 14:07
  • I don't use them. I have my own MVC library. Funds and Projects all loaded at the beginning when app starts into static collections. Then I only user linq on them and thats all. And I think this is faster solution than quering DB. Obviously when I need execute sophisticated query I do it on DB. But simple I think I can do with Linq – Robert Dec 15 '11 at 14:46
  • If starting up the app doesn't take that long then sure, that's a good way of doing it. – George Duckett Dec 15 '11 at 14:59