Linq one to many relations as comma separated values

Question

I have a product table with one to many relationship to another 2 tables.

Product:

1. ProductId
2. ProductName
3. IsDeleted (bit)

ProductTag:

1. ProductTagId
2. ProductId
3. TagName
4. IsDeleted (bit)

ProductCategory:

1. ProductCategoryId
2. ProductId
3. CategoryName
4. 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()

Answer 1

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

var query = from product in context.Products
            let tags = from tag in context.Tags
                        where !tag.IsDeleted
                            && tag.ProductId == product.ProductId
                        select tag.TagName
            let categories = from category in context.Categories
                        where !category.IsDeleted
                         && category.ProductId == product.ProductId
                        select category.CategoryName
            where !product.IsDeleted
            select new
            {
                Id = product.ProductId,
                Name = product.ProductName,
                Tags = string.Join(", ", tags)
                Categories = string.Join(" ", categories)
            };

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

  modelBuilder.Entity<Foo>().Map(m => m.Requires("IsDeleted").HasValue(false));
  modelBuilder.Entity<Bar>().Map(m => m.Requires("IsDeleted").HasValue(false));