Getting multiple tables with one db call in entity infrastructure

Question

I am new to ASP.NET MVC and Entity framework. I used to work with ADO.NET Datatables and DataSets. Therefore, in scenarios such as retrieving multiple tables from a database in one call, I used a DataSet. eg.

ALTER PROCEDURE [dbo].[SpTest] As Begin Select * from Table1 Select * from Table2 End 

And in C # I usually did

 Dataset ds = DAL.GetDataSetBySp("SpTest"); Datatable dt1 = ds.Tables["Table1"]; Datatable dt2 = ds.Tables["Table2"]; 

Now If I want some tables with Entity Framework in Single Db Call, how can I do this?

Answer 1

you can do this, but the tables should have relationships:

 public class Table1 { [Key] public int IDCol{ get; set; } public string Col2{ get; set; } } public class Table2 { [Key] public int IDCol2{ get; set; } public int IDCol{ get; set; } public string Col3{ get; set; } } public class JoiningTable { public int IDCol1{ get; set; } public int IDCol2{ get; set; } public string Col2 { get; set; } public string Col3 { get; set; } } public List<JoiningTable> GetData() { List<JoiningTable> bothTablesData = from t in Table1 join t2 in Table2 on t.IDCol equals t2.IDCol1 select new { IDCol1 = t.IDCol, Col3 = t2.Col3, Col2 = t.Col2 }.ToList<JoiningTable>(); return bothTablesData; }