I have a product table with one to many relationship to another 2 tables.
Product:
- ProductId
- ProductName
- IsDeleted (bit)
ProductTag:
- ProductTagId
- ProductId
- TagName
- IsDeleted (bit)
ProductCategory:
- ProductCategoryId
- ProductId
- CategoryName
- IsDeleted (bit)
I need a result like the following in a single linq query and while using join I need to check the IsDeleted flag too. Please help.
Required output:
1 | Adidas_Shoes | shoes,new,adidas,sport | Mens,Shoes,Adidas
Just tried to join 1 table as follows
(from p in Product join pt in ProductTag on p.ProductId equals pt.ProductId into productTags from pt in productTags.DefaultIfEmpty() where (pt==null || !pt.IsDeleted) select new { ProductId = p.ProductId, ProductName = p.ProductName, Tags = string.Join(",", pt.TagName.ToArray()) }).Distinct().ToList()
you should have posted some code that you have tried with, maybe a slimmed down version of your DbContext, but in the end you need to iterate over your products and left join on tag and category names
Note: Due to the lack of any mappings information i simply assume that you don't have navigational properties defined, but feel free to adapt the above to match your particular setup
Note: in case you find yourself always excluding deleted entities from your query you should start considering tackling EF soft delete scenarios, and among those one is to deal with discriminators