How to call stored procedure in EF Core v2.1 and set result to custom model?

asp.net entity-framework-core

Question

In EF 6, I use this code that calls a stored procedure and returns result as a custom model.

var pShippingMethodId = _dataProvider.GetParameter();

pX.ParameterName = "X";
pX.Value = x;
pX.DbType = DbType.Int32;

var pStoreId = _dataProvider.GetParameter();
pY.ParameterName = "Y";
pY.Value = y;
pY.DbType = DbType.Int32;

var pLanguageId = _dataProvider.GetParameter();
pZ.ParameterName = "Z";
pZ.Value = z;
pZ.DbType = DbType.Int32;

var parameters = " @" + pX.ParameterName + ", @" + pY.ParameterName + ", @" + pZ.ParameterName;

// invoke stored procedure
var result = _dbContext.SqlQuery<AvailableDeliveryDateTimeModel>(Constants.LoadAvailableDeliveryDateTimesStoredProcedureName + parameters,pX, pY, pZ).ToList();

I want migrate this code to EF Core, but I can't find appropriate methods in EF Core

1
1
8/26/2018 7:08:10 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

You can utilize the Query Types introduced in EF Core 2.1.

First you need to register you class as query type:

modelBuilder.Query<ClaimDataView>();

Then you can use Context.Query<ClaimDataView>() in place of your current Context.Claims:

var query = Context.Query<ClaimDataView>().FromSql(...);

Update (EF Core 3.x+):

Starting with EF Core 3.0, query types have been consolidated with entity types and renamed to Keyless Entity Types, so the corresponding code is

modelBuilder.Entity<ClaimDataView>().HasNoKey().ToView(null);

and

var query = Context.Set<ClaimDataView>().FromSql(...);
22
4/2/2020 12:35:12 PM

Popular Answer

If you are not on version 2.1, you will need to add: 

public DbSet<ClaimDataView> ClaimDataView { get; set; }

to your moduleContext. And add NotMapped to your class:

[NotMapped]
public class ClaimDataView
rBalzer

Related Questions

Executing a stored procedure that returns a result set and has output parameters with Entity Framework

It turns out that the output parameters aren't available until all results are read. I fixed my problem by appending ....ToList()... to the tend of the ...ExecuteFunction()... call. I figured it out by browsing the metadata in Visual Studio. You can see the relevant documentation here: ...I don't see this mentioned in the actual documentation on...
c# entity-framework entity-framework-6 stored-procedures
asked by Tim Coker

EF Core, how to call stored procedure with input and output params?

You could write a utility method like:...private static int ExecuteSqlCount(string statement, SqlParameter[] paramsSql) { using (Entities dbContext = new Entities()) { var total = dbContext.Database.SqlQuery<int>(statement, paramsSql).First(); return total; } } } ...Assuming the stored procedure takes the two par...
c# entity-framework-core stored-procedures
asked by Peter Bill

Unable to call stored procedure with FromSql in EF Core

You need to add a DbSet, and define a primary key, as there are no columns called Id or PalyerOverviewId, EF is unable to guess which column is the key. So use either the [Key] attribute or the fluent api (or simply add a unique PlayerOverviewId to your result and model: ...[Key] public string Player { get; set; } ...https://docs.microsoft.com/en-u...
c# entity-framework-core
asked by ErikEJ

NpgSql - How to call a stored procedure (void function) from EF core - asynchronously

The easiest way to accomplish what you're trying to do is probably the following:..._context.Database.ExecuteSqlCommand("SELECT generate_accession_attributes({0})", AccessionId); ...If you don't want to use async, then call ExecuteSqlCommand (the sync version) instead of ExecuteSqlCommandAsync. You can place the logging line right after that (inste...
c# entity-framework-core npgsql postgresql
asked by Shay Rojansky

EF Core server evaluation of filter on stored procedure result

I used a table-valued function instead, it is composable since EFC knows that it is read only.
c# entity-framework entity-framework-core sql-server
asked by Raphael Laurent

EF Core stored procedure different returning result

You've defined ...Id... in ...BookViewModel... class as a key but you're passing non-unique values:...add ...authorsBooks.Id... to your SQL query and pass it to ...[Key]... in you class and define another field to display ...BookId... in both your query and class....The code would be like this:...public class BookViewModel { [Key] public in...
c# entity-framework-core
asked by behzad chhizari

How to call a stored procedure in EF Core 3.0 via FromSqlRaw

Solution (thanks to David Browne, you should have posted it as an answer):...Replacing Single with ToList works :-) ...var createdPath = ModelContext.Paths.FromSqlRaw("AddNodeWithPathProc {0}, {1}", nodeTitle, parentPathString).ToList();
.net-core-3.0 c# entity-framework entity-framework-core
asked by Dan

EF Core 3.0 Stored procedures custom view model

ok, i was able to resolve it, here is what i did....1) changed the code back to the old way and saved it. 2) cleaned solution, and build again. 3) changed the code to the 3.0 version. 4) cleaned solution, and build again....and now i dont get any errors anymore about adding migration.
.net-core asp.net-core-3.0 ef-core-3.0 entity-framework-core
asked by Sol Stein

Possible to call a stored procedure on a Table-Per-Hierarchy table in EF Core 3.1?

This is yet another issue introduced by EFC 3, tracked by ...#18232: Impossible to use stored procedures related to entities that inherits another one.......The reason is that SP calls are not composable, and EF Core always try to compose SQL for TPH base entities in order to add discriminator condition. Similar to Global Query Filters, but there y...
entity-framework-core table-per-hierarchy
asked by Ivan Stoev

Entity Framework Core stored procedure with input parameter and return custom model

I am trying to return a list of students from the stored procedure. I am using ASP.NET boilerplate which uses Entity Framework Core 2.2.x....I am not sure how to execute stored procedure here. I tried to follow ...How to use DbContext.Database.SqlQuery<TElement>(sql, params) with stored procedure? EF Code First CTP5...but I get an error:...Database...
aspnetboilerplate c# entity-framework-core stored-procedures
asked by Gopi

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow