developing LINQ query where column values can be hard coded but row headings are dynamic

Question

In my company we have total 41 products ( U basins, T seats, W basins, Urinals, White W basins, Pink W basins ...), but the numbers of distributors are changing monthly.

We have to develop a report where we have to show the quantity of Products distributed by each distributors. So the format will be that the Product Name will form the column heading and the Distributors name will be row heading. We already know the Product Name so we can hard code in the code. But the name of Distributors is changing, so we can't hard code them.

Below is the model class and sample data set.

public class DistributionScale
    {
        public int DistributionScaleId { get; set; }
        [Required]
        public decimal Quantity { get; set; }       
        
        [ForeignKey("ProductId")]
        public Product Products { get; set; } 
        public int ProductId { get; set; }

        [ForeignKey("DistributorsId")]
        public Distributors Distributors { get; set; }
        public int DistributorsId { get; set; }
    }
    
public class Product 
    {
        public int ProductId { get; set; }        
        public string ProductName { get; set; }        
    }
    
 public class Distributors
    {
        public int DistributorsId { get; set; }
        public string DistributorsName { get; set; }                 
    }
    
I have provided some sample data:
List<Distributors> Distributors = new List<Distributors>(){
    new Distributors{DistributorsId = 1, DistributorsName = "S S SUPPLIERS"},
    new Distributors{DistributorsId = 2, DistributorsName = "HIND SUPPLIERS"},
    new Distributors{DistributorsId = 3, DistributorsName = "NEXT SUPPLIERS"}
};
List<Product> Product = new List<Product>{
    new Product{ ProductId = 1, ProductName = "U basins" },
    new Product{ ProductId = 2, ProductName = "Urinals" },
    new Product{ ProductId = 3, ProductName = "White W basins"}
};
List<DistributionScale> DistributionScale = new List<DistributionScale>(){
    new DistributionScale{DistributionScaleId = 1, Quantity = 1000 ,ProductId =1 ,DistributorsId =1    },
    new DistributionScale{DistributionScaleId = 2, Quantity = 1500,ProductId =2 ,DistributorsId =  2 },
    new DistributionScale{DistributionScaleId = 3, Quantity = 2500,ProductId =3 ,DistributorsId =  3 }
    new DistributionScale{DistributionScaleId = 4, Quantity = 1200,ProductId =1 ,DistributorsId =  3 },
    new DistributionScale{DistributionScaleId = 5, Quantity = 1300,ProductId =2 ,DistributorsId =  1 }
};

Below is the simple query what I am trying to do. I do not how to make the product name as column heading and show the distribution data as attached in the image.

public JsonResult ReportDistributionScale()
        {
                var res = (from n in db.DistributionScale
                           join s in db.Product on n.ProductId equals s.ProductId
                           join k in db.Distributors on n.DistributorsId equals k.DistributorsId
                           orderby n.ProductId
                           select new DistributionScaleViewModel
                           {
                                ProductName = s.ProductName
                               ,DistributorsName = k.DistributorsName
                               ,Quantity = n.Quantity
                           }).ToList();

                return Json(res, JsonRequestBehavior.AllowGet);           
        } 
 
//// another way I am trying 

var res = from n in db.DistributionScale
join s in db.Product on n.ProductId equals s.ProductId
join k in db.Distributors on n.DistributorsId equals k.DistributorsId
  group new
  {
        s.ProductName
       ,k.DistributorsName
       ,n.Quantity
  } by ProductName into g
  select new {
    ///// how to proceed further    
  }).ToList();

Attached is the image. How can I write LINQ to get the final output as shown in the image.

Answer 1

So you have a one-to-many relation between Products and DistributionScales: Every Product has zero or more DistributionScales; every DistributionScale belongs to exactly one Product, namely the Product that the foreign key ProductId refers to.

Similarly you also have a one-to-many relation between Distributors and DistributionScales: Every Distributor has zero or more DistributionScales, every DistributionScale belongs to exactly one Distributor, namely the Distributor that the foreign key DistributorId refers to.

In fact, this makes the relation Products - DistributionScales a many-to-many relation with a junction table that has an extra value: Quantity.

Normally in this situation the combination of [ProductId, DistributorId] in the DistributionScales would be unique. You don't want the following in your DistributorScales:

