[rank_math_breadcrumb]

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

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.

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—

we could have add the SQL Scripts like bellow in our migration to generate migration script

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.

Here RunSqlScript is an Extention funtion that takes the script name as parameter to generate the script—

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.

Now to generate the migration script, run the following command—

-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

But this script show some syntax error—

So that we will add some modification maually and script sould be like folllowing—

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.

all are done, now test with running following code—

Pattern Matching Expression

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.