title: How to call Stored Procedure with join on multiple tables in Entity Framework Core?
body: <p>I have to call a stored procedure which is selecting records from multiple tables.</p>
  
  <p>I have tried the following code, but it's returning null for columns from other tables than the entity class.</p>
  
  <pre><code>private async Task&lt;IEnumerable&lt;TEntity&gt;&gt; InvokeStoredProcedureAsync(string input = "")
  {
      var storedProcedureName = "sp_BulkSelect";
  
      using (var db = new MyDbContext(_options))
      {
          var result = await db.Set&lt;TEntity&gt;().FromSql(storedProcedureName + " @inputIds", new SqlParameter("inputIds", input)).ToListAsync();
          return result;
      }
  }
  </code></pre>
  
  <p>Stored procedure:</p>
  
  <pre><code>SELECT 
      [MainTable].[Id],
      [Table1Id],
      [Table2Id],
      [MainTable].[Table1Code],
      [Table2].[Table2Code]
  FROM
      [MainTable] [MainTable]
  LEFT JOIN 
      [Table1] [Table1] ON [MainTable].Table1Id = [Table1].[Id]
  LEFT JOIN 
      [Table2] [Table2] ON [MainTable].[Table2Id] = [Table2].[Id];
  </code></pre>
  
  <p><code>MainTable</code> class:</p>
  
  <pre><code>[Table("MainTable")]
  public class MainTable : FullAuditedEntity
  {
  
      [ForeignKey("Table1Id")]
      public virtual Table1 Table1 { get; set; }
      public virtual int Table1Id { get; set; }
  
      [ForeignKey("Table2Id")]
      public virtual Table2 Table2 { get; set; }
      public virtual int? Table2Id { get; set; }      
  
  }
  </code></pre>
  
  <p>So when I call this stored procedure, <code>Table1Code</code> and <code>Table2Code</code> are missing in the return value.</p>
  
  <p>I tried to add the following code in <code>MainTable</code> class, but its also not working.</p>
  
  <pre><code>[NotMapped]
  public virtual string Table2Code { get; set; }
  
  [NotMapped]
  public virtual string Table1Code { get; set; }
  </code></pre>
  
  <p>Then I removed <code>[NotMapped]</code> from both the properties and added migration, in this case, its returning proper value. But It will add two columns in MainTable. <strong>It's really a <code>BAD</code> design</strong>.</p>
  
  <p>So my question is how to select columns from multiple tables in the stored procedure in Entity Framework Core.</p>
  
  <p>I'm using EF Core 2.0.</p>
  
  <p>I think there has to be some way to call the stored procedure with using Entity and then map it to any class because select columns from multiple tables using join is a very basic requirement.</p>
  
  <p>I tried the similar <a href="https://stackoverflow.com/a/27807174/6527049">solution</a>, but its giving compilation error.</p>
  
  <blockquote>
    <p>'DatabaseFacade' does not contain a definition for 'SqlQuery' and no
    extension method 'SqlQuery' accepting a first argument of type
    'DatabaseFacade' could be found (are you missing a using directive or
    an assembly reference?)</p>
  </blockquote>