CODEWITHSUNDEEP
c#asp.netlinq-to-sqlgridview
ariel
ariel

Reputation: 1

Linq-to-SQL gridview add sum column

I have this linq query in GridView

 var table = from ll in dbo.Workers
                    join p in dbo.WorkDays on ll.Id equals p.Id
                    orderby p.Enter
                    select new
                    {
                        ll.Id,
                        ll.Name,
                        ll.Salary,
                        p.Enter,
                        p.ExitT,
                        p.Place,
                        WorkTime = Math.Round(getWorkTime(p.Enter, p.ExitT), 2),
                        Earned = Math.Round(getEarned(p.Enter, p.ExitT, ll.Salary), 2),
                    };

How can I add column of sum to the table?

Upvotes: 0

Views: 975

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236328

Create class which will hold data and provide calculations based on that data:

public class WorkerInfo
{
    // I don't know exact type of fields, but its enough for you to get the idea
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Salary { get; set; }
    public DateTime Enter { get; set; }
    public DateTime ExitT { get; set; }
    public string Place { get; set; }

    public int WorkTime
    {
       get { return (ExitT - Enter).TotalDays; }
    }

    public decimal Earned
    {
       get { return WorkTime * Salary; }
    }
}

Fill that these entities with data:

var workers = from ll in dbo.Workers
              join p in dbo.WorkDays on ll.Id equals p.Id
              orderby p.Enter
              select new WorkerInfo
              {
                 Id = ll.Id,
                 Name = ll.Name,
                 Salary = ll.Salary,
                 Enter = p.Enter,
                 ExitT = p.ExitT,
                 Place = p.Place
              };

UPDATE Sample for returning aggregated data for all days:

var query = from w in dbo.Workers
            join d in dbo.WorkDays on w.Id equals d.Id into days
            let WorkTime = days.Sum(d => d.ExitT - d.Enter)
            select new 
              {
                 w.Id,
                 w.Name,
                 w.Salary,
                 WorkTime,
                 Earned = WorkTime * w.Salary
              };

Upvotes: 1

Related Questions