CODEWITHSUNDEEP
c#sql-serverentity-frameworklinq
Manu
Manu

Reputation: 1470

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

Upvotes: 2

Views: 25424

Answers (2)

Manu
Manu

Reputation: 1470

I figured it out - here is my controller that works:

using System.Linq;
using System.Web.Http;
using System.Web.Http.OData.Query;
using IndireKat.Data.Contracts;
using IndireKat.Data.Contracts.Entities;
using IndireKat.Shared.Framework.Security;

namespace Presentation.Host.Controllers
{
    public class KatastralgemeindenController : BaseODataController
    {
        private readonly IIdentityStorage identityStorage;

        public KatastralgemeindenController(IUnitOfWork unitOfWork, IIdentityStorage identityStorage)
        {
            UnitOfWork = unitOfWork;
            this.identityStorage = identityStorage;
        }

        [Queryable(AllowedQueryOptions = AllowedQueryOptions.All)]
        public IQueryable<Katastralgemeinden> Get()
        {
            IIndireKatPrincipal indireKatPrincipal = identityStorage.GetPrincipal();

            var comunityIds = UnitOfWork.GetAll<UserGroup>()
                .Where(group => group.Id == indireKatPrincipal.GroupId)
                .SelectMany(group => group.Tbl_Group_Communities).Select(comunity => comunity.GKZ).ToList();

            IQueryable<Katastralgemeinden> result = null;

            if (comunityIds.Any())
            {
                result = UnitOfWork.GetAll<Katastralgemeinden>().Where(company => comunityIds.Contains(company.GKZ));
            }

            return result;
        }
    }
}

Regards, Manu

Upvotes: 0

Sampath
Sampath

Reputation: 65860

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

Upvotes: 3

Related Questions