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 VIEW vwLocationTemperatureSummery
AS
SELECT 
     Date,
     Location,
     TemperatureC Temperature,
     Summary
FROM WeatherForecasts
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

 

script directory 1 Efficient way to handle Procedures, Views, Functions in EF Core
Build Action of those file should be Embedded resource- Right click on the file then select Property—
2 build action Efficient way to handle Procedures, Views, Functions in EF Core

Now opening the Package Manager Console, will run following migration command—

add-migration v1_3 -c ApplicationDbContext -o Migrations

3. run migration Efficient way to handle Procedures, Views, Functions in EF Core

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—
4. error 1 1 Efficient way to handle Procedures, Views, Functions in EF CoreSo 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—
5.script runngr 1 1 Efficient way to handle Procedures, Views, Functions in EF Core
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;

final Efficient way to handle Procedures, Views, Functions in EF Core

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.