Convert SQL code to LINQ - Group By and Sum

Question

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

Make an attempt yourself and come back with what you have tried that isn't working... https://stackoverflow.com/questions/12238423/linqpad-convert-sql-to-linq-command — Wyatt Earp, Sep 07 '16 at 16:06
Please never just post SQL and ask for conversion. At least show a class model so navigation properties and the multiplicity of associations are visible. Also, tell what type of LINQ you're targeting (to entities?), *and* show your own first efforts. They clarify more to us than you might think. — Gert Arnold, Sep 07 '16 at 19:04

score 0 · Accepted Answer · answered Sep 07 '16 at 16:31

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.

score 0 · Answer 2 · answered Sep 07 '16 at 18:33

You don't even need to use linq for this. Actually you can use datatable, and set the datasource of the gridview to that datatable.

  public void LoadData()
        {
            DataTable dt = new DataTable();
            using (var conn = new SqlConnection("your connection string here"))
            {
                using (var cmd = conn.CreateCommand())
                {
                    conn.Open();

                    //set the command up as a select
                    cmd.CommandText = "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";

                    using (SqlDataAdapter a = new SqlDataAdapter(cmd))
                    {
                        a.Fill(dt);
                    }

                    //set the datasource of the gridview to the loaded table.  The gridview will now display the data of your select statement.
                    dgvActivity.DataSource = dt;

                }
            }
        }

Convert SQL code to LINQ - Group By and Sum

2 Answers2