0

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);

but nothing has worked.

Error message is shown as below. And this method seems quite an ad-hoc execution. 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.

Unhandled Exception: System.Data.Entity.Core.EntityCommandExecutionException: The data reader is incompatible with the specified 'TestEF_Code.CustomerDetail'. A member of the type, 'CustomerID', does not have a corresponding column in the data reader with the same name. at System.Data.Entity.Core.Query.InternalTrees.ColumnMapFactory.GetMemberOrdinalFromReader(DbDataReader storeDataReader, EdmMember member, EdmType currentType, Dictionary2 renameList) at System.Data.Entity.Core.Query.InternalTrees.ColumnMapFactory.GetColumnMapsForType(DbDataReader storeDataReader, EdmType edmType, Dictionary2 renameList) at System.Data.Entity.Core.Query.InternalTrees.ColumnMapFactory.CreateColumnMapFromReaderAndType(DbDataReader storeDataReader, EdmType edmType, EntitySet entitySet, Dictionary2 renameList) at System.Data.Entity.Core.Objects.ObjectContext.InternalTranslate[TElement](DbDataReader reader, String entitySetName, MergeOption mergeOption, Boolean streaming, EntitySet& entitySet, TypeUsage& edmType) at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryInternal[TElement](String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters) at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass691.b__68() at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass691.b__67() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func1 operation) at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryReliably[TElement](String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters) at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQuery[TElement](String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters) at System.Data.Entity.Internal.Linq.InternalSet1.<>c__DisplayClass11.b__10() at System.Data.Entity.Internal.LazyEnumerator1.MoveNext() at System.Linq.Enumerable.Single[TSource](IEnumerable1 source) at TestEF_Code.Program.Main(String[] args) in B:\Users\trio\Programming\TestEF-Code\TestEF-Code\Program.cs:line 17

Improve this question
7
  • afaik, if you want to use stored procedure you have to use database first approach.
    – Bagus Tesa
    Commented May 20, 2022 at 4:17
  • As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text.
    – Dale K
    Commented May 20, 2022 at 4:19
  • Yes, I'm exactly using database first approach
    – Hoy Cheung
    Commented May 20, 2022 at 6:14
  • Read the Exception message. It has useful information. It also means that "nothing has worked" isn't true. EF did execute the sproc (the first line is correct) but it doesn't match.
    – Gert Arnold
    Commented May 20, 2022 at 6:39
  • so it is the only and official way to go? i don't understand the error message. What should I do to make it work?
    – Hoy Cheung
    Commented May 20, 2022 at 6:52
Related questions
2
Use a stored procedure in entity framework
0
How to call stored procedure in Entity Framework?
0
How to call Stored Procedure from in EF 6.1?
Load 7 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.