title: Fetching complex objects by raw SQL query in Entity Framework
body: <p>I would like to fetch from database complex object using single query. Let's look at the following example:</p>
  
  <pre><code>SELECT TableA.*, TableB.*
  FROM TableA
  INNER JOIN TableA.B_Id = TableB.Id
  </code></pre>
  
  <p>and corresponding classes:</p>
  
  <pre><code>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; }
  }
  </code></pre>
  
  <p>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):</p>
  
  <pre><code>var results = ctx.Database.SqlQuery&lt;QueryResult&gt;(\\example_query).ToList();
  </code></pre>
  
  <p>Note that:</p>
  
  <ul>
  <li>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.</li>
  <li>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.</li>
  </ul>
  
  <p>I am using Entity Framework 4.3 and SQL Server 2012.</p>
  
  <p>Thanks,
  Art</p>