EF Core Accessing a list relation

Question

i have a question about ef core query and ill be appreciated if you friends can help me ! i have a .net core mvc project , that has three (classes) tables including , Products , Groups and ProductToGroups with these relations : in products and groups table (class) :

public List<ProductToGroup> ProductToGroups { get; set; }

and in ProductToGroup table :

 public Product Product { get; set; }
 public Group Group { get; set; }

lets assume that i have 2 product with productId 1,2 , and have 2 group with groupId 1,2 and in ProductToGroup table i declared that the product id 1 has the groupId 1,2 and also for productId 2 , it has groupId 1,2 ! ive wrote these query and gets products on a list:

IQueryable<Products> result = _context.Products.Include(p => p.ProductToGroups);

now i want to write a query that gets all products with groupId that i sent to it from result using ProductToGroups table (PS. its a list relation and if i use single or first it just take the first group that stored in database , for example if i want to gets products with groupId=2 , it returns null , and just returns groupId=1) ! Thanks alot!

Answer 1

if i want to gets products with groupId=2

Try

            int[] groupid = { 2};
            var products  = _context.Products.Include(u => u.ProductToGroups).ThenInclude(u => u.Groups).ToList();

            var list =products.Where(x => groupid.All(r => x.ProductToGroups.Any(y => y.Groups.groupId== r)));

My demo is about UserRole, like your ProductToGroups.

Result: