How to join two tables with Linq in an MVC controller

Question

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

I'm sure somebody can show you how to do the join, but I would advise you research on how to configure code first relationships. If you setup the proper models with navigation properties it is pretty simple. I would advise looking into ViewModels. — Steve Greene
– Steve Greene, Commented Oct 29, 2016 at 15:01
Do you mean you can't find any documentation on how to write the Join statement? Apart from that, it's not clear what UnitOfWork can produce. Does it also provide Katastralgemeinden? And if so, what does GetAll return? If it's IEnumerable, joining isn't efficient at all. — Gert Arnold
– Gert Arnold, Commented Oct 29, 2016 at 15:15
I could find documentation, but since I am pretty new with C# and MVC-controllers the docus were more confusing than helping. UnitOfWork does also provide Katastralgemeinden. I thought GetAll does return all records, which was just a check that for one table the controller does work. What would be more efficient than IEnumerable? — Manu
– Manu, Commented Oct 29, 2016 at 15:39
If you join two IEnumerables, the data of both tables will be fetched into memory by two queries (that probably return lots of data) and then joined in memory. When you join two IQueryable (coming from the same context), the join will be translated into one efficient SQL query. — Gert Arnold
– Gert Arnold, Commented Oct 29, 2016 at 17:18

Sampath · Accepted Answer · 2016-10-29 15:27:52Z

3

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

answered Oct 29, 2016 at 15:27

Sampath

66.2k70 gold badges327 silver badges461 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Manu · Accepted Answer · 2016-10-31 08:19:54Z

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

Collectives™ on Stack Overflow

How to join two tables with Linq in an MVC controller

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related