How do I access columns from joined tables using Linq, C# in asp.net MVC

Question

I need to join two tables in my controller and pass the result to my view. Trouble is, I cannot seem to be able to access the column values from the joined table once I'm in the view.

I've tried to do a join in my controller, but I have a feeling I'm going in the wrong direction.

Controller:

//GET: kurser/stats/5
    public ActionResult Stats()
    {
        var stat = from k in db.kursister
                   join ku in db.kurser
                   on k.kursus_id equals ku.kursus_id
                   select k;

        return View(stat);
    }

My view:

@model IEnumerable<Itucation.Models.kursister>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<div>
   @{ 
        foreach (var item in Model)
        {
            @Html.DisplayFor(modelItem => item.kursus_navn)<br/>
        }
    }
</div>

I would expect to be able to get the value of kursus_navn, which is in the kursus table, which is what I'm trying to join with the kursister table. But it doesn't work.

Looks like in your linq query, you are only selecting the k object. Currently you'll get all kursister records that have a matching kursus_id in kurser, but no data from the kurser table. — Nick
– Nick, Commented Apr 4, 2019 at 20:22
because you just select k and you can access only property of kursister model, so you can not access kurser because you have not select this property — Lalji Dhameliya
– Lalji Dhameliya, Commented Apr 5, 2019 at 4:40

Amir Molaei · Accepted Answer · 2019-04-05 14:07:18Z

1

You are only selecting the columns of db.kursister after joining the two tables. In order to select all columns from both db.kursus and db.kursister you need to change your select statement into this:

var stat = from k in db.kursister
                   join ku in db.kursus
                   on k.kursus_id equals ku.kursus_id
                   select new KursisterusViewModel {k = k, ku = ku};

Then in view:

<div>
   @{ 
        foreach (var item in Model)
        {
            @Html.DisplayFor(modelItem => item.ku.kursus_navn)<br/>
        }
    }
</div>

Update
Since anonymous type is not supported in a razor page, you need to create a new class like KursisterusViewModel to wrap your data into that:

public class KursisterusViewModel
{
    public kursister k { get; set; }
    public kursus ku { get; set; }
}

Then change the first line of the razor page into this:

@model IEnumerable<Itucation.ViewModels.KursisterusViewModel>

Assuming that KursisterusViewModel class is in ViewModels folder.
Note that Select statement was updated too.

edited Apr 5, 2019 at 14:07

answered Apr 4, 2019 at 20:31

Amir Molaei

3,8601 gold badge20 silver badges22 bronze badges

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

6 Comments

Lasserh Over a year ago

thank you so much for answering. I've edited my code according to your answer, but now i get this error: 'kurser' does not contain a definition for 'ku' and no extension method 'ku' accepting a first argument of type 'kurser' could be found (are you missing a using directive or an assembly reference?)

Amir Molaei Over a year ago

@Lasserh The reason is because anonymous types are not supported in razor pages. I updated the answer. Let me know if you still have any issues.

Lasserh Over a year ago

Hi @rad, I'm making progress thanks to your help. However, I still get an error. this time it's The model item passed into the dictionary is of type 'System.Collections.Generic.List1[<>f__AnonymousType43[Itucation.Models.kursister,Itucation.Models.kursus_kursist,Itucation.Models.kurser]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[Itucation.Models.KursisterusViewModel]' I've tried using return View(stat.ToList()); - but apparently that's not it. Do you have any more suggestions up your sleeve?

Amir Molaei Over a year ago

Did you apply all the edit that i made? for example did you modify the select statement to select new KursisterusViewModel {k = k, ku = ku}; ?

Lasserh Over a year ago

You're right, of course. After I read your updated answer properly and updated my code accordingly - everything works beautifully. thank you very, very much.

|

Collectives™ on Stack Overflow

How do I access columns from joined tables using Linq, C# in asp.net MVC

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related