Updated March 30, 2023
Introduction to Entity Framework Join
Entity Framework Join is used to load the data from more tables. The Join operator enables to join more tables on one or more columns (with multiple columns). By default it performs the inner join of the tables; through the Entity Framework, we can use the left joins operator and DefaultIfEmpty method. The Join query is used to merge the data from more tables with the help of columns between the tables.
Overviews Entity Framework Join
Entity Framework Join is used to merge the data from various tables like one or more tables with the column match between those tables. Instead of LINQ Join to query, it is recommended to make use of navigational properties.
Let’s see the syntax of join method
SELECT[column-names]
FROM[table_1Name]
JOIN[table_2Name]
ON[table1-cloumn]=[table2_column]Let’s see the following queries which merging the tables Books and Author by using the Joins (), The following query combines Authors and Books tables using the Join() method.
using (var context = new BookDataStore())
{
var dataObj = context.AuthorDetails
.Join(
context.BookDetails,
author => author.AuthorId,
book => book.Author.AuthorId,
(_author, _book) => new
{
BookId = _book.BookId,
AuthorName = _author.Name,
BookTitle = _book.Title
}
).ToList();
foreach(var bookObj in dataObj)
{
Console.WriteLine("Book Title: {0} \n\t Written by {1}", bookObj.BookTitle, bookObj.AuthorName);
}
}How to Use Entity Framework Join?
Entity Framework Join is used to combine the data from multiple tables like one or more tables with the column match between those tables. Let’s understand with one example consider two tables Person and EmailID by using the join operator, it combines the two table data by using the keyword Equals for comparing particular properties. To get the records of table 1 People the query starts from p in db.Person its the outer table and to join the inner table we using like join e in db.EmailID and here the on keyword is used to specify the join condition. For comparing the conditions we make use of the equals keyword. Initially, you need to know to make use of the navigation properties, if we do not move with the navigation property then proceed with joining two or more tables,
Here just imagine we implementing two tables by using the join operator, this join operator uses the keyword “Equals” to compare desired properties. The query joins the tables Person with the table EmailID on the BusinessID key, this query same as SQL join queries, just code at ActionMethod as follows,
data = (from prn in db.People
join email in db.EmailID
on prn.BusinessID equals email.BusinessEntityID
where prn.UserName == "Rio"
select prn).ToList();Let’s see one example with two context classes which refers from two different databases, initially create Entity Framework Models, here the two tables of different databases
StudentInformation Model and the context name of this model is StudentInfo_Context as follows,
And the next model contains information about examination details of the student, and the context name of the following model is StuExamInfo_Context
Let’s create the class for storing the details about student results which we retrieve once after joining the tables. What we get actually is the name of the student through the mobile number.
To join the both tables as follows
public IEnumerable<FinalResult> SelectStudentList()
{
IEnumerable<FinalResult> student_info = null;
using (StudentInfo_Context studentsContext = new StudentInfo_Context ())
{
student_info = studentsContext.tblstudents.Select(x => new FinalResult()
{
StudentName = x.Studentname,
Sender = x.Mobileno
).Distinct().ToList();
}
using (StuExamInfo_Context smsDbContext = new StuExamInfo_Context ())
{
string keys = ConfigurationManager.AppSettings["Keys"];
var Temp = smsDbContext.sms_receive.AsEnumerable().Select(i => new
{
Sender = i.Sender,
ExamResult = i.ExamResult
}).ToList();
IEnumerable<FinalResult> get_TotalScore = Temp.Join(student_info, x => x.Sender, y => y.Sender,
(x, y) => new
{
Sender = x.Sender,
ExamResult = x.ExamResult,
StudentName = y.StudentName
}).GroupBy(x => new { x.Sender, x.StudentName })
.Select(s => new FinalResult()
{
NoOfExam = s.Count(p => p.Sender != null),
ExamResult = s.Sum(b => Convert.ToInt32(b.ExamResult)),
Sender = s.Key.Sender,
StudentName = s.Key.StudentName
}).ToList();
return get_TotalScore;
}
}
}To place the gridview control for displaying the result let see the below gridview control codings as follows,
<asp:GridView ID="Grid_Students" runat="server" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3" AutoGenerateColumns="False">
<Columns>
<asp:TemplateField HeaderText="Sl No">
<ItemTemplate>
<%#Container.DataItemIndex+1 %>
</ItemTemplate>
<ItemStyle HorizontalAlign="Center" VerticalAlign="Middle" />
</asp:TemplateField>
<asp:BoundField DataField="Studentname" HeaderText="Name"
SortExpression="Sender" />
<asp:BoundField DataField="Sender" HeaderText="Sender"
SortExpression="Sender" />
<asp:BoundField DataField="ExamResult" HeaderText="Score"
SortExpression="ExamResult" />
<asp:BoundField DataField="NoOfExam" HeaderText="No Of Exam"
SortExpression="ExamResult" />
</Columns>
<FooterStyle BackColor="White" ForeColor="#000066" />
<HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
<RowStyle ForeColor="#000066" />
<SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#007DBB" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#00547E" />
</asp:GridView>
</ContentTemplate>After that call the method in the form of events of the webpage and bind those data in the function of gridview,
public void DisplayStudents(GridView G_Students)
{
G_Students.DataSource = bel.DisplayStudents().Take(20);
G_Students.DataBind();
G_Students.EmptyDataText = "No Data Found.";
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DisplayStudents(Grid_Students);
}
}SQL QUERIES
In SQL Queries the JOIN Clause is mostly used to merge the rows from more tables like one or more tables with the help of related/ same column name between those tables. In Entity Framework we can make use of Join() method whereas in Entity Framework we can make use of both Join() and GroupJoin() method to retrieve the results. Let’s see the following queries joining the two tables Customers and Invoice
Lets us see one example with NorthWind database
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
<Columns>
<asp:BoundField DataField="Product ID" HeaderText=" Product ID " />
<asp:BoundField DataField="Product Name" HeaderText=" Product Name " />
<asp:BoundField DataField="Price" HeaderText Price " />
<asp:BoundField DataField="Category Name" HeaderText=" Category Name " />
<asp:BoundField DataField="Description" HeaderText=" Description " />
</Columns>
</asp:GridView>The following code joins and retrieve the result using joins in C# code,
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
NorthwindModel.NorthwindEntities db_entities = new NorthwindModel.NorthwindEntities();
var getDetails = (from productTB in db_entities.Products
join catgryTB in db_entities.Categories
on productTB.CategoryID equals catgryTB.CategoryID
orderby productTB.ProductID
select new
{
ProductID = productTB.ProductID,
ProductName = productTB.ProductName,
UnitPrice = productTB.UnitPrice,
CategoryName = catgryTB.CategoryName,
Description = catgryTB.Description
}).Take(5).ToList();
GridView_Products.DataSource = getDetails;
GridView_Products.DataBind();
}
}The output as follows,
Conclusion
This article has explained the Entity Framework Joins here we use the multiple tables with various conditions for joins, the joins condition is based on only the equality that equals the operator, and Entity Framework Join is used to load the data from more tables. Hope the article helps you to understand.
Recommended Article
This is a guide to Entity Framework Join. Here we discuss the Introduction, overview, and How to Use Entity Framework Join? examples along with code implementation and output. You may also have a look at the following articles to learn more –
