How to call stored procedure in WPF using MVVM pattern and DBContext

Question

I'm having a difficult time retrieving data from a stored procedure using my current project setup with WPF, MVVM, Code First. The first thing I have done is to create an object to represent the resultset coming back. I actually did this by using an existing view that I already had which had auto-created the code for me when doing reverse engineering with entity framework. My object is "spVessel".

Here is what I am trying:

// DAL: call stored procedure
        public List<spVessel> CallStoredProc()
        {
            object[] o = {new SqlParameter("RollYear", 2012) };
            return (List<spVessel>)this.Database.SqlQuery(typeof(List<spVessel>), "spGetMPTSVesselByASMT @RollYear @ASMT", o, null);
        }

I get an error on the call to the sqlquery:

Unable to cast object of type 'System.Data.Entity.Internal.InternalSqlNonSetQuery' to type 'System.Collections.ObjectModel.Collection'

What are recommendations on getting data from stored procedure using dbcontext? Thanks.

Answer 1

I can recommend an alternate to fix this problem.

Since you already generated the stored procedure (if not create a temporary stored procedure with required input/out parameters and logic) go to yourgenerate the code from the stored procedure and see what types it is creating and binding, so that you can change your list and collection types accordingly.

Let me know if you have any concerns on this.

---

Thank you,

Regards, Srigopal

Answer 2

On 8/17/2012 4:45 PM, RLuther wrote:

> I'm having a difficult time retrieving data from a stored procedure

> using my current project setup with WPF, MVVM, Code First. The first

> thing I have done is to create an object to represent the resultset

> coming back. I actually did this by using an existing view that I

> already had which had auto-created the code for me when doing reverse

> engineering with entity framework. My object is "spVessel".

>

> Here is what I am trying:

>

> // DAL: call stored procedure

>          public List<spVessel>  CallStoredProc()

>          {

>              object[] o = {new SqlParameter("RollYear", 2012) };

>              return (List<spVessel>)this.Database.SqlQuery(typeof(List<spVessel>),"spGetMPTSVesselByASMT @RollYear @ASMT", o, null);

>          }

>

> I get an error on the call to the sqlquery:

>

> Unable to cast object of type

> 'System.Data.Entity.Internal.InternalSqlNonSetQuery' to type

> 'System.Collections.ObjectModel.Collection'

>

> What are recommendations on getting data from stored procedure using

> dbcontext? Thanks.

>

 

Yeah that's right. What it's telling you is that you cannot cast an

object from one namespace to different namespace.

 

Objects originated in the

System.Data.Entity.Internal.InternalSqlNonSetQuery namespace and

System.Collections.ObjectModel.Collection' is another namespace that you

are trying to return and cast objects on the fly in mid-flight and

return them in a List<T> (System.Collection.ObjectModel.Collection>.

 

You can't do it. You can't cast an object in one namespace over to

another namespace the object didn't originate in.

 

Somehow, you are going to have to go into a reader loop on the returned

results from that sproc, and each iteration through the loop, you make a

custom object from the data from the results of the sproc, load a

List<custom objects> and return the List<custom objects>.

 

 

Answer 3

Ok, SqlQuery returns an object, not list of objects, so I need to loop after getting results.

var vessels = this.Database.SqlQuery<vwVessels>("exec spGetMPTSVesselByASMT @ASMT, @RollYear",
                new SqlParameter("ASMT", val), new SqlParameter("RollYear", rollyear));
            Collection<vwVessels> vesselsList = new Collection<vwVessels>();
            
            foreach (vwVessels item in vessels)
            {
                vesselsList.Add(item);
            }
            return vesselsList;