CODEWITHSUNDEEP
asp.netlinqentity-framework-6
mak
mak

Reputation: 21

Group By Asp net core

I'm making a small application related to the queue, and I'm stuck at one point. I have context data 

public class DATA_ABONENT_ARCHIVE
{
    [Key]
    public int ID { get; set; }
    public int NUM { get; set; }
    public string PREFIX { get; set; }
    public string FULL_NUM { get; set; }
    public DateTime DATETIME_REGISTR { get; set; }
    public DateTime DATE_REGISTR { get; set; }
    public TimeSpan TIME_REGISTR { get; set; }
    public int SPR_USLUGI_ID { get; set; }
    public Nullable<int> SPR_LGOTA_ID { get; set; }
    public DateTime DATETIME_START_SERVICE { get; set; }
    public DateTime DATE_START_SERVICE { get; set; }
    public TimeSpan TIME_START_SERVICE { get; set; }
    public int SPR_WINDOW_ID { get; set; }
    public int SPR_OPERATOR_ID { get; set; }
    public string FIO { get; set; }
    public DateTime DATETIME_STOP_SERVICE { get; set; }
    public DateTime DATE_STOP_SERVICE { get; set; }
    public DateTime TIME_STOP_SERVICE { get; set; }
    public int SPR_STATUS_ID { get; set; }
    public int PRIORITET { get; set; }
    public int PRERECORD { get; set; }
    public Nullable<DateTime> START_TIMEOUT { get; set; }
    public Nullable<DateTime> STOP_TIMEOUT { get; set; }
    public int SPR_BASE_ID { get; set; }
}

In the controller, I have to make a selection and get a list of models containing 3 date fields, the number of people for that day, the average waiting time

public class AverageExpectationViewModel
{
    public ReportResultModel ReportResult { get; set; }
    public IEnumerable<AverageWaitingModel> AverageWaitingOutgoing { get; set; } 
}

The output model contains two message fields and a list

I do

IEnumerable<AverageWaitingModel> AverageWaiting = Db_Resource.DATA_ABONENT_ARCHIVE

                .Where(c => c.SPR_BASE_ID == Convert.ToInt32(data.BranchId))
                .Where(c => c.DATE_REGISTR >= startData)
                .Where(c => c.DATE_REGISTR <= endData)
                .GroupBy(b=>b.DATE_REGISTR)  
                .Select(c => new AverageWaitingModel
                {
                    DateRegistr = c.Key,
                    Count = c.Count(),
                    //Waiting = (Math.Round(c.TIME_START_SERVICE.TotalMinutes - c.TIME_REGISTR.TotalMinutes, 2))
                })
                .ToList();

I do so but I do not know how to go any further and find the average value while shoveling, maybe you have some nice ideas how it can be done

Upvotes: 1

Views: 12006

Answers (1)

mak
mak

Reputation: 21

I decided to do so, maybe someone will help.

        IEnumerable<AverageWaitingModel> AverageWaiting = await Db_Resource.DATA_ABONENT_ARCHIVE 
            .Where(c => c.SPR_BASE_ID == Convert.ToInt32(data.BranchId))
            .Where(c => c.DATE_REGISTR >= startData)
            .Where(c => c.DATE_REGISTR <= endData)
            .GroupBy(b=> new { b.DATE_REGISTR })  
            .Select(c => new AverageWaitingModel
            {
                DateRegistr = c.Key.DATE_REGISTR,
                Count = c.Count(),
                Waiting = Math.Round(c.Average(p=>(p.TIME_START_SERVICE.TotalMinutes - p.TIME_REGISTR.TotalMinutes)),2) 
            })
            .ToListAsync();

The key to the grouping is the date, in the date field of the new model, the key was recorded. Using the Count () function, I got the number of elements in a group. And the average waiting time has rounded to fractional and caused the method of the Average difference between the two fields

Upvotes: 1

Related Questions