Entity Framework Group Summing: 1 - Use Linq


Been at it again trying this way then that, Doing more searches trying to find something that fits. Eventually did get something that fits the bill. Needed a database grouping computation. Could always assemble the data in an app and then do Linq to make the query. Can it be done more directly at the database?

The Problem

With the Aths Helpers app, it’s end of season and time to determine who has volunteered the most via the app, so we can thanks and award them It’s easy enough to create a Sql query to count the number of entries for each helper of the season. An attempt was then made to use RawSQL from C# in the Blazor appto directly use that query. Problem was needing to return a type. Eventually the solution was to do this directly using LInq; it took several tries though.

Recap

As discussed in previous posts here, a Blazor web app has been developed to manage an athletics club’s volunteering for duties on competition days. The club is assigned a number of specific duties on any given day. These are then entered into the app ny admin and helpers can log in and select activities that they are willing to perform. If multiple persons are required for the same activity, then multiple entries are submitted at set up. When a helper selects a duty it appears in the app as selected. Only an admin person can deselect a person for a duty; they can also assign any helper.

There are also two unused helper capabilities. Firstly, helpers can be sent an SMS the day before competition asking them to confirm their availability for the nominated task, the status of the reply appears in the app. A nominated person receives all negative confirmations. Secondly, helpers can confirm in the app, (on a phone) on the day of competition, that they are in attendance ready to start the duty.

The app is also used for club record management (claims and verification), as discussed in previous posts.

The Sql Query

The database is an Azure Microsoft SQlL database. The query can be run in SQL Server Management Study (SSMS) or similar:

SELECT [HelperId], COUNT(*) as Count
FROM Activitys 
where ([RoundId]>31) AND ([RoundId]<37) AND ([HelperId] >0)
GROUP BY [HelperId]
ORDER BY 2 DESC;

The SQL Query

The query result in SSMS

The returned data above identifies helpers via their Id. A join could be included to include helpers’ name to make it more intelligible.

It was desired though to obtain this data so that the round/s can be parameterized and presented in the apps’s UI.

Direct Sql from C#

An attempt was made to directly use that query string in a C# call such as:

var query =  dbcontext.Activitys.FromSqlRaw<Activity>(sqlQuery, parameters);

But that fails it has to return an Enumerable in Activity. It needs a a query such as:

SELECT *
FROM Activitys 
where ([RoundId]>31);

Other calls were looked at such as:

  • dbcontext.Database.ExecuteSqlRaw()
  • dbcontext.Database.SqlQuery()

Could also try a Sql Stored procedure but the problem remains, returning a list.

At this point. whilst it is assumed that there is a direct Sql solution to this, it was decided to just do the query directly in Linq!
. . . . .
. . . . .

Spoiler alert: The dbcontext.Database.ExecuteSqlRaw() solution is presented in the next blog post!

. . . . .

Using Linq

Starting with all Activities:

var allActivitys = await _context.Activitys.Include(activity => activity.Helper).Include(activity => activity.Round).ToListAsync();

The Linq queries failed until the includes were added to that.

Filters are then applied to eliminate any activities that don’t have an assigned helper and any activity that hasn’t been assigned a round. (That should not occur though.) This produced filledActivities.

That is then filtered for the required range of competition rounds:

var filteredActivities = (from m in filledActivities where (m.Round.Id >= start) && (m.Round.Id <=end) select m).ToList();

The grouping and count query as follows is then applied:

    var result = filteredActivities
        .GroupBy(a => a.Helper)
        .Select(a => new ActivityHelpsCount
        {
            helper = a.Key,
            NumHelps = a.Count()
        }) 
        .OrderByDescending(x=>x.NumHelps)
        .ToList();
    return result;

This returns a list of type ActivityHelpsCount which is

    public class ActivityHelpsCount
    {
        public Helper helper { get; set; }
        public int NumHelps { get; set; }
    }

The UI

The code above runs as a Blazor service called from the UI of a Razor page. The parameters of the call are the begin and end Ids of the rounds in focus. In the UI there are two dropdown menus listing all of the rounds.One menu for the selection the start round and one for selecting the end round. not implemented yet, but should be is a constraint that the begin round is not after the end round. There is then a button to action the query call.

Rather than querying across all helpers it is possible to make the query for just one specific helper. For this there is a further dropdown menu listing all helpers which when selected, provides a helper Id (VolunteerId) for the service call. as below:

    private async Task GetHelps()
    {
        if (VolunteerId<0)
            HelpsCounts = await service.GetHelps(startRound, endRound);
        else
            HelpsCounts = await service.GetHelps(startRound, endRound, VolunteerId);
    }

The service call then conditionally implements a further filter to enforce this.

As Helpers are returned rather than just their Ids, the table that displays the returned data, uses helpers’ names.

The UI as displayed

Get number of times Helpers have Volunteered.

Get start and end rounds:

Select a Start Round

Start Round: Click and scroll

1 AVSL on Saturday 08-10-2022

Select an End Round

End Round: Click and scroll

12 AVSL on Saturday 25-03-20232

Selected:

Round: 1 AVSL at Aberfeldie on Saturday 08-10-2022

Round: 12 AVSL at Aberfeldie on Saturday 25-03-2023

Start Id: 37 End Id: 58

Get Helpers’ Volunteer Count

Can select a specific Helper:

                                
Num Times Volunteered Helper
8 Kkk Wwwwww
7 Mmm
7 Tttttt Hhhhhhh
6 Jjjjj Mmmmm
6 Sssssss Ssss
5 Ppppp Bbbbbb

😀

Discussion

The Grouping and summing can be done starting with all of the entity data in the app and using Linq queries to determine teh result. The next post does the same computation, but more directly on the database.

 TopicSubtopic
<  Prev:   Aged Care Independent Living
   
 This Category Links 
Category:Web Sites Index:Web Sites
  Next: > Entity Framework Group Summing
<  Prev:   Jekyll