how can Join and Group by id between Two Tables and Get Sum of Fields - Linq C# through Id?

Question

  private class DataBindingProjection
    {
        public int WorkerId { get; set; }
        public string WorkerName { get; set; }
        public string Address { get; set; }
        public string Contact { get; set; }
        public string ReferenceName { get; set; }
        public string ReferenceContact { get; set; }
        public int TotalDistribute { get; set; }
        public int TotalCollection { get; set; }
        public int TotalDeposit { get; set; }
        public int TotalPayment { get; set; }
        public int TotalPayable { get; set; }
    }

    private void WorkerLIstForm_Load(object sender, EventArgs e)
    {
        var totalDistributed = (db.Distributions.AsEnumerable().GroupBy(d => d.WorkerId)).Select(a => new
        {
            WorkerId = a.Key,
            Amount = a.Sum(r => r.Piece)
        }).ToList();

        var collection = (db.Collections.AsEnumerable().GroupBy(d => d.WorkerId)).Select(a => new
        {
            WorkerId = a.Key,
            Piece = a.Sum(r => r.Piece),
            Deposit = a.Sum(r => r.PayableDeposit)

        }).ToList();

        var workerPayment = (db.WorkerPayments.AsEnumerable().GroupBy(d => d.WorkerId)).Select(a => new
        {
            WorkerId = a.Key,
            Payment = a.Sum(r => r.Payment),
            PayableAmount = a.Sum(r => r.PayableAmount)
        }).ToList();


        var worker = from w in db.Workers
                     join d in totalDistributed on w.WorkerId equals d.WorkerId
                     join c in collection on d.WorkerId equals c.WorkerId
                     join wp in workerPayment on w.WorkerId equals wp.WorkerId
                     select new DataBindingProjection
                     {
                         WorkerId = w.WorkerId,
                         WorkerName = w.WorkerName,
                         Address = w.Address,
                         Contact = w.Contact,
                         ReferenceName = w.ReferenceName,
                         ReferenceContact = w.RefereceContact,
                         TotalDistribute = d.Amount,
                         TotalCollection = c.Piece,
                         TotalDeposit = c.Deposit,
                         TotalPayment = wp.Payment,
                         TotalPayable = wp.PayableAmount
                     };
        workerDataGridView.DataSource = worker.ToList();

    }

This is my code I want to load data through workerId and sum their specific column but I face this error..System.NotSupportedException: 'Unable to create a constant value of type 'Anonymous type'. Only primitive types or enumeration types are supported in this context.'

Is my code is wrong?
If it's wrong which the correct or better approach for it?

add `.ToList()` just after the query – Vivek Nuna Jun 21 '20 at 12:01 — Vivek Nuna, Jun 21 '20 at 12:01
Sorry I don't understand where ? Maybe i already added – Rakib Ahasan Jun 21 '20 at 12:05 — Rakib Ahasan, Jun 21 '20 at 12:05

score 0 · Accepted Answer · answered Jun 21 '20 at 16:16

First thing you could try:

var worker = from w in db.Workers
     .Where(w => totalDistributed.Select(td => td.WorkerId).Contains(w.WorkerId))
     .Select(w =>  new {  w.WorkerId,
                         w.WorkerName,
                         w.Address,
                         w.Contact,
                         w.ReferenceName,
                         w.RefereceContact})
     .ToList()
    join d in totalDistributed on w.WorkerId equals d.WorkerId
....

Also I would say that you can try to perform this whole query on db side. Try removing AsEnumerable and ToList from workerPayment, collection and totalDistributed in your original code.

score 0 · Answer 2 · answered Jun 21 '20 at 16:20

  private class DataBindingProjection
    {
        public int CustomerId { get; set; }
        public string CustomerName { get; set; }
        public string Address { get; set; }
        public string Contact { get; set; }
        public string ReferenceName { get; set; }
        public string ReferenceContact { get; set; }
        public int TotalSaleOfPiece { get; set; }
        public int TotalReturn { get; set; }
        public int TotalDue { get; set; }

    }

    private void CustomerListForm_Load(object sender, EventArgs e)
    {
        var customer = from c in db.Customers
                       join s in db.Sales on c.CustomerId equals s.CustomerId
                       group c by new { c.CustomerId } into y
                       from customer1 in db.Customers.DefaultIfEmpty().ToList()
                       select new DataBindingProjection
                       {
                           CustomerId = customer1.CustomerId,
                           CustomerName = customer1.ShopName,
                           Address = customer1.Address,
                           Contact = customer1.Contact,
                           ReferenceName = customer1.ReferenceName,
                           ReferenceContact = customer1.RefereceContact,
                           TotalSaleOfPiece = customer1.Sales.Sum(m => (int?)m.Piece) ?? 0,
                           TotalReturn = customer1.Sales.Sum(m => (int?)m.ReturnPiece) ?? 0,
                           TotalDue = customer1.Sales.Sum(m => (int?)m.Due) ?? 0,
                       };
        customerDataGridView.DataSource = customer.ToList();
    }

//by using this code data load in drid view properly but 1 data repeated 3 times where is the problem?

how can Join and Group by id between Two Tables and Get Sum of Fields - Linq C# through Id?

2 Answers2