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:

[csharp]
public List<Child> GetChildren(string parentName)
[/csharp]

The database

[sql]
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
)
)
[/sql]

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.

In SQL

To get a list of all the children in sql your query would look something like:
[sql]
select c.*
from Parent p
inner join Child c on c.ParentId = p.Id
Where p.ParentName = @searchString — where @searchString is a variable
[/sql]

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:

[csharp]

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;
}

[/csharp]