CODEWITHSUNDEEP
linqrazorgroup-byentity-framework-6
Jay
Jay

Reputation: 3082

LINQ query for a GroupBy and how this is displayed in razor view

I have a class called MeteredGenerationInformation that contains three relevant fields to the question SettlementDate, GenerationUnitMeteredGeneration and LossAdjustedGenerationUnitMeteredGeneration. There are thousands of these records and I would like to group by the Settlement date and return sums of the two values for records across a particular date range.

I now have the following LINQ query:

IQueryable<IGrouping<DateTime, MeteredGenerationInformation>> mgList = db.MeteredGenerationInformations.Where(mg => mg.IntervalPeriodTimestamp >= intervalSearch.StartDate && mg.IntervalPeriodTimestamp <= intervalSearch.EndDate && mg.messageType598.SettlementRunIndicator == intervalSearch.SettlementRunIndicator).GroupBy(mg => mg.messageType598.SettlementDate);

How can I display this in my view so that I have a settlement date and the summed values for both other fields? I have tried the code and while it renders the view I am unable to work out how to display this on view:

@model System.Data.Entity.Infrastructure.DbQuery<System.Linq.IGrouping<System.DateTime, Reconciliation.Models.MeteredGenerationInformation>>

Upvotes: 1

Views: 391

Answers (2)

Jay
Jay

Reputation: 3082

To solve this I had to pass the model as follows:

var mgList = db.MeteredGenerationInformations.Where(mg => mg.messageType598.SettlementDate >= aggregationSearch.StartDate && mg.messageType598.SettlementDate <= aggregationSearch.EndDate && mg.messageType598.SettlementRunIndicator == aggregationSearch.SettlementRunIndicator).GroupBy(mg => mg.IntervalPeriodTimestamp).ToArray();
return View("MeteredGenerationInformations", mgList);

I then referenced the model in the view as follows:

@model System.Linq.IGrouping<System.DateTime, Reconciliation.Models.MeteredGenerationInformation>[]

I was then able to display in the view using this code:

@foreach (var group in Model)
    {
        <tr>
            <td>@group.Key</td>
            <td>@group.Sum(i => i.GenerationUnitMeteredGeneration)</td>
            <td>@group.Sum(i => i.LossAdjustedGenerationUnitMeteredGeneration)</td>
        </tr>
    }

Upvotes: 0

Janus Pienaar
Janus Pienaar

Reputation: 1103

 var summedResult = db.MeteredGenerationInformations.Where(mg => mg.IntervalPeriodTimestamp >= intervalSearch.StartDate && mg.IntervalPeriodTimestamp <= intervalSearch.EndDate && mg.messageType598.SettlementRunIndicator == intervalSearch.SettlementRunIndicator)
                .GroupBy(mg => mg.messageType598.SettlementDate)
                .SelectMany(s => s.Select(sl => new
                {
                    GroupedDate = sl.SettlementDate,
                    SumGenerationUnitMeteredGeneration = s.Sum(c => c.GenerationUnitMeteredGeneration),
                    SumLossAdjustedGenerationUnitMeteredGeneration = s.Sum(c => c.LossAdjustedGenerationUnitMeteredGeneration),
                }));

Should give you what you're looking for.

Upvotes: 1

Related Questions