Sometimes we need to write new Functions, Views and Procedures or use existing ones. In the past, EFCore hasn’t had a super nice way to handle non-table database structures. Therefore, in order to work with non-table database objects in EFCore5 code first implemented some new functionality around some of these items, including the ability to do a ToFunction or ToView call in the Fluent API to map queries to table-valued functions, procedures and views, which we can leverage in EFCore6.
In this post, we will see how to add non-table database objects to db context and put the SQL scripts into migration script in a nicer and cleaner way.
At first we will create one Procedure (spGetWeatherInformation), one View (vwLocationTemperatureSummery) and one Function (fnGetTemperatureByLocation) for example. Then we will keep them in three seperate directory in Sql Script directory of our WeatherForecast.Infrastructure project.
CREATE OR ALTER PROCEDURE spGetWeatherForecast
(
@location NVARCHAR(50)
)
AS
BEGIN
SELECT
Date,
Location,
TemperatureC Temperature,
Summary
FROM WeatherForecasts
WHERE LOWER(Location) = LOWER(@location)
END
CREATE OR ALTER FUNCTION fnGetTemperatureByLocation
(
@location NVARCHAR(50)
)
RETURNS INT
AS
BEGIN
DECLARE @Temperature INT
SELECT TOP(1)
@Temperature = TemperatureC
FROM WeatherForecasts
WHERE LOWER(Location) = LOWER(@location)
ORDER BY Id DESC
RETURN @Temperature
END
Build Action of those file should be Embedded resource- Right click on the file then select Property—
Now opening the Package Manager Console, will run following migration command—
add-migration v1_3 -c ApplicationDbContext -o Migrations
we could have add the SQL Scripts like bellow in our migration to generate migration script
namespace WeatherForecast.Infrastucture.Migrations
{
public partial class v1_3 : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql(@"'CREATE OR ALTER VIEW vwLocationTemperatureSummery
AS
SELECT
Date,
Location,
TemperatureC Temperature,
Summary
FROM WeatherForecasts'");
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql(string.Format(@"DROP VIEW IF EXISTS {0}", "vwLocationTemperatureSummery"));
}
}
}
But we will follow another approach so that we can create migration scripts dynamically from previously created scripts as well as those scripts can be tracked by the source control easily.
namespace WeatherForecast.Infrastucture.Migrations
{
public partial class v1_3 : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.RunSqlScript(View.LocationTemperatureSummery);
migrationBuilder.RunSqlScript(Procedure.GetWeatherInformation);
migrationBuilder.RunSqlScript(Function.GetTemperatureByLocation);
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql(string.Format(@"DROP VIEW IF EXISTS {0}", View.LocationTemperatureSummery));
migrationBuilder.Sql(string.Format(@"DROP PROCEDURE IF EXISTS {0}", Procedure.GetWeatherInformation));
migrationBuilder.Sql(string.Format(@"DROP FUNCTION IF EXISTS dbo.{0}", Function.GetTemperatureByLocation));
}
}
}
Here RunSqlScript is an Extention funtion that takes the script name as parameter to generate the script—
namespace WeatherForecast.Infrastucture.Extensions
{
public static class MigrationExtension
{
public static void RunSqlScript(this MigrationBuilder migrationBuilder, string script)
{
var assembly = Assembly.GetExecutingAssembly();
var resourceName = assembly.GetManifestResourceNames().FirstOrDefault(x => x.EndsWith($"{script}.sql"));
using var stream = assembly.GetManifestResourceStream(resourceName);
using var reader = new StreamReader(stream);
var sqlResult = reader.ReadToEnd();
migrationBuilder.Sql(sqlResult);
}
}
}
Besides this, we will use constants to keep the name of View, Procedure and Function because this name needs to be used in many places.
public class View
{
/// <summary>
/// vwLocationTemperatureSummery
/// </summary>
public const string LocationTemperatureSummery = "vwLocationTemperatureSummery";
}
public class Function
{
/// <summary>
/// fnGetTemperatureByLocation(@location nvarchar) returns int
/// </summary>
public const string GetTemperatureByLocation = "fnGetTemperatureByLocation";
}
public class Procedure
{
/// <summary>
/// spGetWeatherInformation(@location nvarchar(50))
/// </summary>
public const string GetWeatherInformation = "spGetWeatherInformation";
}
Now to generate the migration script, run the following command—
Script-Migration location-added v1_3 -c ApplicationDbContext -o Services/WeatherForecast/WeatherForecast.Infrastucture/Migrations/Scripts/v1.3.sql -i
-o = output location
-c = db-context
From location-added to v1_3
-i = Generate a script that can be used on a database at any migration
BEGIN TRANSACTION;
GO
IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20221031051328_v1_3')
BEGIN
CREATE OR ALTER VIEW vwLocationTemperatureSummery
AS
SELECT
Date,
Location,
TemperatureC Temperature,
Summary
FROM WeatherForecasts
END;
GO
IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20221031051328_v1_3')
BEGIN
CREATE OR ALTER PROCEDURE spGetWeatherForecast(@location nvarchar(50))
AS
SELECT
Date,
Location,
TemperatureC Temperature,
Summary
FROM WeatherForecasts
WHERE LOWER(Location) = LOWER(@location)
END;
GO
IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20221031051328_v1_3')
BEGIN
CREATE OR ALTER FUNCTION fnGetTemperatureByLocation
(
@location NVARCHAR(50)
)
RETURNS int
AS
BEGIN
DECLARE @Temperature int
SELECT TOP(1)
@Temperature = TemperatureC
FROM WeatherForecasts
WHERE LOWER(Location) = LOWER(@location)
ORDER BY Id DESC
RETURN @Temperature
END
END;
GO
IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20221031051328_v1_3')
BEGIN
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20221031051328_v1_3', N'6.0.4');
END;
GO
COMMIT;
GO
But this script show some syntax error—
So that we will add some modification maually and script sould be like folllowing—
BEGIN TRANSACTION;
GO
IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20221031051328_v1_3')
BEGIN
EXEC(N' CREATE OR ALTER VIEW vwLocationTemperatureSummery
AS
SELECT
Date,
Location,
TemperatureC Temperature,
Summary
FROM WeatherForecasts')
END;
GO
IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20221031051328_v1_3')
BEGIN
EXEC(N' CREATE OR ALTER PROCEDURE spGetWeatherForecast(@location nvarchar(50))
AS
SELECT
Date,
Location,
TemperatureC Temperature,
Summary
FROM WeatherForecasts
WHERE LOWER(Location) = LOWER(@location)')
END;
GO
IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20221031051328_v1_3')
BEGIN
EXEC(N' CREATE OR ALTER FUNCTION fnGetTemperatureByLocation
(
@location NVARCHAR(50)
)
RETURNS int
AS
BEGIN
DECLARE @Temperature int
SELECT TOP(1)
@Temperature = TemperatureC
FROM WeatherForecasts
WHERE LOWER(Location) = LOWER(@location)
ORDER BY Id DESC
RETURN @Temperature
END')
END;
GO
IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20221031051328_v1_3')
BEGIN
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20221031051328_v1_3', N'6.0.4');
END;
GO
COMMIT;
GO
Run the script and see the output—
Migration is done, Now we will create Dtos against our View, Procedure and Functions as well as map them in DbContext.
public class ApplicationDbContext: DbContext, IApplicationDbContext
{
public ApplicationDbContext()
{
}
#region TABLES
public DbSet<WeatherForecastEntity> WeatherForecasts => Set<WeatherForecastEntity>();
#endregion
#region VIEWS
/// <summary>
/// vwLocationTemperatureSummery
/// </summary>
public DbSet<LocationTemperatureSummeryDto> LocationTemperatureSummery => Set<LocationTemperatureSummeryDto>();
#endregion
#region PROCEDURES
/// <summary>
/// spGetWeatherInformation(@location nvarchar(50)
/// </summary>
public DbSet<WeatherInformationDto> GetWeatherInformation => Set<WeatherInformationDto>();
#endregion
#region FUNCTIONS
/// <summary>
/// fnGetTemperatureByLocation(@location nvarchar(50)
/// </summary>
public DbSet<TemperatureByLocationDto> GetTemperatureByLocation => Set<TemperatureByLocationDto>();
#endregion
protected override void OnModelCreating(ModelBuilder builder)
{
builder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());
base.OnModelCreating(builder);
#region VIEWS
builder.Entity<LocationTemperatureSummeryDto>(x =>
{
x.HasNoKey();
x.ToView(View.LocationTemperatureSummery);
x.Metadata.SetIsTableExcludedFromMigrations(true);
});
#endregion
#region FUNCTIONS
builder.Entity<TemperatureByLocationDto>(x =>
{
x.HasNoKey();
x.ToFunction(Function.GetTemperatureByLocation);
x.Metadata.SetIsTableExcludedFromMigrations(true);
});
#endregion
#region PROCEDURES
builder.Entity<WeatherInformationDto>(x =>
{
x.HasNoKey();
x.ToView(Procedure.GetWeatherInformation);
x.Metadata.SetIsTableExcludedFromMigrations(true);
});
#endregion
}
}
all are done, now test with running following code—
var locationTemp = await _context
.LocationTemperatureSummery
.Where(x => x.Location == request.Location)
.ToListAsync();
var location = new SqlParameter("location", System.Data.SqlDbType.NVarChar);
location.Value = request.Location;
// procedure
var weatherInfo = await _context
.GetWeatherInformation
.FromSqlRaw($"EXEC dbo.{Procedure.GetWeatherInformation} @location", location)
.ToListAsync();
// function
var temp = (await _context
.GetTemperatureByLocation
.FromSqlRaw($"SELECT dbo.{Function.GetTemperatureByLocation}(@location) Temperature", location)
.FirstOrDefaultAsync())!.Temperature;
Hope this will help to track non-table database objects by source controll and make easy to use of non-table database like View, Procedure and Function.
Thanks with Source.
