1/3/2023 0 Comments Scaffold dbcontext![]() That’s because it relies on the DbContext method FromExpression. However, in this case, you don’t have the option of placing it in a static class. To map this TVF, you need to define the method for the mapping as you did for the scalar function. I can compose SQL queries from the TVF-only using the two columns of course-such as: select * from dbo.CustomerNameAndTotalSpent() where TotalSpent>100 Select customers.name,dbo.TotalSpentb圜ustomer as TotalSpent CREATE FUNCTION CustomerNameAndTotalSpent() I defined it to get all customers, although I could have specified that parameter for a single customer’s ID if I wanted. ![]() I’ll add a simple TVF to the database which returns both the customer name and total. The EF Core team is always eager to work with authors of PRs to bring new features and fixes into the framework!) (Fun fact: TVF support is a feature created by Paul Middlelton, a member of the developer community, via a pull-request to EF Core 5. Next up is mapping to a table-valued function (TVF), which returns a structured table rather than a single scalar value. This returns a list of a locally defined struct named CustWithTotal. For example: private static List RunScalarFunction() Now you can use the method in your queries. modelBuilder.HasDbFunction(typeof(SalesContext) The next step is to map this method in OnModelCreating as a DbFunction, using the ModelBuilder.HasDbFunction method. The method throws an exception to prevent it being used elsewhere in code. That means defining a method either directly in the DbContext class or, if it’s common across multiple DbContexts in your solution, in a static method in your application: public int TotalSpentB圜ustomer(int customerId) First, you need to let your application know that the function exists. Mapping scalar functions is not new to EF Core 5, but may be new to you. WHERE Orders.customerid = nice to have, rather than working out the LINQ query logic in any query that you’d like to include that bit of data. ![]() INNER JOIN Orders ON LineItem.OrderId = Orders.OrderId Here is the core TSQL of that function: SELECT = sum(LineItem.Quantity * Product.UnitPrice) The function takes an int (customerId) as a parameter. I’ve created a scalar function in my database called TotalSpentB圜ustomer which takes in the customer’s ID and returns a sum of all orders that customer has ever placed. If you want to test this out, you can find the sample application along with migrations for seeding data and creating the relevant functions in the database in my GitHub repository here. I’ll be working with a simple model of Customers, Orders, LineItems and Products. #Scaffold dbcontext how to#In this article, you’ll learn how to map to scalar functions and table-valued functions (TVFs) in your DbContext. There are some other ways to map entities to the database that you should be aware of and may be able to benefit from. In my “ Getting Started with EF Core 5” course, I discuss some of the most common ways to map your entities to a relational database-namely, through tables and views-as well as how to make raw SQL calls and capture results in entity types.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |