Entity Framework Group Summing: 2 - ExecuteRawSql


The task here is to to count the number of times a helper has volunteered to do an activity over a season of athletics meets. In the previous post on this topic, in an Entity Framework context from a Blazor app, consideration was given to using raw Sql queries directly on the database to select activitys over a range of meeting dates grouped on helpers and counted thus for each helper. The previous solution ended up processing the query in Linq after returning all entity values from the Activitys table joined to Helpers table. The database direct solution here uses databasecontext.database..ExcuteRawSql().

The Problem

(As previous) With the Aths Helpers app, it’s end of season and it’s time to determine who has volunteered the most via the app, so we can thank and award them It’s easy enough to create a Sql query in SQL Server Management Studio (SSMS) to count the number of entries for each helper of the season. The problem encountered was that when called from the app, the returned data was not a database context entity. The solution is to add it via an explicit coded model that refers to a database view that implements the required grouping and count query. The database is an Azure Microsoft Sql database.

Reference

Apart from the lack of an entity in the database context to return the data to, an issue is that the data is keyless; values do not have an Id. Microsoft Learn documents discusses that here: Keyless Entity Types which was worked through for this alternative solution.

Creating the View

The View can can be created in SSMS which provides a graphical UI for doing so and can be saved to the database. In SSMS right-click on Views under the database and select New View. You can test ir there but only becomes part of the database when saved.

Note that the saved View doesn’t appear in SSMS explicitly like a table. To view a saved View:

  • Expand System Views under Views
  • Right-click on INFORMATION_SCHEMA_VIEWS then select Top 100 Rows
    (The 100 might be another number if you have previously set the number of table rows, 1000 in my case.)
  • Find its via the Table Name column
  • Right-click on the View Definition columns, copy and paste into a Text Editor to view the View query

Nb: Found that you can’t right-click and delete a View if in Edit mode. But can do a coded delete. A DROP View query in SSMS would probably work.

Creating the View in Code

Within a Blazor Service call the following creates the required View:

 dbContext.Database.ExecuteSqlRaw(
@$"CREATE VIEW dbo.View_ActivityCounts
AS
SELECT TOP (100) PERCENT COUNT(dbo.Activitys.HelperId) AS Count, dbo.Helpers.Name
FROM   dbo.Activitys INNER JOIN
        dbo.Helpers ON dbo.Activitys.HelperId = dbo.Helpers.Id
WHERE  (dbo.Activitys.RoundId >= {start}) And (dbo.Activitys.RoundId <= {end})
GROUP BY dbo.Helpers.Name
ORDER BY Count DESC;"
);

Nb: Can’t run this if the view View_ActivityCounts already exists.

dbCntext.Database.ExecuteSqlRaw(
@"DROP VIEW IF EXISTS dbo.View_ActivityCounts");

… will delete the view if it exists.

Nb: A View can’t be parameterized, so that each time the view data is required it is deleted and recreated in this code. The CreateView string is interpolated in that a new start and end are used each time.

The Model

The View is returning the Count and Name for each Helper. This is reflected in the HelperActivityCount class:

    public class HelperActivityCount
    {
        public string Name { get; set; }
        public int Count { get; set; }
    }

PS: I forgot to make the properties public so went around in circles for a while!

The MS Learn reference then says to create the OnModelCreating() method but:

  • It doesn’t say where to place it.
  • In the example presented it does not make a base call, which was found to be required.

I placed the method in the application database context:

    public class ApplicationDbContext : IdentityDbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }

        #region Required
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder
                  .Entity<HelperActivityCount>(
                      eb =>
                      {
                          eb.HasNoKey();
                          eb.ToView("View_ActivityCounts2");
                          eb.Property("Name").HasColumnName("Name");
                          eb.Property("Count").HasColumnName("Count");
                      });

        }
        #endregion

        //dbcontext entities:
        // ...

Note the one to one mapping of the query properties to the target class properties.

The Raw Sql Call

In the Service call, just after the View creation as above, the view can then be queried:

            List<HelperActivityCount> helperCounts = _context.HelperActivityCounts.OrderByDescending(h => h.Count).ToList();

And this all worked! 😀

Note that although the View query orders the data descending, the returned data still needed ordering.

Discussion

Two ways have been demonstrated for grouping and summing entity data. The first method in the previous post assembled all of the data then used Linq queries to group one entity based upon a second entity then sum number in each group for each second entity. The second method used in this post is a bit more esoteric in that it used a Sql View to do the processing. Both methods work OK so which is best. The Linq approach is probably conceptually easier but probably is slower with large amounts of data, than the second. The dataset here is small so there is no discernible performance difference between the two.

 TopicSubtopic
   
 This Category Links 
Category:Web Sites Index:Web Sites
  Next: > Entity Framework Group Summing
<  Prev:   Entity Framework Group Summing