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.

1 Answers

1
rad On Best Solutions

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.