How to pass a list (sql join) to view in c#?

Question

In my controller I return a list which is a result of joining 2 tables. Now I want to pass this list to my view but can't do this. Such error occurs:

System.Collections.Generic.List1[<>f__AnonymousType2 [System.String,System.String,System.String,... etc etc], while this dictionary needs element of type System.Collections.Generic.IEnumerable[XYZ.Models.MyModelTable].

        public ActionResult someAction()
        {
            BASEdb db = new BASEdb();

            var model = db.MyModelTable
                            .Join(db.Customers,
                            z => z.FOPKTO_, k => k.ID,
                            (z, k) => new { FOPKTO = z, Customers = k })
                            .Where(w => w.FOPKTO.Date == null)
                            .Select(s => new
                                     {
                                      s.Customers.Name,
                                      s.MyModelTable.FOPKTO,
                                      s.MyModelTable.FOPBDA,
                                      s.MyModelTable.FOPGSS,

                                      })
                             .ToList();

            return View(model);
        }

and my View:

@model IEnumerable<XYZ.Models.MyModelTable>


    @{
        ViewBag.Title = "Invoices";
        Layout = "~/Views/Shared/_Layout.cshtml";
    }

    <h2>Invoices</h2>


    <table class="table">
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.FOPBDA)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.FOPLBN)

.... etc. (there's foraeach instruction below as well)

How can I pass list which return data from 2 tables to View? my view now takes type of one table (MyModelTable)? It shoudl be more generic type I think but have no idea how to get around it

Create a class with properties for the joined data. – itsme86 Sep 17 '20 at 15:01 — itsme86, Sep 17 '20 at 15:01

Jonathan · Accepted Answer · 2020-09-17T15:15:01.097

You should create a ViewModel for your view. This will be a data structure that is specifically used by your view, and can be a structure that is a combination of your two other models.

Lots of information on the web, here's an example from StackOverflow:

What is ViewModel in MVC?

The way you're doing it, you are not creating a IEnumerable<XYZ.Models.MyModelTable> (the type that is expected on your view), you are creating a list of anonymous type.

Let's call it MyViewModel. You would declare it like:

public class MyViewModel(){
    public string Name {get;set;}
    public int FOPKTO {get;set;}
    public int FOPBDA {get;set;}
    public int FOPGSS {get;set;}
}

And then in your controller, build it up like:

        var model = db.MyModelTable
                        .Join(db.Customers,
                        z => z.FOPKTO_, k => k.ID,
                        (z, k) => new { FOPKTO = z, Customers = k })
                        .Where(w => w.FOPKTO.Date == null)
                        .Select(s => new MyViewModel
                                 {
                                  Name = s.Name,
                                  FOPKTO = s.FOPKTO,
                                  FOPBDA = s.FOPBDA,
                                  FOPGSS = s.FOPGSS
                                  })
                         .ToList();

        return View(model);

And then update your view to use this viewmodel instead of the previous model.

This is the essence of MVC: creating a Model for your View and passing it from your Controller. It's entirely okay to do it. — Captain Kenpachi, Sep 17 '20 at 15:18
Ok, I did that but when I try to compile I got an error: "The cast to value type 'System.Decimal' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type." How can I get around it? — Muska, Sep 17 '20 at 15:48
make the property nullable (if that's appropriate) or set a default value (say '0') if appropriate — Jonathan, Sep 17 '20 at 15:49

score 1 · Answer 2 · answered Sep 17 '20 at 15:01

This is a guess, since you didn't show your MyModelTable class, but you probably need to do something like:

var models = db.MyModelTable
                .Join(db.Customers,
                z => z.FOPKTO_, k => k.ID,
                (z, k) => new { FOPKTO = z, Customers = k })
                .Where(w => w.FOPKTO.Date == null)
                .Select(s => new MyModelTable
                        {
                          Name = s.Customers.Name,
                          FOPKTO = s.MyModelTable.FOPKTO,
                          FOPBDA = s.MyModelTable.FOPBDA,
                          FOPGSS = s.MyModelTable.FOPGSS,
                        })
                 .ToList();

score 1 · Answer 3 · answered Sep 17 '20 at 15:09

Create a ViewModel type like MyViewModel.

public class MyViewModel(){
    public string Name { get; set; }
    public int FOPKTO { get; set; }
    public int FOPBDA { get; set; }
    public int FOPGSS { get; set; }
}

And select it to list.

BASEdb db = new BASEdb();

            var model = db.MyModelTable
                            .Join(db.Customers,
                            z => z.FOPKTO_, k => k.ID,
                            (z, k) => new { FOPKTO = z, Customers = k })
                            .Where(w => w.FOPKTO.Date == null)
                            .Select(s => new MyViewModel
                                     {
                                      Name = s.Customers.Name,
                                      FOPKTO = s.MyModelTable.FOPKTO,
                                      FOPBDA = s.MyModelTable.FOPBDA,
                                      FOPGSS = s.MyModelTable.FOPGSS
                                      })
                             .ToList();

            return View(model);

And change your Views model type to MyViewModel

@model IEnumerable<MyViewModel>


   ... etc

You are wellcome :) – Bahtiyar Özdere Sep 17 '20 at 16:29 — Bahtiyar Özdere, Sep 17 '20 at 16:29

score 1 · Answer 4 · answered Sep 17 '20 at 18:13

1

In-short, create a model class. Define att the properties in that model. fill the instance of that model class in your controller-action and return to your view ... simple.

Please dont forget to make this as answer :) thanks

answered Sep 17 '20 at 18:13

Imran

254
3
14

How to pass a list (sql join) to view in c#?

4 Answers4