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
– Sibeesh Venu, Commented Jun 10, 2016 at 8:55
I am using the same as the example in the link you provided. Thanks — Sibeesh Venu
– Sibeesh Venu, Commented Jun 10, 2016 at 9:03
Thanks, Yes I am calling a stored procedure. I have mentioned it in my question :) — Sibeesh Venu
– Sibeesh Venu, Commented Jun 10, 2016 at 9:06

Chris Cooper · Accepted Answer · 2016-06-10 09:16:02Z

0

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();

answered Jun 10, 2016 at 9:16

Chris Cooper

3894 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sibeesh Venu Over a year ago

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

Chris Cooper Over a year ago

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

Sibeesh Venu Over a year ago

I am using the property names as in the select query. When I use a single class with all property, it is working.

Community · Accepted Answer · 2017-05-23 10:34:01Z

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.

Collectives™ on Stack Overflow

Bind the SQL data to model property in Entity Framework

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related