A database may contain various constructs – tables, views, stored procedures, functions, etc.

We already have seen how tables can be mapped to C# EF core models, by defining a DbSet property in DbContext. We also have seen ToView method can be used to map a class to a view in database. We can also use raw SQL to invoke the stored procedures.

We can also use raw SQL queries to invoke the functions from the database. But in this article, we will have a look at how a CLR method (C# method) can be used to invoke a SQL function.

What are we planning to do ?

We will create a simple DbContext – UniversityContext – which has only one table – Students. Also, we are going to use SQL Server as underlying data store.

Next, we will create a simple scalar valued function – GetStudentsWithSameLastName – which takes a parameter @lastName. This function would return the number of students which have same last name as provided in the input parameter.

Then we will create a method in C# and will map it to the scalar valued function created before. Then we will use the C# method in a LINQ query to get the count.

So, let’s get started !

EF Core Class Library – Context and Models

Let’s create a simple .NET core class library – EFCoreFunctionsDemo.Data.EF. Add references to two NuGet packages in that library:

• Microsoft.EntityFrameworkCore.Design – for enabling dotnet CLI EF Core tools on the class library.
• Microsoft.EntityFrameworkCore.SqlServer – as we are going to use SQL Server database

Then add a simple EF Core model – Student – for holding very basic information related to university students.

Next, add a new class a UniversityContext class as shown below.

We can also create a class to enable design time support for migrations by adding a class as shown in below snippet.

Create Database

Then the DOTNET CLI commands given below can be executed to create the database in SQL Server.

Create a Scalar Valued Function

For the purpose of demonstration, we are going to create a scalar valued function as shown below. It has one parameter of type nvarchar and it returns an integer value representing number of records.

Map to C# Method

Now, let’s create a C# method. The name of C# method does not need to be same as of SQL function. But the arguments of the method should be same as of the function. The body of function does not matter. Hence, in the below code we are just throwing a NotImplementedException from the method.

By default, EF Core would try to find the same Function as of method’s name. But we can use OnModelCreating method to map C# method name to SQL Function name if the name differ.

Also, if EF Core is not able to map parameters, then it would try to execute the method on client side.

Although we have defined the method in the DbContext class, it can be defined in any other class as static method.

Below is the complete code of modified UniversityContext after adding the C# method and mapping :

Create Console App

Next, let’s create a console app (EFCoreFunctionsDemo) and add reference to EFCoreFunctionsDemo.Data.EF class library.

Below is how the console app looks like. It creates two instances of context. First instance is used just to add few students to the database. Second instance is used to query using the new CLR method we created.

The query is to retrieve only those students whose surnames appear more than once.

Now, if we run the console application we should see only two records returned by the query.

Are you interested in seeing how the LINQ query from the above code gets translated to SQL query ? You can enable Simple Logging to view it.

I hope you find this information helpful. Let me know your thoughts.