EF5 db.Database.SqlQuery mapping 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 map out the returning objects from a database via 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'm having is that I cant seem to get the address object mapped even though there is address data returned. The other properties of the searchResult map fine.

Alexandre Rondeau · Accepted Answer · 2014-03-12 20:02:24Z

14

SqlQuery doesn't support Complex Type

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

edited Mar 12, 2014 at 20:02

answered Mar 12, 2014 at 18:26

Alexandre Rondeau

2,68725 silver badges31 bronze badges

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

2 Comments

JP Hellemons Over a year ago

Could you define complex type? If a class inherits, would the parent/base class props work? And is this still valid in EF 6.2? I can't find much about this on msdn...

Toolkit Over a year ago

what if some property is a List<SomeClass> ?

Collectives™ on Stack Overflow

EF5 db.Database.SqlQuery mapping returned objects

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related