CODEWITHSUNDEEP
c#sqllinqentity-framework
Jim
Jim

Reputation: 11379

In the entity framework, how would you do an "IN" query on a joined table?

I'm a SQL junkie, and the syntax of the EF is not intuitive to me.

I have a Restaurant table and a Food table. I want the restaurants and foods where the foods have a type contained in the string list Categories. Here is some SQL that roughly represents what I want.

SELECT r.*, f.*
FROM Restaurant R
  JOIN food f on f.RestaurantID = r.RestaurantID
WHERE f.Type IN ("Awesome", "Good", "Burrito")

Here's the code I want to turn into that SQL.

List<string> types = new List<string>() { "Awesome", "Good", "Burrito"};
var dbrestaurants = from d in db.Restaurants
                    .Include("Food")
                    //where Food.Categories.Contains(types)//what to put here?
                    select d;

Upvotes: 0

Views: 94

Answers (3)

RePierre
RePierre

Reputation: 9566

Try this:

var dbRestaurants = 
from r in db.Restaurants
join f in db.Foods on r.RestaurantId equals f.RestaurantId
where types.Any(foodType => foodType == f.Type)
select r;

Upvotes: 0

Phil
Phil

Reputation: 42991

Try

var restaurants = db.Restaurants.Where(r => types.Contains(r.Food.Type));

Upvotes: 1

usr
usr

Reputation: 171178

where Food.Categories.Any(c => types.Contains(c.Name))

Upvotes: 0

Related Questions