CODEWITHSUNDEEP
linqentity-frameworkselect
Alexander Shlenchack
Alexander Shlenchack

Reputation: 3869

Select nested list

I have three tables in DB like this:

products

+----+------+
| id | name |
+----+------+
| 1  | prod1|
+----+------+

values

+----+---------+---------+
| id | value   |  alias  | 
+----+---------+---------+
| 1  |  10 g   |  10m    |
+----+---------+---------+

prdoucts_values

+---------------+---------+
|  product_id   | value_id| 
+---------------+---------+
|      1        |  1      | 
+---------------+---------+

How select all products from DB, that have any value from List<Values.alias> ?

Upvotes: 1

Views: 289

Answers (2)

Jens Kloster
Jens Kloster

Reputation: 11277

If you are using EF (database first) then the table prdoucts_values is not part of the conseptual model.

instead EF givs you a direct path from Products to Values Therefore you can write a query that look like this:

var lst = new List<Products>();
lst = db.Products.Where(c => c.Values.Any()).ToList();

Upvotes: 0

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236188

List<decimal> aliases = ...
var query = db.Products.Where(p => p.Values.Any(v => aliases.Contains(v.Alias)));

Or (if you don't have navigation properties)

var query = from p in db.Products
            join pv in db.ProductsValues on p.ProductId equals v.ProductId
            join v in db.Values on pv.ValueId equals v.ValueId into values    
            where values.Any(v => aliases.Contains(v.Alias))
            select p

Upvotes: 1

Related Questions