Entity Framework Group Summing: 4 - Stored Procedures Part B
web code jekyll html linq grouping entity-framework sqlsvr stored-procedures
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 thenupdate-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
andend
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
Topic | Subtopic | |
Next: > | Nuget updates | With errors such as NU1605 and NU1301 |
This Category Links | ||
Category: | Web Sites Index: | Web Sites |
Next: > | Club Record Certificate | Part 1 ... Athlete Selection |
< Prev: | Entity Framework Group Summing | 3 - Stored Procedures Part A |