Entity Framework Core Database Table Valued Functions Mapping

user_350977 0
Viewed 3250

I use EFCore 2.1 Database First approach. I'm pretty familiar with SQL syntax and prefer build queries myself rather then leave this work on EF. I use Table Valued and Scalar Functions for querying the database.

I found this for Scalar

https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-2.0#database-scalar-function-mapping

But unfortunately nothing about Table Functions.

Is there any way to force Visual Studio grab all Table Functions and Scalar Functions and Stored Procedures from SQL Server, when I run Scaffolding?

I was using LINQ to SQL dbml designer before. Everything was extremely simple with dbml. You drag from Server Explorer drop to dbml and boom, I can use SQL Function or SP like regular C# method.

enter image description here

Any chance to reproduce this in EFCore?

ef-core-2.1 user-defined-functions stored-procedures
2 Answers

There's no reverse engineer (aka DbContext scaffolding) support for it, but you can use FromSql() to query using table-valued functions. See these docs.

var searchTerm = "EF Core";
var blogResults = db.Blogs.FromSql(
    "SELECT * FROM dbo.SearchBlogs({0})",
    searchTerm);
user_4750310

Source : https://www.allhandsontech.com/data-professional/entityframework/entity-framework-core-advanced-mapping/

Use HasDbFunction to do a mapping, refer Microsoft doc

It requires return types to be declared as Keyless entity using HasNoKeyMicrosoft doc

Configure EF Context to expose Db function

modelBuilder.HasDbFunction(typeof(SalesContext)
    .GetMethod(nameof(NameAndTotalSpentByCustomer)))
    .HasName("CustomerNameAndTotalSpent");

modelBuilder.Entity<CustWithTotalClass>().HasNoKey();

Invoke Db function in calling code

_context.NameAndTotalSpentByCustomer().Where(c=>c.TotalSpent>100).ToList();

Generated SQL

SELECT [c].[Name], [c].[TotalSpent]
FROM [dbo].[CustomerNameAndTotalSpent]() AS [c]
WHERE [c].[TotalSpent] > 100
user_7889270
Related