EF5 db.Database.SqlQuery matching returned objects

Question

I have two C # classes

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 used in a dbContext call to display the returned objects from the database through EF5

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

The problem I am facing is that I cannot get the address object, even if the address data is returned. Other properties of the searchResult fine map.

Answer 1

SqlQuery does not support Complex Type

What you need to do:

 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, [...] } }