Bind the SQL data to model property in Entity Framework

Question

Good day to you. I am returning a result as preceding from SQL stored procedure.

Now I am binding the data as follows.

 List<EventPhotos> resList = null;
 resList = pe.Database.SqlQuery<EventPhotos>(sqlQuery, param).ToList();

Following is my model.

public partial class EventPhotos
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public EventPhotos()
        {

        }        

        public EventInfo EventInformation { get; set; }
        public ImageInfo ImageInformation { get; set; }

    }

    public class EventInfo
    {
        public string EventID { get; set; }

        public string EventName { get; set; }

        public bool IsJoint { get; set; }
    }
    public class ImageInfo
    {
        public string ImageID { get; set; }

        public string EventMediaFolder { get; set; }

        public string Image { get; set; }

        public string DateTime { get; set; }

        public string ImageDescription { get; set; }

        public string Action { get; set; }
    }

But when I run this, nothing gets mapped to my properties EventInformation and ImageInformation. Is there anyway to achieve this. I know this can be done if we use dataAdapter and assign names to the each table retrieved. But I want to use the Entity framework. Any help is appreciated. Thanks in advance.

Hi, Thanks for the reply. But that is for providing the parameter. In my case the data is all fine. I am getting the data if I am using a single class with the properties. Now the problem is when I split into two properties of separate class, it is not mapping automatically. — Sibeesh Venu, Jun 10 '16 at 08:55
I am using the same as the example in the link you provided. Thanks — Sibeesh Venu, Jun 10 '16 at 09:03
Thanks, Yes I am calling a stored procedure. I have mentioned it in my question :) — Sibeesh Venu, Jun 10 '16 at 09:06
Possible duplicate of [EF5 db.Database.SqlQuery mapping returned objects](http://stackoverflow.com/questions/22359522/ef5-db-database-sqlquery-mapping-returned-objects) — Vojtěch Dohnal, Jun 10 '16 at 09:56

score 0 · Answer 1 · answered Jun 10 '16 at 09:16

Try and add the table and column attributes

public class EventInfo
{

    [Column(Name = "EventID")]
    public string EventID { get; set; }


    [Column(Name = "EventName")]
    public string EventName { get; set; }


    [Column(Name = "IsJoint")]
    public bool IsJoint { get; set; }
}
public class ImageInfo
{

    [Column(Name = "ImageID" )]
    public string ImageID { get; set; }


    [Column(Name = "EventMediaFolder")]
    public string EventMediaFolder { get; set; }


    [Column(Name = "Image")]
    public string Image { get; set; }


    [Column(Name = "DateTime")]
    public string DateTime { get; set; }


    [Column(Name = "ImageDescription")]
    public string ImageDescription { get; set; }


    [Column(Name = "Action")] 
    public string Action { get; set; }
}

I use the below code to do the mapping

 returnItems = context.ExecuteQuery<EventPhotos>(sql).ToList();

When I try this, I am getting an error as Name is not a valid named attribute. Any idea how to fix this? Thanks in advance — Sibeesh Venu, Jun 10 '16 at 09:33
@Vojtěch Dohnal, has answered your question really. Both column name and table names within the attributes need to match the SQL query column names. — Chris Cooper, Jun 10 '16 at 09:36
I am using the property names as in the select query. When I use a single class with all property, it is working. — Sibeesh Venu, Jun 10 '16 at 09:47

score 0 · Answer 2 · edited May 23 '17 at 10:34

Database.SqlQuery:

Creates a raw SQL query that will return elements of the given type. The type can be any type that has properties that match the names of the columns returned from the query, or can be a simple primitive type. The type does not have to be an entity type. The results of this query are never tracked by the context even if the type of object returned is an entity type.

It means, that the column names of the resultset of your stored procedure must match with the names of properties defined in your POCO class EventPhotos. So you must create the class EventPhotos with exact names of the returned columns instead of adding two properties - the nested properties names cannot be found by the Database.SqlQuery method.

SqlQuery does not support complex types.

See a similar question here.

Bind the SQL data to model property in Entity Framework

2 Answers2