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

  • 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.

    Friday, August 17, 2012 8:45 PM

Answers

  • 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>.
     
     
    • Marked as answer by RLuther Friday, August 24, 2012 7:37 PM
    Saturday, August 18, 2012 8:56 PM

All replies

  • 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


    Saturday, August 18, 2012 12:46 PM
  • 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>.
     
     
    • Marked as answer by RLuther Friday, August 24, 2012 7:37 PM
    Saturday, August 18, 2012 8:56 PM
  • 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;

    Friday, August 24, 2012 7:41 PM