Facing Issue with Entity Framework Code First Approach Stored Procedure mapping

Question

User-1664020322 posted

Hi,

I'm calling a stored procedure in entity framework code first approach. Basically I have four columns for my table but using stored procedure I'm only returning two columns. As, im returning only two columns. I'm getting following error:

An exception of type 'System.Data.Entity.Core.EntityCommandExecutionException' occurred in EntityFramework.SqlServer.dll but was not handled in user code Additional information: The data reader is incompatible with the specified 'CodeFirstDepartment.Models.Department'. A member of the type, 'Id', does not have a corresponding column in the data reader with the same name.

If I use the same stored procedure and return all columns its working fine. 

Is it something to do with mapping.

Following is  my code:

DepartmentContext db = new DepartmentContext(); 
SqlParameter param1 = new SqlParameter("@FilterExpression", "Sandeep"); 
var list = db.Department.SqlQuery("sp_test_Ef @FilterExpression", param1).ToList(); 

Any help would be appreciated. 

Thanks,

Sandeep

Answer 1

User1120430333 posted

Is it something to do with mapping.

Yes, it has to do with the reader looking at all the properties of the Department object and expecting all the columns of the retuned result of the sproc  matching the properties of the Department object, mapping.

If you want to use the sproc the way you are wanting to do it, you could just use the EF backdoor, use ADO.NET, SQL Command objects, the sproc, a datareader and a custom type/object populating the object from selected columns in the reader loop, loading the custom object into a List<T> and return the collection. 

Answer 2

User-1664020322 posted

Is there any template for the suggestion you provided.

Answer 3

User1120430333 posted

Sandeep Apsingekar

Is there any template for the suggestion you provided.

All you want to do is use the EF connection to use ADO.NET, the SQL Command objects to run the sproc, a datareader populating a custom object and  load the object into a List<T>.

https://blogs.msdn.microsoft.com/alexj/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database/

You have to use the adapter to get a hold of the needed connection context.

https://msdn.microsoft.com/en-us/library/system.data.entity.infrastructure.iobjectcontextadapter%28v=vs.113%29.aspx?f=255&MSPPError=-2147217396

You see me using adapter in the below code that I have commented out. But the connection I did achieve so that if I wanted to go use the EF backdoor with just using ADO.NET and the MS SQL Server command objects in a traditional sense through EF, I could have done so., which I have done in previous versions of EF.

using System.Data.Entity;
using System.Data.Entity.Core.EntityClient;
using System.Data.Entity.Core.Objects;
using System.Data.Entity.Infrastructure;
using System.Data.SqlClient;

You will use some or all of the above using statements

https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

http://www.java2s.com/Code/CSharp/Database-ADO.net/ReferencedatainSqlDataReaderbycolumnname.htm

 public List<DTOStudent> GetStudents()
        {
           
            var dtos = new List<DTOStudent>();

            using (var context = new CUDataEntities())
            {
                //var adapter = (IObjectContextAdapter)context;
                //var objectContext = adapter.ObjectContext;
                
                //var entityConn = objectContext.Connection as EntityConnection;
                //var dbConn = entityConn.StoreConnection as SqlConnection;

                //dbConn.Open();

                var students = context.Students.ToList();

                foreach(var stud in students)
                {
                    var dto = new DTOStudent
                    {
                        StudentID = stud.StudentID,
                        FirstName = stud.FirstName,
                        LastName = stud.LastName,
                        EnrollmentDate = stud.EnrollmentDate
                    };

                    dtos.Add(dto);
                }
            }

            return dtos;
        }

Answer 4

User-1664020322 posted

Hi, 

I appreciate your time for looking into my issue, I'm bit confused now. 

What I understand from your response is, You want me to call Stored procedure using ADO.Net and then follow the code which you mentioned in the response to map it. 

Please, let me know if you meant this. 

Thanks, 

Sandeep

Answer 5

User1120430333 posted

Hi, 

I appreciate your time for looking into my issue, I'm bit confused now. 

What I understand from your response is, You want me to call Stored procedure using ADO.Net and then follow the code which you mentioned in the response to map it. 

Please, let me know if you meant this. 

Thanks, 

Sandeep

Use the EF backdoor, run the sproc and map the columns you want from the datareader to the custom object that you made. If you only coming back with two  columns then the custom object should have properties for those two columns.

EF is not going to allow you to not use all the properties on its object using the sproc.

Answer 6

User1120430333 posted

I like this even better than what I have suggested, if it works.

https://social.msdn.microsoft.com/Forums/en-US/61b7f6d7-8fdd-43e0-b64a-c81397940475/issue-with-mapping-stored-procedures-using-entity-framework-code-first-approach?forum=adodotnetentityframework

Answer 7

User1168443798 posted

Hi Sandeep,

For your issue, I suggest you try to define a ViewModel for Output and use "Database.SqlQuery".

A demo code like below:

DepartmentContext db = new DepartmentContext(); 
SqlParameter param1 = new SqlParameter("@FilterExpression", "Sandeep"); 
var list = db.Database.SqlQuery<ProductVM>("sp_test_Ef @FilterExpression", param1).ToList();


    public class ProductVM
    {
        public string Name { get; set; }
    }

Answer 8

User-1664020322 posted

Hi Everyone,

Thank you for all your help. This actually helps. When I use the following it is working:

db.Database.SqlQuery<ProductVM>("sp_test_Ef @FilterExpression", param1).ToList();

I appreciate your time. 

Thanks,

Sandeep