how to show data in list from multiple join having lot of column of complex object/class

Question

These are below tables. I can not modify it

Brand 
Id  Name  IsRegisterd  
1   ABC   True
2   XYZ   True

Product

Id  Name BrandId  Active Version
1    Soap  ABC      True   1.0
2    Soap  ABC      True   2.0
3    Oil   Xyz      True 

I need output like BrandName,ProductName And list of version. Below is my query.But unable to fetch list of version.How to do that.

from b in DbContext.Brand
join p in oneAssetContext.Products on Brand.Id equals p.BrandId into PslJ
from Ptagged in PslJ.Where(p => (P.Active == true)).DefaultIfEmpty()
where IsRegisterd = true
select new SinglePoductDetails
{
    BrandName = Brand.Name,
    Product = Product.Name,
    Version =
}

Answer 1

I need output like BrandName,ProductName And list of version. Below is my query.But unable to fetch list of version.How to do that.

Based on your shared code snippet, I have tried to reproduce your scenario and found couple of issues.

First of all, within your query, the context and variable names are not used property. Specifically, the variable Brand and Product hasn't been tagged in correct way.

Another major mistake is, the DefaultIfEmpty method is used incorrectly in the join clause. You are trying to use it in a way that doesn't fit the context of a left join.

The most obvious flow is, you haven't used the group by clause the way you should.

Solution:

In order To fetch and display data according to your scenario and description you should use GroupBy and SelectMany in appropiate manner, so that it will fetch the list of product versions for each brand.

All you need to do is, group the data by brand and product name and then Select the desired fields, including the list of versions.

Let's have a look in practice how we could achieve that:

Demo Model:

public class Brand
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool IsRegistered { get; set; }
}

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int BrandId { get; set; }
    public bool Active { get; set; }
    public string Version { get; set; }
}

public class SingleProductDetails
{
    public string BrandName { get; set; }
    public string ProductName { get; set; }
    public List<string> Versions { get; set; }
}

Note: I have made above class based on your shared query, feel free to adjust the table according to your real code.

Controller:

public IActionResult Index()
 {
     var brands = new List<Brand>
     {
         new Brand { Id = 1, Name = "ABC", IsRegistered = true },
         new Brand { Id = 2, Name = "XYZ", IsRegistered = true }
     };

     var products = new List<Product>
     {
         new Product { Id = 1, Name = "Soap", BrandId = 1, Active = true, Version = "1.0" },
         new Product { Id = 2, Name = "Soap", BrandId = 1, Active = true, Version = "2.0" },
         new Product { Id = 3, Name = "Oil", BrandId = 2, Active = true, Version = "1.0" },
         new Product { Id = 4, Name = "Oil", BrandId = 2, Active = true, Version = "1.1" },
         new Product { Id = 5, Name = "Oil", BrandId = 2, Active = false, Version = "2.0" }
     };

    
     var result = from b in brands
                  join p in products on b.Id equals p.BrandId
                  where b.IsRegistered == true && p.Active == true
                  group p by new { BrandName = b.Name, ProductName = p.Name } into g
                  select new SingleProductDetails
                  {
                      BrandName = g.Key.BrandName,
                      ProductName = g.Key.ProductName,
                      Versions = g.Select(x => x.Version).ToList()
                  };

     var productDetailsList = result.ToList();

     
     return View(productDetailsList);
 }

View:

@model List<SingleProductDetails>
<h2>Product Details</h2>
<table class="table">
    <thead>
        <tr>
            <th>Brand Name</th>
            <th>Product Name</th>
            <th>Versions</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var item in Model)
        {
            <tr>
                <td>@item.BrandName</td>
                <td>@item.ProductName</td>
                <td>@string.Join(", ", item.Versions)</td>
            </tr>
        }
    </tbody>
</table>

Output:

Note: Please refer to this official document for details about group by and group join