question

HoyCheung-4190 avatar image
0 Votes"
HoyCheung-4190 asked JackJJun-MSFT edited

How to execute stored procedures in Entity Framework?

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

dotnet-entity-framework
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JackJJun-MSFT avatar image
1 Vote"
JackJJun-MSFT answered JackJJun-MSFT edited

@HoyCheung-4190, 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:

204576-image.png

 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)

204549-image.png

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.



image.png (5.0 KiB)
image.png (37.9 KiB)
image.png (36.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered HoyCheung-4190 commented

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://docs.microsoft.com/en-us/ef/ef6/modeling/designer/stored-procedures/query

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

yeah, I know it's easy. But I'm working on a Database first model.
i have to write my own code.

0 Votes 0 ·