EF5 db.Database.SqlQuery mapping returned objects

c# dbcontext entity-framework sql

Question

I have two classes in C#.

public class SearchResult
{
    public int? EntityId { get; set; }
    public string Name { get; set; }
    public Address RegisteredAddress { get; set; }
}

and

public class Address
{
    public int? AddressId { get; set; }
    public string Address1 { get; set; }
    public string Address2 { get; set; }
    public string Address3 { get; set; }
}

this is utilized in a dbContext call to use EF5 to map out the objects that a database returns.

using (DbEntities db = new DbEntities())
{
    querySearchResult = db.Database.SqlQuery<SearchResult>(
        @"SELECT e.entity_id AS EntityId, e.entity_reg_name AS Name,
              a.address_1 AS [RegisteredAddress.Address1]
          FROM
              entity AS e
              LEFT JOIN address AS a ON e.entity_reg_addr_id = a.address_id",
        objectParameterList.ToArray()).ToList();
}

Even though there is address data returned, I can't seem to map the address object, which is my problem. The searchResult's other properties map correctly.

1
10
3/12/2014 6:32:40 PM

Accepted Answer

SqlQuery does not provideComplex Type

What you need to do is

internal class TempResult
{
    public int? EntityId { get; set; }
    public string Name { get; set; }
    public int? AddressId { get; set; }
    public string Address1 { get; set; }
    public string Address2 { get; set; }
    public string Address3 { get; set; }
}

var tempResults = db.Database.SqlQuery<TempResult>(
    @"SELECT e.entity_id AS EntityId, e.entity_reg_name AS Name,
          a.address_1 AS [RegisteredAddress.Address1]
      FROM
          entity AS e
          LEFT JOIN address AS a ON e.entity_reg_addr_id = a.address_id",
    objectParameterList.ToArray()).ToList();

querySearchResult = tempResults.Select(t => new SearchResult
{
    EntityId = t.EntityId,
    [...]
    RegisteredAddress = new Address 
        {
            AddressId = t.AddressId,
            [...]
        }
}
11
3/12/2014 8:02:24 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow