How to Join Two Tables From Different Databases Using Entity Framework

Introduction

 

This article explains how to join two different tables from two different databases, or we may say how to join two tables from two different contexts, in Entity Framework.

 

The first thing we should remember is that it is not possible in Entity Framework to join two or more entities of different contexts directly. Then the question arises on how to join tables that are from a different context? So for that, we can create a memory object of both contexts and join them in memory and then return it back to the caller function. The following code shows how to do this.

 

Step 1

 

First of all, create two contexts that refer to two different databases. For creating the Entity Framework model click here.

 

In this example, I use the following two tables of two different databases.

1. WILMO_DBContext is the context for the following model.
    
    
   This table contains information about student information.
    
2. Wst_SMSContext is the context for the following model.
    
    
   This table contains information on the student examination information.
    
   Remember: Both tables are in different databases.
    
   For joining these two tables first create two different contexts as of above. If you are new to Entity Framework then click here to understand how to create a context in EF.

Step 2

 

Now create a class that will store information about the student results that we will get after joining both tables.

 

Actually I need to get the student name from tblstudent by mobile number.

public class SmsResult
{
    public int Id { get; set; }
    public Nullable<int> UserId { get; set; }
    public string Sender { get; set; }
    public string Receiver { get; set; }
    public string StudentName { get; set; }
    public string Message { get; set; }
    public System.DateTime OnDate { get; set; }
    public System.DateTime TsSent { get; set; }
    public int ExamResult { get; set; }
    public int NoOfExam { get; set; }
}

Step 3

 

Write the following function in the code behind to join both tables.

public IEnumerable<SmsResult> SelectStudentList()
{
       IEnumerable<SmsResult> studentwilmo = null;
       using (WILMO_DBContext students = new WILMO_DBContext())
       {
              studentwilmo = students.tblstudents.Select(x => new SmsResult()
              {
                    StudentName = x.Studentname,
                    Sender = x.Mobileno
                    ).Distinct().ToList();
              }
             using (Wst_SMSContext smsDb = new Wst_SMSContext())
             {
                   string keys = ConfigurationManager.AppSettings["Keys"];
                  var Temp = smsDb.sms_receive.AsEnumerable().Select(i => new
                  {
                       Sender = i.Sender,
                       ExamResult = i.ExamResult
                  }).ToList();

                 IEnumerable<SmsResult> TotalScore = Temp.Join(studentwilmo, 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 SmsResult()
                             {
                                    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 TotalScore;
            }
      }
}

Step 3

 

Drag and drop a grid view control with the following Bound Field.

<asp:GridView ID="gvStudents" 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>

Step 4

 

Call the function in the form of load events of the webpage and bind the result of that function with the grid view controls.

public void SelectStudentList(GridView gv)
{
      gv.DataSource = bel.SelectStudentList().Take(20);
      gv.DataBind();
      gv.EmptyDataText = "No Data Found.";
}

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
           SelectStudentList(gvStudents);
    }
}

Now we get output like:

 

 

Summary

 

This article showed how to join two tables of different databases or different contexts using Entity Framework (EF).

 

Thanks.

 

I would like to get feedback from my readers. Please post your feedback, question, or comments about this article.