Entity Framework Group Summing: 4 - Stored Procedures Part B


How to: Calling a Parameterized Microsoft Sql Server Database Stored Procedure from a C# Entity Framework app where the result is a collection.

In first 2 posts in this series, a count is made of the number of times a helper has volunteered to do an activity over a season of athletics meets. In the first post, Linq was used. In the second post a Sql View is used to do the required processing on the database with ExecuteRawSql is used to get the result into the app. Both achieve the same result. The third post demonstrated using a Stored Procedure to return scalars and strings. This post covers using a parameterized Stored Procedure to sum the number of times a helper has volunteered.

As previous: _The app is a Blazor Service app using Entity Framework for access to the Azure Sql data. The Code-First approach is used where entities are defined as classes and added to the database context. The code behind is then updated using the add=migration command and then update-database command to update the database table/s.

The Code

  • A class HelperActivityCountr is defined thus:
    public class HelperActivityCountr
    {
        public string Name { get; set; }
        public int Count { get; set; }
    }
  • Also HelperActivityCountrs is added to the DBContext:
public DbSet<HelperActivityCountr> HelperActivityCountrs { get; set; }
  • This entity has to be created such that it has no key as there is no such table in the database:
#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");
                });
        modelBuilder
            .Entity<HelperActivityCountr>(
                eb =>
                {
                    eb.HasNoKey();
                });
    }
#endregion

HelperActivityCount is from the View code as previously discussed.

  • The method being called from the razor page, which is on the blazor service, returns a collection of the HelperActivityCountr type:
IEnumerable<HelperActivityCountr> GetHelpsSP2(int start, int end)
  • start and end are the Ids of the meet rounds database table which have a date associated in them.
var result = _context.HelperActivityCountrs.FromSql<HelperActivityCountr>($"exec GetStudentDetail  @Start={start},@End={end}").AsNoTracking().ToList();

Reference: RelationalQueryableExtensions.FromSql Method MS Docs

Nb: Found the comma between the parameters in the enumerated string was required: @Start={start},@End={end} Was getting runtime errors with the call without it.

  • The method then returns the collection:
return (IEnumerable <HelperActivityCountr> )result;

The method is called thus:

var counts = await service.GetHelpsSP2( start,  end).ToList();

The code for the stored procedure is

[dbo].[GetHelperCounts]
(
    @Start int,
    @End int
)
AS
BEGIN
    SET NOCOUNT ON

    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
    RETURN @End
END
  • The calling razor page then displays the counts v name in a table:
Count Helper
10 Joe Mag
6 Sav Sing
5 Wal Kimbly
4 Hel Samp
3 Phil Bant

So Joe Mag is the “best” helper here.

Summary

The previous post covered calling a Stored Procedure from Blazor where the result returns a scalar or string. This post demonstrates a call where the result is a collection generated from a query where the collection entity is not a table in the database. start and end parameters are passed to the stored procedure which represent the range of meets to be summed over.

Ps: Found this link here that gives a variety of database stored procedure calls via Entity Framework. FYI

 TopicSubtopic
  Next: > Nuget updates
   
 This Category Links 
Category:Web Sites Index:Web Sites
  Next: > Club Record Certificate
<  Prev:   Entity Framework Group Summing