user6805346 user6805346 - 2 months ago 15
C# Question

Convert SQL code to LINQ - Group By and Sum

can not convert to this code to LINQ and preview to data grid view.

I have looked at this answer but it is not helping me

select tbl_user.id,tbl_user.name,tbl_user.family, sum(tbl_price.price)
from tbl_user,tbl_price
where tbl_user.id=tbl_price.user_id_fk
group by tbl_user.name+''+tbl_user.family,tbl_user.id,tbl_user.name,tbl_user.family


please help me to convert this code to linq

Answer

You basically need to join the two tables, group the result by the UserId and call the Sum method on the Price property value for each items for the User.

Something like this

var usersWithTotalPrice = (from a in db.Users
                           join b in db.UserPrice on a.UserId equals b.UserId
                           select new { UserId = a.UserId, 
                                        FamilyName = a.Name + " " + a.FamilyName, 
                                        Price = b.Price}
                          ).GroupBy(f => f.UserId, items => items, (a, b) => new
                                     {
                                          UserId = a,
                                          FamilyName = b.FirstOrDefault().FamilyName ,
                                          Price = b.Sum(g=>g.Price)
                                     }
                          ).ToList();

usersWithTotalPrice variable will be a collection of items each with a UserId, FamilyName and Price property. I used anonymous projection. If you have a view model, you can use that.

public class UserWithPrice
{
  public int Id { set;get;}
  public string FamilyName { set;get;}
  public decimal Price { set;get;}
}

and use that in the projection part

var usersWithTotalPrice = (from a in db.Users
                           join b in db.UserPrice on a.UserId equals b.UserId
                           select new { UserId = a.UserId, 
                                        FamilyName = a.Name + " " + a.FamilyName, 
                                        Price = b.Price}
                          ).GroupBy(f => f.UserId, items => items, (a, b) => 
                                   new UserWithPrice
                                   {
                                          Id = a,
                                          FamilyName = b.FirstOrDefault().FamilyName ,
                                          Price = b.Sum(g=>g.Price)
                                    }
                          ).ToList();

Update your entity/Dbset/Property names based on your definition.

Comments