How to execute stored procedures in Entity Framework?

Question

How do I create a function mapping a SQL stored procedure?

I have tried

var order = context.CustomerDetails.SqlQuery("DisplayTotalOrders @CustomerID", new SqlParameter("CustomerID", "1"));

var order = context.CustomerDetails.SqlQuery("DisplayTotalOrders @CustomerID", 1);

return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Nullable<int>>("DisplayTotalOrders", customerIDParameter);

but nothing has worked.

Error message for the last one is shown as below.
Unhandled Exception: System.Data.Entity.ModelConfiguration.ModelValidationException: One or more validation errors were detected during model generation:

TestEF_Code.DisplayTotalOrder: : EntityType 'DisplayTotalOrder' has no key defined. Define the key for this EntityType.
TotalOrders: EntityType: EntitySet 'TotalOrders' is based on type 'DisplayTotalOrder' that has no keys defined.

at System.Data.Entity.Core.Metadata.Edm.EdmModel.Validate()
at System.Data.Entity.DbModelBuilder.Build(DbProviderManifest providerManifest, DbProviderInfo providerInfo)
at System.Data.Entity.DbModelBuilder.Build(DbConnection providerConnection)
at System.Data.Entity.Internal.LazyInternalContext.CreateModel(LazyInternalContext internalContext)
at System.Data.Entity.Internal.RetryLazy`2.GetValue(TInput input)
at System.Data.Entity.Internal.LazyInternalContext.InitializeContext()
at System.Data.Entity.Internal.InternalContext.Initialize()
at System.Data.Entity.Internal.InternalContext.ForceOSpaceLoadingForKnownEntityTypes()
at System.Data.Entity.DbContext.System.Data.Entity.Infrastructure.IObjectContextAdapter.get_ObjectContext()
at TestEF_Code.b365context.GetTotalOrders(Int32 CustomerID) in B:\Users\trio\Programming\TestEF-Code\TestEF-Code\b365context.cs:line 27

I found somewhere that i need to include [Keyless] attribute. But the attribute belongs to EF core. I am working on a .net FW 4.8 project. How do I add Keyless attribute to .net FW 4.8 method mapping to a stored procedure ?

Is there a way destined to create a method which binds a SQL Server stored procedure in EF? I am not talking about just INSERT, UPDATE, DELETE functions. I am referring to executing any SQL Server stored procedure. Please give me some reference which is up to date.

Thanks

Answer 1

@Hoy Cheung , Welcome to Microsoft Q&A, please refer to the following steps to execute the stored procedure in ef for a keyless table.

First, I create a table and stored procedure like the following:

CREATE PROCEDURE [dbo].[GetAge]  
 @age int   
AS  
 SELECT Name from Employee where Age>@age  
RETURN 0  
   

Second, Please add a new item called ADO.NET Entity Data Model to my project.

Second, Please click EF Designer from database and click Next.

Third, Please choose database and click Next.

Fourth, Please choose the table and the stored procedure and click finish.

Finally, we could see the generted code like the following:

 public virtual ObjectResult<string> GetAge(Nullable<int> age)  
        {  
            var ageParameter = age.HasValue ?  
                new ObjectParameter("age", age) :  
                new ObjectParameter("age", typeof(int));  
      
            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<string>("GetAge", ageParameter);  
        }  

We could get the data by using the following code:

   TestEntities context = new TestEntities();  
    var result=context.GetAge(26);  

The result:(the username who's age is less than 26)

Hope the above solution could help you.

Best Regards,
Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

The easier way is a "EF function import" for the stored procedure, then you get the SP as OR method to execute it directly, see
https://learn.microsoft.com/en-us/ef/ef6/modeling/designer/stored-procedures/query