none
DBContext query to execute a SP RRS feed

  • Question


  • When trying to execute this using dbContext:

    var _alist2 = db.Database.SqlQuery<string>
                      (
                      " SELECT PartyID, PartyName, TypeName FROM tblParty " +
                      " LEFT OUTER JOIN tbltype on partyTypeID_fk=partytypeid " +
                      " WHERE partyID BETWEEN @p0 AND @p1 ", @p0, @p1
                      )
                      .ToList();          
    


    I get the following run-time error:              

    The data reader has more than one field. Multiple fields are not valid for EDM primitive types.

    I then tried to use Anonymous Type, but got compilation errors:

    var _alist2 = db.Database.SqlQuery<string>
                      (
                      " SELECT PartyID, PartyName, TypeName FROM tblParty "+
                      " LEFT OUTER JOIN tbltype on partyTypeID_fk=partytypeid " +
                      " WHERE partyID BETWEEN @p0 AND @p1 ", @p0, @p1
                      )
                      .select new
                        {
                            partyID   = tblParty.PartyID,
                            PartyName = tblParty.PartyName,
                            TypeName  = tblType.TypeName
                        };

    Your help is appreciated.




    Sunday, May 13, 2012 10:25 AM

Answers

  • Hi ForEverLearning;

    The DbContext.Database.SqlQuery<TElement>( ... ) method needs a return type that fits what the method will be returning. In your code you have it returning a string but looking at the query it is returning an object that contains three elements. Therefore you need to create a type that contains the three items with the same names as with the select statement and same data type. This return type also needs that the three elements are implemented as public properties as the PartyInfo class below.

    public class PartyInfo
    {
    	public int PartyID { get; set; }
    	public string PartyName { get; set; }
    	public string TypeName { get; set; }
    }

    Once you have implemented such a class then the query can be as follows:

    var _alist2 = db.Database.SqlQuery<PartyInfo>
                  (
                  " SELECT PartyID, PartyName, TypeName FROM tblParty " +
                  " LEFT OUTER JOIN tbltype on partyTypeID_fk=partytypeid " +
                  " WHERE partyID BETWEEN @p0 AND @p1 ", @p0, @p1
                  ).ToList(); 

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Sunday, May 13, 2012 2:09 PM

All replies

  • Hi ForEverLearning;

    The DbContext.Database.SqlQuery<TElement>( ... ) method needs a return type that fits what the method will be returning. In your code you have it returning a string but looking at the query it is returning an object that contains three elements. Therefore you need to create a type that contains the three items with the same names as with the select statement and same data type. This return type also needs that the three elements are implemented as public properties as the PartyInfo class below.

    public class PartyInfo
    {
    	public int PartyID { get; set; }
    	public string PartyName { get; set; }
    	public string TypeName { get; set; }
    }

    Once you have implemented such a class then the query can be as follows:

    var _alist2 = db.Database.SqlQuery<PartyInfo>
                  (
                  " SELECT PartyID, PartyName, TypeName FROM tblParty " +
                  " LEFT OUTER JOIN tbltype on partyTypeID_fk=partytypeid " +
                  " WHERE partyID BETWEEN @p0 AND @p1 ", @p0, @p1
                  ).ToList(); 

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Sunday, May 13, 2012 2:09 PM
  • Thank you sir for your continuous support. I am struggling with some elementary concepts and a bit confused with the many options and tools out there...I really appreciate your help.
    Sunday, May 13, 2012 2:53 PM
  •  

    Not a problem, I am always happy to help.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Sunday, May 13, 2012 3:05 PM