Entity Framewok: Join Tables Example C#

First things first: you should make good use of the navigation properties whenever possible.

The Join Query tool could be your friend if you don’t have any navigation properties but you still need to join two tables (or more).

 

Starting Point

In this tutorial we will use the simple database created in theĀ Entity Framework Database First Approach, but without using the navigation properties.

robot edmx

Syntax

The first thing you have to know is the syntax:

SELECT [column/columns]
FROM [table1-name]
JOIN [table2-name]
ON [table1-column] = [table2-column]

Join Query

Suppose we want to join the RobotDog and RobotFactory tables, to find the robot dogs produced in the Texan military base.
The Equals keyword is our ally in this mission.

We join the two tables on the RobotFactoryId, taking only the beasts whom factory is in Texas.

using (RobotIndustryEntities context = new RobotIndustryEntities())
{
    var robotDogs = (from d in context.RobotDogs
                     join f in context.RobotFactories
                     on d.RobotFactoryId equals f.RobotFactoryId
                     where f.Location == "Texas"
                     select d).ToList();
}

Or we can use the Join method

var robotDogs = context.RobotDogs
    .Join(
        context.RobotFactories.Where(x => x.Location == "Texas"),
        d => d.RobotFactoryId,
        f => f.RobotFactoryId,
        (d, f) => d)
    .ToList();

Select columns to show

You can also choose which column to show after the join. Consider the previous code:

var robotDogs = (from d in context.RobotDogs
     join f in context.RobotFactories
     on d.RobotFactoryId equals f.RobotFactoryId
     where f.Location == "Texas"
     select d).ToList();

You can change that select d in:

select new {
    Location = f.Location,
    RobotName = d.Name,
    IsArmed = d.Armed
}

Multiple Tables

Not much changes if we want to join multiple tables.

Consider a database with three tables:

join multiple tables

We want to find all the people that has dogs from the Texan factory:

var robotOwners = (from o in context.RobotOwners
     join d in context.RobotDogs
     on d.DogOwnerId equals o.DogOwnerId
     join f in context.RobotFactories
     on f.RobotFactoryId equals d.RobotFactoryId
     where f.Location == "Texas"
     select o).ToList();