• [Id: 1, ProductId 10 (= Urinals), DistributorId 20 (= Next Suppliers), Quantity 30]
• [Id: 2, ProductId 10 (= Urinals), DistributorId 20 (= Next Suppliers), Quantity 40]

How many Urinals does Next Suppliers have? 30? 40? 70?

And how do you solve the problem if Next Suppliers won't supply Urinals anymore? After you have removed these two, you'll still have to check the whole table to see if there is not a third combination [10, 20]

If you make the combination [ProductId, DistributorId] the primary key, you won't have this problem, you can't have more than one [ProductId, DistributorId]. Fetch / Update / Remove will be much faster.

Back to your question

For every Distributor give me the quantity of all Products that this Distributor distributes and a zero quantity for all Products that this Distributor does not Distribute.

It is no use to start with the DistributionScales, if you do so, you will never get Products that are not-distributed at all, nor get the Distributors that don't Distribute anything.

You need to start either at the Product side, or at the Distributor side: give me all Distributors, each with the Product that they Distribute and the Products that they don't distribute.

var distributorsAndTheirProducts = dbContext.Distributors.Select(distributor => new
{
   Name = distributor.Name,

   DistributedProducts = dbContext.DistributionScales
       .Where(distributionScale => distributionScale.DistributorId == distributor.Id)
       .Select(distributionScale => new
       {
           ProductName = dbContext.Products
               .Where(product => product.Id == distributionScale.ProductId)
               .Select(product => product.Name)
               .FirstOrDefault(),
           Quantity = distributionScale.Quantity,
       },
   NonDistributedProducts = ... // TODO

You know there will be exactly one Product with this ProductId, so if you want you can use Single instead of FirstOrDefault. Some entity framework systems won't accept this, they want SingleOrDefault, or FirstOrDefault.

If you think you might have several [Urinals, Next Suppliers] combinations, you'll have to sum the quantities.

Now to get all Products that are not Distributed by this Distributor, even the Products that are Distributed by no one and thus are not in the DistributionScales table, start at the Products side.

From the table of Products keep only those Products that have no DistributionScale for this product and DistributorId. From the remaining products get the Name and a quantity zero:

    NonDistributedProducts = dbContext.Products
    .Where(product => !dbContect.DistributionScale
        .Where(distributionScale => distributionScale.ProductId == product.Id
                                 && distributionScale.DistributorId == distributor.Id)
        .Any())

So it says: Where NOT exists any DistributionScale with combination [ProductId, DistributorId]

   .Select(product => new
   {
       ProductName = product.Name,
       Quantity = 0,
   })

So now you have per Distributor, even for the Distributors that don't distribute anything:

• Name,
• DistributedProducts: a sequence of ProductName / Quantity combinations
• NonDistributedProducts: a sequence of ProductName / 0 combinations

It is easy to see, that if you UNION the DistributedProducts and the NonDistributedProducts, you get per Distributor all Products with their quantities, and a zero quantity for the products that this Distributor does not distribute.

So put it all together, hold your horses, because it will be one big LINQ:

var distributorsAndTheirProducts = dbContext.Distributors.Select(distributor => new
{
   Name = distributor.Name,

   Products = dbContext.DistributionScales
       .Where(distributionScale => distributionScale.DistributorId == distributor.Id)
       .Select(distributionScale => new
       {
           ProductName = dbContext.Products
               .Where(product => product.Id == distributionScale.ProductId)
               .Select(product => product.Name)
               .FirstOrDefault(),
           Quantity = distributionScale.Quantity,
       })

       // UNION with the nonDistributedProducts
       Union(dbContext.Products
           .Where(product => !dbContect.DistributionScale
               .Where(distributionScale => 
                   distributionScale.ProductId == product.Id
                && distributionScale.DistributorId == distributor.Id)
           .Any())
           .Select(product => new
           {
               ProductName = product.Name,
               Quantity = 0,
           }))
           .ToList(),
    });
     

So now you have per Distributor: its Name, and all Products, the one that he Distributes and the ones that he doesn't distribute. For every of these Products: the name and the quantity, or zero if the product isn't distributed by this Distributor. This is even for the Distributors who don't distribute anything, and the Products that are distributed by no one.