Efcore: Stored procedure mapping support

[rowanmiller]

Note: Some of the features tracked in this issue could help with using EF Core with database views. However, the feature is not limited to database views and its completion would not mean that every aspect of database view support has been implemented. See #827 for an overview of the areas where EF Core interacts with database views.

---

Feature description

(added by @divega on Sep 4, 2014)

This is a general meta-issue for enabling better support for working with stored procedures in EF Core, with an emphasis on mapping stored procedures to parts of the EF Core model as opposed to using them in an ad hoc manner, which is covered in other issues. There are different aspects of stored procedures support than should be consider separately, e.g.:

• [x] Invoking stored procedures in an ad hoc manner to return entity types (already possible with FromSql)
• [x] Invoking stored procedures in an ad hoc manner to return arbitrary types (covered by #1862)
• [ ] Mapping of CUD operations to stored procedures (#15671)
• [ ] Mapping navigation properties to stored procedures
• [ ] General data retrieval using stored procedures
• [ ] Scaffold/reverse engineer stored procedures (see #15105)
• [ ] Wrap Migrations calls to create stored procedures on SQL Server with EXEC (see #19740)

Note that for data retrieval stored procedures aren't generally considered composable on the store i.e. it wouldn't normally be possible to reference them in LINQ queries without causing all the further processing to switch to the client (#621 and #622 cover composable functions) but with our compensating LINQ provider it could be possible some day to enable mapping specific LINQ patterns to stored procedures calls, e.g.:

C# var customerOrders = db.Orders.Where(o => o.Customer == customerId);

Could cause a stored procedure OrdersByCustomerId(int @customerId) to be invoked.

[IlyaTsilikov]

IMHO one of the most useful features of EF 6 is wrapping stored procedure into DbContext-based class methods. So it would be better to include such feature in EF 7 and if it's possible make two improvemtnts:
1). make available EF to generate methods with out-parameters for OUTPUT SQL keyword (and also ref);
2). make EF available to generate async/await verson of such methods.

[BaranyiAndor]

Also a good idea would be to add User Defined Table Type support for Stored Procedure parameters when generating the DbContext-based class methods using the edmx Database first code generator.

[mikary]

Note: Support for stored procedures with client composition on DbSet<T> was added via #1864

[Aristarh71]

Where I can find any example of using stored procedures with client composition on DbSet in entity framework 7 context?

[mikary]

There are a few examples in the functional tests FromSqlSprocQueryTestBase.cs.

The general pattern is to call the FromSql extension with the stored procedure command text and compose on the result:
myContext.Products.FromSql("[dbo].[Ten Most Expensive Products]").Where(...

or command text format string and parameters:
myContext.CustomerOrders.FromSql("[dbo].[CustOrderHist] @CustomerID = {0}", "ALFKI").Where(...

This feature depends on the generic type of the DbSet being a type that can be populated from the columns returned by the stored procedure. There are still some limitations/issues when the composed methods require server side execution (i.e. Include or additional FromSql calls #2420), and there is no support for saving changes to the results at this time.

[Aristarh71]

Code with FromSql is outside of the context class (myContext), am I right?
Are there any plans to support stored procedure mapping in RC?

[rowanmiller]

@Aristarh71 FromSql uses your context, and you can always hide it away in a method on your context:

``` c#
public class BloggingContext : DbContext
{
public DbSet Blogs { get; set; }

public IEnumerable<Blog> FindBlogs(string searchTerm)
{
    return this.Blogs.FromSql("SELECT * FROM dbo.SearchBlogs(@p0)", searchTerm);
}

}
```

[poke]

Are there any plans yet to provide a way to consume stored procedures without having to set up a real entity type in the model builder? So that we only get the mapping capabilities, but leave out redundant stuff like the whole change tracker? I already add a AsNoTracking() after everything, but the structures are still there even if it doesn’t make sense for the types.

So ideally, I would just like to be able to define “mapping entities” (I have no idea how these are actually called), that don’t do anything except provide the definitions when EF needs to map a result onto that type. This would then also mean that normal entity restrictions would not be necessary (e.g. having no primary key—which is perfectly reasonable for non-tracked items from a stored procedure).

[rowanmiller]

@poke I think that is an interesting idea... it's probably also something you want when you map views (or at lease non-updateable views). We'll keep that in mind when we come to these features.

[Vasim-DigitalNexus]

RE: mapping capabilities/non-updateable views

This would be a great feature, just map to a type, key or no key

In our case we want to hide the keys sometimes for a read-only view of the result

[dealdiane]

+1 for OUTPUT support.

As for a real world example: When doing paging using stored procedures, we need to run 2 queries. One to get the paged entities and another to get the total row count of unpaged entities to build a pager. By passing an OUTPUT parameter, we could reduce this to a single query. We can use both OFFSET and COUNT() OVER in a single query and return both (entities and total # of rows thru the OUTPUT parameter).

This is just one example. I'm sure many will benefit this if implemented.

[divega]

@dealdiane I would recommend filing a separate bug if your main interest is to have output parameters supported in FromSql() queries. Otherwise I am afraid your request is going to be lost in a comment in this bug that is already in the backlog. It would also be great if you could include the actual SQL (I assume it would be a stored procedure) to illustrate your particular scenario better.

[TimPosey2]

+1 for adding Stored Proc Async/Await Support
-1 for out/ref parameters because it would conflict with adding async support.

[eadbhard]

I reverse-enginered an existing SQL Server database and added the model classes and context class to my MVC 6 project. This is my controller constructor, I assume DI will take care of injecting the context (which is registered as a service in StartUp.cs)

public ReportsController(akzent_reportsContext context)
{
this._context = context;
}

Now on a controller method I'm trying this:

IEnumerable results = _context.FromSql("SELECT * FROM dbo.MyFunctionName(@p0)", "something");

But I get this compilation error:

'akzent_reportsContext' does not contain a definition for 'FromSql' and no extension method 'FromSql' accepting a first argument of type 'akzent_reportsContext' could be found (are you missing a using directive or an assembly reference?) Reportes.DNX 4.5.1, Reportes.DNX Core 5.0

I've already added the EntityFramework.Relational 7.0.0-rc1-final package to the project, and in the controller I am

using Microsoft.Data.Entity;

What am I missing?

Thanks for any insight.

[poke]

FromSql does not exist on the database context but on a context’s DbSet. For example as shown in the code above: context.Blogs.FromSql(…).

The reason for this is that EF does need to know the resulting entity type it should materialize from the SQL statement. This also means that you—currently—need to declare entities for your stored procedure result.

[stevensrf1]

a

[stevensrf1]

Does anyone know where I can find source code project to download the uses the FromSQl to call a stored procedure that actually compiles and works,

[poke]

As explained above, you can just call FromSql on the DbSet to materialize a result of registered entities. If you are having problems with that, feel free to open a question on Stack Overflow to ask for help with your specific problem (but remember to include a MCVE).

Otherwise, this issue is used to track the progress for stored procedure progress, so it’s not really the right place to ask for individual usage assistance.

[stevensrf1]

I am using EF Core in a windows class library and not a web class library.
I do not see the FromSQL extension on the entity class referenced by the derived dbcontext
object.
I guess I have to see what other assembly I need to install..

[stevensrf1]

I am looking to call a StroredProcedure not an sql statement. Is there a working code example available for download that show FromSql calling a stored procedure?

[poke]

See the functional stored procedure tests on how to consume stored procedures. As I already said, you need to run it _on a DbSet_, not on the context or some entity type. And there is no way to call stored procedures other than to execute SQL right now.

[stevensrf1]

I did not say run it from the DBContext. At least I did think I said that.
I said reference to the entity referenced by the DBContext such as
dbContext.entity.FromSQL

The reason I could not find the FromSQL ion the entity was because an extension in an assembly was not loaded.
I used Net.Reflector to search through the various entity framework DLLs used in a Web Application and found the assembly that needed to be load..

[gdoron]

@rowanmiller I read several issues here, but I'm still not sure what's the current status of SP support and how things should be done.

Is there a place that summarize what's supported and what's not?
And how to do do each action.

[rowanmiller]

@gdoron the only thing that is supported at the moment is using DbSet.FromSql to run raw SQL to return results from a stored procedure. Proper stored procedure support will not be in the 1.0.0 release. See the roadmap for more info.

[gdoron]

@rowanmiller And what about the most common scenario (at least from my experience), that the SP doesn't return an exact table row but adds some of other tables?

E.g.

Customers:
Id,
Name,

Orders:
OrderId,
CustomerId,
Price

And there's a SP that returns something like this:

select Sum(O.Price),  C.Name
from Customers C 
inner join Orders O
    ON O.CustomerId = C.CustomerId
where C.CustomerId = @customerId

Do I need to create a fake Entity and ignore it in the Migrations like with VIEWs?
Fallback to DataReader (Yuck!)

Thanks

[rowanmiller]

@gdoron yep there is no nice way to handle that right now. What is available is really just a workaround for the lack of proper stored procedure support in EF Core.

We totally get that this is a massive limitation for an O/RM, just reiterating what the roadmap says about the list of missing features that stored procedure support is part of:

The things we think we need before we say EF Core is the recommended version of EF. Until we implement these features EF Core will be a valid option for many applications, especially on platforms such as UWP and .NET Core where EF6.x does not work, but for many applications the lack of these features will make EF6.x a better option.

[maclax]

I'm trying to call stored procedure in my dbcontext class using the entity type. Please find the code below:
public IEnumerable ExecuteStoreCheckQuery()
{
DateTime startdate = new DateTime(2017, 03, 15);
string sdate = String.Format("{0:MM/dd/yyyy}", startdate);

        DateTime enddate = new DateTime(2017, 03, 01);
        string edate = String.Format("{0:MM/dd/yyyy}", enddate);


        var storecheckData= StoreCheckByDate.FromSql("exec dbo.StoreCheckByDate @StartDate={0},@EndDate={1}", sdate, edate);
        return storecheckData;

    }

On executing, i'm getting the following error:

[maclax]

Using async/await i modified the stored procedure call.
Controller class
```C#
[HttpGet]
public async Task ReportSalesbyBrand()
{

        return View(await _totalSalesRepository.GetStoreCheckData(););

    }

Repository class
```C#
  public async Task<IEnumerable<StoreCheckByDate>> GetStoreCheckData()
        {

             return await _totalSalesContext.ExecuteStoreCheckQuery();
        }

Dbcontext
```C#
public async Task> ExecuteStoreCheckQuery()
{
DateTime startdate = new DateTime(2017, 03, 15);
string sdate = String.Format("{0:MM/dd/yyyy}", startdate);

        DateTime enddate = new DateTime(2017, 03, 01);
        string edate = String.Format("{0:MM/dd/yyyy}", enddate);


       return  await StoreCheckByDate.FromSql("exec dbo.StoreCheckByDate @StartDate={0},@EndDate={1}", sdate, edate).ToListAsync();

    }

Model Class
```C#
public class StoreCheckByDate
    {
        [Key]
        public DateTime Date { get; set; }

        public string UserCode { get; set; }

        public string SalesmanName { get; set; }

        public string Role { get; set; }

        public string ClientCode { get; set; }

        public int TotalApplicableCount { get; set; }
    }

Stored Procedure

CREATE proc [dbo].[StoreCheckByDate]--SalesByBrand '02-01-2017','02-28-2017'
@StartDate DATETIME,
@EndDate DATETIME
AS
select date,usercode,salesmanname,role,clientcode,totalapplicablecount from tblStoreCheck
where date<= @StartDate and date> @EndDate
order by date

I'm getting following error:

An unhandled exception occurred while processing the request.

NullReferenceException: Object reference not set to an instance of an object.
lambda_method

Stack Query Cookies Headers
NullReferenceException: Object reference not set to an instance of an object.
lambda_method
Shape
<_ShapedQuery>b__0
MoveNext
ThrowForNonSuccess
HandleNonSuccessAndDebuggerNotification
MoveNext
ThrowForNonSuccess
HandleNonSuccessAndDebuggerNotification
MoveNext

Any help would be highly appreciated!!

[ajcvickers]

@maclax Can you open a new issue for this? Please make sure to include the full stack trace for the exception you are seeing.

[rajeshj18]

Hi, am new to EF Core (1.1). currently my app is in EF and is using lot of stored procedures. these stored procedures return a complex resultset meaning they don't directly map to a table entity in DB.
Can you point me to some examples where the SP returns a custom resultset which is not same as DB entity.

i have few other questions related to this.

1. Can i use DBSet statement is like this - public DbSet getData{ get; set; } where T is my complex model which i have created. the structure of T will be same as the output of SP.
2. online i see some examples like await
   context.Database.SqlQuery("CountOfOrders @ProductID", param1).SingleAsync();
   but when i type, i don't get SqlQuery. i tried to include system.Data.Entity as per intellisense, but it is not getting added. how do i proceed.

Thanks in advance for the help.

[rmarskell]

@rajeshj18 As far as I know this is not yet supported. See this comment.

[rajeshj18]

@rmarskell thanks for the reply. just to confirm, "_EF core 1.1 does not support calling sps which return resultsets which are different from db entities_". is that correct statement?

in above discussion is @maclax not trying to do the same thing?

can you shed some light on #1 and #2 questions above?

Thanks.

[poke]

@rajeshj18 @rmarskell Please read this issue. As I repeatedly explained above, consuming stored procedures is possible with EF Core but requires you to set up the entity type (not a DbSet). This is the case because EF can only materialize entities that are defined in the database context (using the model builder).

I’ve written an answer on Stack Overflow that shows how this exactly works. If that does not help, please open a question on Stack Overflow with your concrete issue. Feel free to ping me about it, and I’ll gladly help out since I’ve gathered a lot of experience with consuming stored procedures using EF Core. So yes, it is absolutely possible with a current version, just a bit annoying.

But please, stop polluting this issue with individual issues that are not about the actual progress on getting proper stored procedure support into EF Core.

[ErikEJ]

@poke How little code is required in ModelCreating method, it looks a bit unclear from your SO reply

[poke]

@ErikEJ You would set it up like any other entity that exists as a table in the database—except that it has no table, so you don’t need to care about things like table name or index configurations. You can set up the entity either using the FluentAPI inside the model builder, or use the data annotations. This is explained in the modeling section of the documentation.

It’s really just the same for an entity you would want to use for a stored procedure. In fact, if your stored procedure returns a result that can be mapped to an actually existing entity that also exists as a table, then you can just use that entity type and you don’t have to do anything special for the stored procedure.

The FromSql call does not care whether the entity it uses is actually useful and exists as a table or not. It does not even care if it’s a good fit for the result. It just requires that that entity is set up in the database context (so the context knows how to materialize it), and then it will use it to map the result to it.

So, to answer your question, the simplest model builder call might look like this:

```c#
modelBuilder.Entity();

This will use either the data annotations or the normal conventions to set up the entity according to the properties that exist in the `StoredProcRow` type.

But you can also go the explicit route without data annotations and declare everything inside the builder:

```c#
modelBuilder.Entity<StoredProcRow>(entity => {
    // entities are required to have a key, even if they don’t matter for stored procedures
    entity.HasKey(item => item.Id);

    // but it’s a good idea to disable auto generating ids
    entity.Property(item => item.Id).ValueGeneratedNever();

    // these will use the normal conventions for the properties
    entity.Property(item => item.Name);
    entity.Property(item => item.Description);

    // you can also map to different column names
    entity.Property(item => item.DisplayName).HasColumnName("Title");

    // you might even want to ignore some properties
    entity.Property(item => item.Type).Ignore();
});

So this is all just the same as you would expect for a normal entity. If you would expose a DbSet<StoredProcRow> from your context, you could now use this to access an actual table for this, but usually, there isn’t one for stored procedure results. So you would just use a Set<StoredProcRow>() internally to access the FromSql method to execute the stored procedure and map the result to your type. That’s what I already showed in the SO answer:

c# public virtual IQueryable<StoredProcRow> GetLoanDetails() { return Set<StoredProcRow>().FromSql("[sp_GetLoanDetails]").AsNoTracking(); }

A general note: Since you’re creating those stored procedure entity as normal entities, and since there is literally no difference to “normal” entities in your context, they will appear in migrations. There is currently no way to avoid this (as per #3436 and this issue), so you need to manually remove those from the generated migrations if you want to avoid creating tables for your “virtual” entities.

[maclax]

@rajeshj18 I have handled the same scenario.
Please find the below code:

public DbSet<SalesDataModel> SalesDataModel { get; set; } var salesDataQuery = await SalesDataModel.FromSql("exec REPORTFORWEB_SALES @StartDate, @EndData, @Division, @Supplier, @ITEMCODE, @ITEMDESCRIPTION, @CLIENTCODE, @CLIENTNAME, @CLIENTBRANCHCODE, @CLIENTBRANCHNAME, @BRANDCODE, @BRANDNAME, @USERCODE, @USERNAME, @SUBCHANNEL", new SqlParameter("@StartDate", startDate), new SqlParameter("@EndData", endDate), new SqlParameter("@Division", Division == null ? (object)DBNull.Value : Division), new SqlParameter("@Supplier", SupplierName == null ? (object)DBNull.Value : SupplierName), new SqlParameter("@ITEMCODE", DBNull.Value), new SqlParameter("@ITEMDESCRIPTION", SKU == null ? (object)DBNull.Value : SKU), new SqlParameter("@CLIENTCODE", DBNull.Value), new SqlParameter("@CLIENTNAME", Customer == null ? (object)DBNull.Value : Customer), new SqlParameter("@CLIENTBRANCHCODE", DBNull.Value), new SqlParameter("@CLIENTBRANCHNAME", DBNull.Value), new SqlParameter("@BRANDCODE", DBNull.Value), new SqlParameter("@BRANDNAME", BrandName == null ? (object)DBNull.Value : BrandName), new SqlParameter("@USERCODE", DBNull.Value), new SqlParameter("@USERNAME", Salesman == null ? (object)DBNull.Value : Salesman), new SqlParameter("@SUBCHANNEL", Channel==null?(object)DBNull.Value:Channel)).ToListAsync();

Here in the SalesDataModel, I define all the attributes that map to the parameters of the stored procedure.

[irowbin]

How can i get output parameter value? The output value is always null.

SP parameter

@todoCounts VARCHAR(MAX) OUTPUT
 ```

works  with  `_context.Database.ExecuteSqlCommand(...)` but not with `_context.Set<T>().FromSql(...)`

```javascript

IQueryable<T> = _context.Set<T>().FromSql("Sp_Todo @todoCounts OUTPUT", // i also tried with OUT or without both Out/Output but no luck
 new SqlParameter("@todoCounts", SqlDbType.Varchar)
 { Direction = ParameterDirection.Output});

[BladeWise]

I think that output parameters are only available once the query has been actually executed and the underlying reader is closed.
So, have you tried to materialize the queryable (call ToList) and then inspect the output parameter?

[irowbin]

@BladeWise I did call something like this,

var total=_context.Set<T>().FromSql("Sp_Todo @todoCounts OUTPUT", 
 new SqlParameter("@todoCounts", SqlDbType.VarChar)
 { Direction = ParameterDirection.Output}).ToList() // no luck

If i add this too, It works.

   _context.Database.ExecuteSqlCommand("Sp_Todo @todoCounts OUTPUT", 
 new SqlParameter("@todoCounts", SqlDbType.VarChar)
 { Direction = ParameterDirection.Output}); // works

Am i doing something wrong here? Why isn't working the first statement? Thanks

[poke]

I can confirm that this does not appear to work with FromSql. Maybe you want to create a new issue for that @irowbin?

[snickler]

I made a blog post on something I did as a workaround until this is completely supported:

http://sinclairinat0r.com/2017/05/06/entity-framework-core--mapping-stored-procedures,-fluently

[verdie-g]

Christmas nuget for people who are still struggling with stored procedures : https://www.nuget.org/packages/StoredProcedureEFCore/0.1.0

[briziomusic]

thank you @verdie-g!
this is a very big present! I'm actually using it with mariadb database and it works!!!
I'm waiting for async methods

[verdie-g]

@briziomusic I'm glad it helped you

I've extended IDataReader which doesn't support ReadAsync and NextResultAsync methods. I should have extended DbDataReader. I will replace IDataReader with DbDataReader in the next realease, which will break the API :/

I also have to find a way to mock completely a DbDataReader for testing purposes.

[briziomusic]

@verdie-g amazing job!
go ahead please :)

[verdie-g]

@briziomusic Done : https://github.com/verdie-g/StoredProcedureEFCore/releases/tag/v0.3.0

[briziomusic]

@verdie-g thank you! I'm going to test and use it tomorrow

[Kabindas]

I still can't scaffold stored procedures. Are there any plans to include this on road map ?

[ajcvickers]

@Kabindas This issue is in the Backlog milestone. This means that it is not going to happen for the 3.0 release. We will re-assess the backlog following the 3.0 release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

[Kabindas]

@ajcvickers thank you for the feedback

[rkmcquillen]

"I still can't scaffold stored procedures"
-- The hard part is getting the tables/columns quickly and efficiently. I would volounteer to program it myself, this weekend... if I had access to the code. I'm sure Microsoft has code from Sql2Linq and EDMX to do this already. Anyways, here is the magic query to get all sp's and resulting columns from a database in under a second:
SELECT p.name, r.*
FROM sys.procedures AS p
CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r;

I was considering adding this to a tt4 file and having a personal solution. I mean 100% of my objects that I access are always stored procs and never tables directly. I've programmed like that for 17 years... cleaner/faster/better, easier to maintain. Sql has multi-threading built in... 0 lines of code to add... so that's what I mean faster.

[rkmcquillen]

"I still can't scaffold stored procedures"
--Proposed Solution - 97 lines of code: 3 hours + 1 hour of googling yesterday
Note: adding a few "in" statements, probably reduces to 80 lines of code.
ie. "WHEN r.system_type_name in('smalldatetime','datetime',''datetime2','date','time') THEN 'DateTime'"

CREATE PROCEDURE usp_Entity_SP (@NameSpace VARCHAR(100) = 'WebApplication1')
AS
SET NOCOUNT ON

DECLARE @Counter INT = 1
DECLARE @End INT = 0
DECLARE @SP_Name VARCHAR(250) = ''
DECLARE @CS_Type VARCHAR(100) = ''
DECLARE @Column_Name VARCHAR(250) = ''
DECLARE @Column_Ordinal INT = 0

SELECT IDENTITY(INT, 1, 1) AS RowID
,p.NAME AS Sp_Name
,CASE r.system_type_name
WHEN 'bigint' THEN 'long'
WHEN 'smallint' THEN 'short'
WHEN 'int' THEN 'int'
WHEN 'UNIQUEIDENTIFIER' THEN 'Guid'
WHEN 'smalldatetime' THEN 'DateTime'
WHEN 'datetime' THEN 'DateTime'
WHEN 'datetime2' THEN 'DateTime'
WHEN 'date' THEN 'DateTime'
WHEN 'time' THEN 'DateTime'
WHEN 'float' THEN 'double'
WHEN 'real' THEN 'float'
WHEN 'numeric' THEN 'decimal'
WHEN 'smallmoney' THEN 'decimal'
WHEN 'decimal' THEN 'decimal'
WHEN 'money' THEN 'decimal'
WHEN 'tinyint' THEN 'byte'
WHEN 'bit' THEN 'bool'
WHEN 'image' THEN 'byte[]'
WHEN 'binary' THEN 'byte[]'
WHEN 'varbinary' THEN 'byte[]'
WHEN 'timestamp' THEN 'byte[]'
WHEN 'geography' THEN 'Microsoft.SqlServer.Types.SqlGeography'
WHEN 'geometry' THEN 'Microsoft.SqlServer.Types.SqlGeometry'
ELSE 'String'
END + CASE
WHEN R.system_type_name IN ('BigInt','Int','SmallInt','TinyInt','float','real','numeric','smallmoney','decimal','money','bit','UNIQUEIDENTIFIER'
,'smalldatetime','datetime','datetime2','date','time')
AND IS_Nullable = 1
THEN '?'
ELSE ''
END AS CS_Type
,replace(IsNull(r.NAME, 'Unnamed'), ' ', '_') AS Column_Name
,r.column_ordinal
INTO #Tentity
FROM sys.procedures AS p
CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r
WHERE r.system_Type_Name IS NOT NULL

SET @End = @@IDENTITY

---

PRINT 'using System;'
PRINT 'using System.Collections.Generic;'
PRINT 'using System.ComponentModel.DataAnnotations;'
PRINT 'using System.Linq;'
PRINT 'using System.Threading.Tasks;'
PRINT ''
PRINT 'namespace ' + @NameSpace + '.Models'
PRINT '{'

WHILE @Counter <= @End
BEGIN
SELECT @SP_Name = Sp_Name
,@CS_Type = CS_Type
,@Column_Name = Column_Name
,@Column_Ordinal = column_ordinal
FROM #Tentity
WHERE RowID = @Counter

IF @Counter > 1
    AND @Column_Ordinal = 1
BEGIN
    PRINT '    }'
    PRINT ''
END

IF @Column_Ordinal = 1
BEGIN
    PRINT '    public class ' + @SP_Name + 'Result'
    PRINT '    {'
    PRINT '        [Key]'
    PRINT '        ' + @CS_Type + ' ' + @Column_Name + ' { get; set; }'
END
ELSE
    PRINT '        ' + @CS_Type + ' ' + @Column_Name + ' { get; set; }'

SET @Counter = @Counter + 1

END

DROP TABLE #Tentity

PRINT ' }'
PRINT '}'

[ljani]

As a workaround, I'm combining query types, scalar functions and a fake, oracle-style V_Dual view.

Migration for PostgreSQL:

CREATE VIEW "V_Dual" AS SELECT 'X' AS "Dummy";

CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
        BEGIN
                -- What if this issued an UPDATE?
                RETURN i + 1;
        END;
$$ LANGUAGE plpgsql;

EF Configuration:

public class Dual
{
    public char Dummy { get; set; }
}

public class MyContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query<Dual>()
            .ToView("V_Dual");
    }

    [DbFunction]
    public static int Increment(int i) {
        throw new Exception();
    }
}

Finally an example of a query:

await _context
    .Query<Dual>()
    .Select(_ => MyContext.Increment(2))
    .SingleAsync()
    .ConfigureAwait(false);

I wonder if there are any downsides to this workaround? Is there a risk of the output of the function to be cached, ie. is it safe to issue eg. UPDATE in the function?

[poke]

@natemcmaster I don’t think this should have been closed? I think your merge of Microsoft.Data.Sqlite in #13843 may have closed some issues accidentally. I wonder if this has happened before with some of the repo merges (and it will likely happen in future 😕)

[natemcmaster]

Oops. Yes, unintentional close caused by merging repos together. It happens again, just let me know and I can re-open.

[shootermacg]

If you are using sql server, just create an EF project using the framework and copy over the generated data classes.

[Kabindas]

If you are using sql server, just create an EF project using the framework and copy over the generated data classes.

You can only do that on Windows... and having a framework project just for scaffolding doesn't seem the best solution :/

[d4dpkrajput]

If i return the SELECT * FROM tablename, the procedure calls works, but if i return custom select statement result ( using joins on two tables, in here its class: SP_GetAssignedLockers) ,
```C#
public class SP_GetAssignedLockers
{
public string LockerNumber { get; set; }
public int EmployeeNumber { get; set; }
public string EmployeeName { get; set; }
public DateTime AssignedDate { get; set; }
public string AssignedBy { get; set; }
}

This call doesn't work.
Am i missing something ? 
```C#
        public IEnumerable<SP_GetAssignedLockers> GetAllWithSP()
        {
            var a =  RepositoryContext.Set<SP_GetAssignedLockers>().FromSql("dbo.SP_GetAssignedLockers").ToList();
            return a;
        }

[ajcvickers]

@d4dpkrajput Please open a new issue and include a small, runnable project/solution or complete code listing that demonstrates the behavior you are seeing.

[d4dpkrajput]

Hi ajcvickers, I am done with the code. I created a class ( Entity ) which is the return type of the stored procedure, created an [ID] column in that class. Because it requires a primary key in the class. and then

MyCode :
C# public IEnumerable<SP_GetAssignedLockers> GetUsedLockers() { return RepositoryContext.SP_GetAssignedLockers.FromSql("SP_GetUsedLockers") .AsNoTracking().ToList(); }
To call stored procedure in the Entity Framework Database First approach code , we need following things

1. Class = Return type of the stored procedure
2. Primary Column Property in that class
3. I had to call method with AsNoTracking()
4. Treate SP_GetAssignedLockers as a table, so add it into the context class
   public virtual DbSet SP_GetAssignedLockers { get; set; }
5.

That what i observed.
Thanks for your help and support !!

[ian-m-cooper]

For me, two of the most useful features in EF6 is:

1. The ability to visualize my data model in Visual Studio, especially since I largely use the DB first approach.

2. The use of Function Imports to import and call complex stored procedures.

So I can live without point 1, but much of my work involves the use of complex stored procedures that simply don't lend themselves being converted to some LINQ implementation or EF Core workaround.

I can't understand why it seems to be so hard to reproduce in EF Core what's already been done in EF6 but until such time that SPs are supported I can see no incentive to move to EF Core.

[roji]

The ability to visualize my data model in Visual Studio, especially since I largely use the DB first approach.

There are several 3rd-party tools available which do this, you may want to take a look.

The use of Function Imports to import and call complex stored procedures.

You can call complex stored procedures with EF Core. Reverse engineering them from the database isn't supported natively, but again one of the 3rd parties may be able to do this.

[ian-m-cooper]

@roji thank you for your reply but what you are really saying is that I should look at an alternative ORM tool from another vendor and ditch EF Core entirely.

[MihaMarkic]

@ian-m-cooper I think @roji is suggesting 3rd party tools for designer, not for engine. I know that LLBLGenPro supports code generation for EF Core and has a nice and powerful designer. However I would consider its engine as well.

[roji]

Yeah, as @MihaMarkic wrote, there are 3rd-party tools that work very well with EF Core and do exactly what you're asking for - it doesn't mean ditching EF Core at all. And there are indeed also non-EF Core alternatives like LLBLGen if you want to go that way.

[AchilliaChris]

Which tool allows use of stored procedures generated as functions? Many of those tools describe creating POCO classes to represent entities, but what we need is the already existing function from stored procedure generation to be included in ef core.
Since it is the main element used for most web applications (to map db content to view models), it should have been a very high priority from the outset.

[mathewsun]

6 years have passed! How many time we should wait this functionality?!!!
It main functionality for rapid development.

[Kabindas]

6 years have passed! How many time we should wait this functionality?!!!
It main functionality for rapid development.

Absolutely, how something, apparently, so simple, can take so long :(

[AndriySvyryd]

@mathewsun @Kabindas See our release planning process

[DarioN1]

Please, have a look there:

https://github.com/DarioN1/SPToCore

I have created a kind of scaffolder for Stored Procedure, I'm using it in a new project that uses an existing database with more than 300 stored procedures.

[the-super-coder]

We don't need a designer, but at least generating the model for the return type of stored procedure is highly needed in our project. That is the only limitation which is holding us from migrating to .Net Core

May be before before bringing other new features, MS should be considerate about bringing this core feature of EF.

My two cents.

[Vasim-DigitalNexus]

@the-super-coder - It has been six (6) years, I doubt that this will become a priority; I think we are the minorities using stored procedure mapping, in fact, all my projects use stored procedures, I would love this enhancement. However, "and I could be wrong," stored procedure mapping is probably not needed by too many developers since they are mapping directly to tables/views.

[ErikEJ]

FWIW, its high on the list for new features in EF Core Power Tools.

[DarioN1]

This is a very supernews @ErikEJ!!!

[NikhilVirani]

Import Stored Procedure Function in .Net Core
https://github.com/NikhilVirani/DotnetCore.DB

[dominikjeske]

I have lost focus on EF Core changes - are TVP parameters already supported or still not?

[ErikEJ]

Latest release of EF Core Power Tools supports mapping / reverse engineering of SQL Server stored procedures

[dominikjeske]

hmm I'm not sure is this answering my question - reverse engineering is a tool for generate code that I can write manually, it is cool they work with SP but my question is about TVP. Is that generated code (or code that I can write manually) can use Table-Valued Parameters?

[ErikEJ]

@bigdnf that was not a reply to your question, but information for people following this issue.
Yes, TVP SqlParameter with a stored procedure is supported when using FromSqlRaw.

[QuanTran91]

@ErikEJ Although SqlParameter with a stored procedure is supported, named parameters are not supported yet in EF Core.

[roggan87]

@ErikEJ sounds interesting! I can't find too much information on the repo on how to use the tool for mapping. Got any resource to point to?

[ErikEJ]

There is info on the wiki and I also have this blog post https://erikej.github.io/efcore/2020/08/10/ef-core-power-tools-stored-procedures.html

@roggan87

[roggan87]

Thanks @ErikEJ I'll definitely check it out!

[ian-m-cooper]

Hi @ErikEJ. I have just discovered EFCore Power Tools and, for me at least, it looks to be exactly the missing piece from EFCore for those of us that want to do database first development and use a lot of stored procedures.

I do have one question though and I apologise if this seems a bit of a newbie question. In the model diagram dgml view, what is the convention you are using for relationship cardinality. I am used to diagrams where there is a cardinality at each end of the relation (EF6 style), but your diagrams seem to use a single cardinality on a relation with an arrow and this has confused me. It does not appear to be Chen, UML, Crows Foot. Is there a name for this type of approach ? I have attached an example of a 1 to Many relationship.

Many thanks.

[ErikEJ]

I honestly cannot remember why this notation is used, but the reason to have text labels is mainly due to constraints from the diagram format.

If you have ideas for a better text label, feel free to suggest or even create a PR.

Looks like there are some proposals here: https://www.gatevidyalay.com/cardinality-in-er-diagram/