how do i select from multiple table and return the result to the view mvc 5

Question

i am trying to display the result from this query in my index view, but it is reporting error, i have tried to debug it, no success, i will appreciate if anyone could assist in letting me know where i have gone wrong.

    public ActionResult Index()
    {           
       var users =
        (from u in db.Users
                     join s in db.States
                         on u.StateId
                         equals s.StateId

                     join l in db.Lgas
                         on u.LgaId
                         equals l.LgaId
                     select new
                     {
                         u.Surname,
                         u.Firstname,
                         u.Othernames,
                         u.Email,
                         s.StateName,
                         l.LgaName
                     }).ToList();


        return View(users);
    }

teo van kot · Accepted Answer · 2020-01-31T15:43:55.630

1

You cant pass anonymous object to View because every property of anonymous object marked internal and Views are in a different namespace. Here is great explanation.

Use ViewModel - it's the best solution.

ViewModel example:

public class UserViewModel
{
   public string Surname { get; set; }
   public string Firstname { get; set; }
   public string Othernames { get; set; }
   public string Email { get; set; }
   public string StateName { get; set; }
   public string LgaName { get; set; }
}

Your Controller:

public ActionResult Index()
{           
   var users =
    (from u in db.Users
                 join s in db.States
                     on u.StateId
                     equals s.StateId

                 join l in db.Lgas
                     on u.LgaId
                     equals l.LgaId
                 select new UserViewModel
                 {
                    Surname =  u.Surname,
                    Firstname = u.Firstname,
                    Othernames = u.Othernames,
                    Email = u.Email,
                    StateName = s.StateName,
                    LgaName = l.LgaName
                 }).ToList();


    return View(users);
}

And at the begin of your View:

@model List<YourViewModelNamespase.UserViewModel>

Change YourViewModelNamespase to actual namespace of your UserViewModel class.

On your View, you can do with your data anything you want everything in Model property.

edited Jan 31 '20 at 15:43

answered Oct 29 '15 at 11:58

teo van kot

12,350
10
38
70

the statename and lga name are not in the same table, with other property, i am selecting fro three different tables, and also how do i use the Viewmodel in the controller? – UwakPeter Oct 29 '15 at 12:10
@UwakMfon What's wrong that they are in different tables? You already join them with your linq statement. I already write your controller for you. check `select new UserViewModel` that's where you use ViewModel. You just create instanses of `UserViewModel` not anonymouse type. – teo van kot Oct 29 '15 at 12:13
Thanks, but this line is reporting error: @Html.DisplayNameFor(model => model.Surname) – UwakPeter Oct 29 '15 at 12:31
@UwakMfon note that you have `List` of objects and i see you try to access properties like it's only one object. – teo van kot Oct 29 '15 at 12:34
It works, Thanks. But i had to change @modelList to @model IEnumerable – UwakPeter Oct 29 '15 at 13:07

how do i select from multiple table and return the result to the view mvc 5

1 Answers1