Manu Manu - 1 month ago 8
C# Question

How to join two tables with Linq in an MVC controller

I have two sql database tables with a 1:n relationship. For my ASP.NET MVC-solution I have enabled EF-code-first-migration and the proper DBContext and classes established.

I would like to write an MVC-controller that joins both tables in order to select specific records for display in a view.

Here are the two classes:

public class Tbl_Group_Communities : Entity
{
public string GKZ { get; set; }
public int G_ID { get; set; }
}

public class Katastralgemeinden : Entity
{
public string KGNr { get; set; }
public string KGName { get; set; }
public string GKZ { get; set; }
public string GemeindeName { get; set; }
}


So far I have been able to come up with a working controller for the tables by themselves but not joined. Below the working controller for the first class:

public IEnumerable<Tbl_Group_Communities> Get()
{
var entities = UnitOfWork.GetAll<Tbl_Group_Communities>().ToList();
return entities;
}


I think, the join can be done with Linq but I have no idea how/where to start.
The common key for both tables is GKZ; so the join should be established via GKZ. And then I need to select specific records from the joined records where G_ID = a certain value.

If someone could give me some help, I'd be very thankful.
Manu

Answer

You can do inner join as shown below.

Assumption : Hope your table names are like Tbl_Group_Communities and Katastralgemeinden.In other words same name as the class names.

from s in db.Tbl_Group_Communities
join sa in db.Katastralgemeinden on s.GKZ equals sa.GKZ
where s.G_ID == 1
select s

You can learn more about join here : Join Operators

Comments