How to filter many to many joins in EF Core

Question

I have the structure below:

• Product Category
• Product (with one Product Category)
• ProductComplementCategory (Product has many ProductComplementCategory)
• ComplementCategory (ProductComplementCategory has one ComplementCategory)
• ComplementCategoryComplements (ComplementCategory has many ComplementCategoryComplements)
• Complement (ComplementCategoryComplements has one Complement)

All tables has the property "Active" and I need to select all tables with join but filtering active = 1 in tables Product Category, Product, ComplementCategory and Complement

Query in SQL:

SELECT
  pc.*,
  p.*,
  cc.*,
  c.*
FROM
  ProductCategory pc
  JOIN Product p ON pc.[ Uid ] = p.ProductCategoryId
  JOIN ProductComplementCategory pcc ON p.[ Uid ] = pcc.ProductID
  JOIN ComplementCategory cc ON pcc.ComplementCategoryID = cc.[ Uid ]
  JOIN ComplementCategoryComplements ccc ON cc.[ Uid ] = ccc.ComplementCategoryID
  JOIN Complement c ON ccc.ComplementID = c.[ Uid ]
WHERE
  pc.Active = 1
  AND p.Active = 1
  AND cc.Active = 1
  AND c.Active = 1

I need to do this query in EF Core!

Answer 1

i hope this would help you

 var temp = (
                from pc in ProductCategory
                join p in Product
                    on pc.Uid equals p.ProductCategoryId
                join pcc in ProductComplementCategory
                    on pcc on p.Uid equals pcc.ProductID
                join permission in ComplementCategory
                    on pcc.ComplementCategoryID equals cc.Uid
                join cc in ComplementCategoryComplements
                    on cc.Uid equals ccc.ComplementCategoryID
                join c in Complement
                    on ccc.ComplementID equals c.Uid
                where pc.Active = 1 && p.Active = 1 && cc.Active = 1 && c.Active = 1
                select new { pc,p,pcc,c,cc }
            ).ToList