Linq for Entity Join Query – Entity Framework

The Problem

You have a parent/child table structure in your database that looks like the table structure outlined below.

You have used the EntityFramework or Linq to SQL to build your data access layer.

Now you want to write a method using linq as follows:

public List<Child> GetChildren(string parentName)

The database

Create Table Parent (
  Id int identity(1,1) NOT NULL,
  ParentName varchar(50) NOT NULL,
  CONSTRAINT PK_Parent Primary Key Clustered (
    Id Asc

Create Table Child (
   Id int identity(1,1) NOT NULL,
   ParentId int NOT NULL,
   ChildName varchar(50) NOT NULL,
   CONSTRAINT PK_Child Primary Key Clustered (
    Id Asc

Obviously I would have foreign keys between parent and child.. you get the idea, this is your basic run of the mill one to many, parent child relationship.


To get a list of all the children in sql your query would look something like:

select c.*
from Parent p 
    inner join Child c on c.ParentId = p.Id
Where p.ParentName = @searchString -- where @searchString is a variable 

The Method

I looked around the web to find a simple example of how to do this and I could not find one, that explained how to do an easy join syntax between two tables.

After much trial and error I came up with this:

public List<Child> GetChildren(string parentName)
    List<Child> children = null;

    using (FrameworkDb dbContext = new FrameworkDb())
        children = (from c in dbContext.Children
                    join p in dbContext.Parent.Where(p => p.ParentName == parentName)
                    on new { PID = c.ParentId } equals new { PID = p.Id }
                    select c).ToList();

    return children;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.