Fetching complex objects by raw SQL query in Entity Framework

Question

I would like to fetch from database complex object using single query. Let's look at the following example:

SELECT TableA.*, TableB.*
FROM TableA
INNER JOIN TableA.B_Id = TableB.Id

and corresponding classes:

public class QueryResult
{
    public TableA A { get; set; }
    public TableB B { get; set; }
}

public class TableA
{
    public int Id { get; set; }
    public string SomeContentA { get; set; }
    public int B_Id { get; set; }
}

public class TableB
{
    public int Id { get; set; }
    public int SomeContentB { get; set; }
}

I would like to execute the raw SQL query from above against the database and get collection of QueryResult objects with correctly set A and B properties. So far I tried using SqlQuery method, but I only managed to get collection of QueryResult objects with nulls in A and B properties (apparently returned result set was not correctly binded to properties):

var results = ctx.Database.SqlQuery<QueryResult>(\\example_query).ToList();

Note that:

• I shouldn't list manually columns in SELECT statement. TableA and TableB classes and SQL tables are likely to change over time, but those changes will be consistent.
• Three queries (one to fetch IDs from TableA and TableB, second to fetch objects from TableA, third for objects from TableB) will hurt performance and I should try avoid it if possible.

I am using Entity Framework 4.3 and SQL Server 2012.

Thanks, Art

Answer 1

You can still use regular EF constructions by just mapping your classes to their corresponding tables and forcing the join in LINQ-To-Entities:

using(var ctx = new MyDbContext())
{
    return ctx.TableA
         .Join(ctx.TableB, a=>a.B_Id, b=>b.Id, (a,b)=>
              new QueryResult{TableA=a, TableB=b});
}

I think that's the only way, at least up to EF6.