user6805346 user6805346 - 3 months ago 25
C# Question

Convert SQL code to LINQ

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

I watch this address but not helping me .enter link description here

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.