SQL stored procedure to Lambda

Question

I have been developing a small project and have trouble getting around a sql stored procedure into a lambda. My project is C# MVC Code first. I have looked into some posts around here but with no success.

The first version is where Total is minutes rounded up (for each minute) and second is just all seconds converted into minutes: Rounded up every minute:

    select ISNULL(noT,'?') as NType,count(*) as TotalCount,SUM((Duration + 59) / 60) as TotalMinutes
    from cHis
    where hDate between @fromDate AND @toDate And Customer_CustomerID = @CustomerID and I_ID = @I_ID
    group by ISNULL(noT,'?') 

Rounded up total into minutes:

    select ISNULL(noT,'?') as NType,count(*) as TotalCount,SUM((Duration) / 60) as TotalMinutes
    from cHis
    where hDate between @fromDate AND @toDate And Customer_CustomerID = @CustomerID and I_ID = @I_ID
    group by ISNULL(noT,'?')

I am open to suggestions and hopefully some ideas for solutions.

Thank you for the help in advance.

Answer 1

You can do this using lambda by grouping the type and then doing a sum and round on each record thus getting a total of the individual rounded records.

Assuming you wanted to round up each records duration, the lambda would be something close to this:

cHis.Where(c=>c.Date > StartDate && c.Date< EndDate && Customer_CustomerID == CustomerId && I_ID == ID)
.Select(c=>new {Type = (c.noT != null), c.Duration})
.GroupBy(c=>c.Type).ToList()
.Select(c=> new {Type = c.Key, TotalDuration = c.Sum(d=>Math.Ceiling((decimal)d.Duration/60)), Count = c.Count()})