CODEWITHSUNDEEP
c#.netlinq
koryakinp
koryakinp

Reputation: 4135

LINQ intersect() method

I have following tables:

enter image description here

Each CoffeeLover has several FavouriteCoffeeRecords associated with him. I want to get list of CoffeeNames for a given CoffeeLoverID using LINQ query.

I guess I need to use Intersect() method. I have tried like this:

using(DataContext db = new DataContext())
{   
    var favCofRecords = db.FavouriteCoffeesRecords.Where(q => q.CoffeeLover.CoffeeLoverID == 1);
    var favCoffees = db.Coffees.Select(q => q.CoffeeID).Intersect(favCofRecords.Select(q2 => q2.CoffeeID));
}

But failed to get the right result.

Upvotes: 2

Views: 103

Answers (2)

usr
usr

Reputation: 171246

You are thinking in a too complicated way. You want the coffee names for which there is a relationship with a given CoffeeLoverID:

var results = 
 from c in db.Coffees
 where c.FavouriteCoffeesRecords.Any(rec => rec.CoffeeLoverID == someID)
 select c.Name;

This is literally the query that I described in prose.

When you want to query columns from a certain table, start with that table in the from clause and filter it.

Upvotes: 1

Selman Genç
Selman Genç

Reputation: 101742

I think what you need is a join

from f in db.FavouriteCoffeesRecords
join c in db.Coffees on f.CoffeeID equals c.CoffeeID
where f.CoffeeLover.CoffeeLoverID == 1
select c.CoffeeName

Upvotes: 1

Related Questions