locked
Related data into ViewModel RRS feed

  • Question

  • User-2008955570 posted

    Hi all,

    I've created a ViewModel, to get related data from 3 models:

    public class GezinUitleen
    {
    public Speelgoed Speelgoed { get; set; }
    public List<Uitleen> Uitleningen { get; set; }
    public List<Gezin> Gezin { get; set; }

    }
    the classes involved:

    public class Speelgoed
    {
    public int Id { get; set; }
    public string omschrijving { get; set; }
    public int? batterijId { get; set; }
    }

    public class Gezin
    {
    public int Id { get; set; }
    public string Naam { get; set; }
    }

    public class Uitleen
    {
    public int UitleenId { get; set; }
    public int speelgoedId { get; set; }
    public int lenerid { get; set; }
    [ForeignKey("fkspeelgoed")]
    public Speelgoed speelgoed { get; set; }
    }

    Controller:

    public ActionResult Details(int? id)
    {
    if (id == null)
    {
    return NotFound();
    }

    var gezin = _context.tblGezin
    .Where(m => m.Id == id).ToList();

    var viewModel = new GezinUitleen()

    {
    Gezin=gezin,
    Uitleningen = _context.TblUitleen
    .Where(uit => uit.lenerid == id).ToList(),
    Speelgoed= ???????????? this is where I am stuck....
    };
    return View("Details", viewModel);
    }


    I want to display the 'omschrijving' property of the Speelgoed class.
    How do I get that into my ViewModel ??

    Saturday, December 7, 2019 2:01 PM

Answers

  • User-854763662 posted

    Hi James Boelens ,

    From your explanation, each time a family borrows a toy, the borrowing contract is recorded once; of course, the same toy may be borrowed by different families. So the relationship between Speelgoed and Gezin  is many-to-many , your model design should be like below :

    1. Model and ViewModel , you could refer to Relationships in EF Core 

    public class Gezin
    {
          public int Id { get; set; }
          public string Naam { get; set; }
    
          public List<Uitleen> Uitleens { get; set; }
    }
    
    public class Speelgoed
    {
          public int Id { get; set; }
          public string omschrijving { get; set; }
          public int? batterijId { get; set; }
          public List<Uitleen> Uitleens { get; set; }
    
    }
    
    //you can represent a many-to-many relationship by including an entity class for the join table
    // and mapping two separate one-to-many relationships public class Uitleen { public int UitleenId { get; set; } public DateTime LenderTime { get; set; }
    public int lenerid { get; set; } [ForeignKey("lenerid")] public Gezin gezin { get; set; } public int speelgoedId { get; set; } [ForeignKey("speelgoedId")] public Speelgoed speelgoed { get; set; } }

    public class GezinUitleen
    {
    //public Speelgoed Speelgoed { get; set; }
    public List<Uitleen> Uitleningen { get; set; }
    public Gezin Gezin { get; set; }
    }

    2.DbContext

    public class MyDbContext:DbContext
        {
            public MyDbContext(DbContextOptions<MyDbContext> options):base(options)
            { }
    
            public DbSet<Gezin> tblGezin { get; set; }
            public DbSet<Uitleen> TblUitleen { get; set; }
            public DbSet<Speelgoed> TblSpeelgoed { get; set; }
    
            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
    
                modelBuilder.Entity<Uitleen>()
                    .HasOne(u => u.gezin)
                    .WithMany(g => g.Uitleens)
                    .HasForeignKey(u => u.lenderId);
    
                modelBuilder.Entity<Uitleen>()
                    .HasOne(u => u.speelgoed)
                    .WithMany(g => g.Uitleens)
                    .HasForeignKey(u => u.speelgoedId);
            }
        }

    3.Controller

    public ActionResult GezinUitleenDetails(int? id)
    {
                if (id == null)
                {
                    return NotFound();
                }
    
                var gezin = _context.tblGezin.SingleOrDefault(g=>g.Id==id);
    
                var viewModel = new GezinUitleen()
                {
                    Gezin = gezin,
                    Uitleningen = _context.TblUitleen.Include(uit=>uit.speelgoed).Where(uit => uit.lenderId == id).ToList()
                };
                return View( viewModel);
    }

    4.View

    @model MVC3_0.Models.TestViewModel.GezinUitleen
    
    <h1>GezinUitleenDetails</h1>
    
    <div>
        <dl class="row">
            <dt class="col-sm-2">
                @Html.DisplayNameFor(model => model.Gezin.Naam)
            </dt>
            <dd class="col-sm-10">
                @Html.DisplayFor(model => model.Gezin.Naam)
            </dd>
        </dl>
        <table>
            <thead>
                <tr>
                    <th>UitleenId</th>
                    <th>ToyName</th>
                    <th>Lender Date</th>
                </tr>
            </thead>
            <tbody>
                @foreach (var item in Model.Uitleningen)
                {
                    <tr>
                        <td>@item.UitleenId</td>
                        <td>@item.speelgoed.omschrijving</td>
                        <td>@item.LenderTime</td>
                        <td></td>
                    </tr>
                }
            </tbody>
        </table>
    </div>

    5.Result

    Best Regards ,

    Sherry

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 11, 2019 2:58 AM

All replies

  • User1120430333 posted

    Below, you will need to use a Linq projection and project into the Speelgoed object, which is a custom type..

    Uitleningen = _context.TblUitleen
    .Where(uit => uit.lenerid == id).ToList(),
    Speelgoed= ???????????? this is where I am stuck....
    };

    https://csharp-station.com/Tutorial/Linq/Lesson02

    Saturday, December 7, 2019 8:17 PM
  • User-854763662 posted

    Hi James Boelens ,

    From your model design , there are the follwoing  relationships between three models

    Gezin and  Uitleningen : one-to-many 

    Speelgoed and  Uitleningen : one-to-many 

    Try the following code:

    var viewModel = new GezinUitleen()
    {
        Gezin = gezin,
        Uitleningen = _context.TblUitleen.Where(uit => uit.lenerid == id).ToList(),
        Speelgoed = _context.TblUitleen.Include(u => u.speelgoed).Where(uit => uit.lenerid == id).FirstOrDefault().speelgoed
    };

    Best Regards ,

    Sherry

    Monday, December 9, 2019 3:56 AM
  • User-2008955570 posted

    Thanks Sherry for your valued input!

    However, using this code, and trying some changes, the Speelgoed property of my GezinUitleen object remains NULL

    Any suggestions?

    thanks,

    James

    Oh, and for the record, there are records in the table.

    But right now, my view shows the SpeelgoedID, where I want to see the omschrijving (description)

    Monday, December 9, 2019 8:37 AM
  • User-854763662 posted

    Hi James Boelens , 

    However, using this code, and trying some changes, the Speelgoed property of my GezinUitleen object remains NULL

    Show the code  you tried if you need the community review and code debug.

    Best Regards ,

    Sherry

    Monday, December 9, 2019 8:41 AM
  • User-2008955570 posted

    Hi,

    I am able to get the results I want with this SQL:

    SELECT TblUitleen.UitleenId, tblGezin.familieNaamVader, TblUitleen.lenerid, TblUitleen.speelgoedId, tblSpeelgoed.omschrijving
    FROM TblUitleen INNER JOIN
    tblGezin ON TblUitleen.lenerid = tblGezin.Id INNER JOIN
    tblSpeelgoed ON TblUitleen.speelgoedId = tblSpeelgoed.Id
    where TblUitleen.lenerid=1

    In my view, I use this to display the contents:

    @foreach (var item in Model.Uitleningen)
        {
            <tr>
                <td>@item.UitleenId</td>
                <td>@item.speelgoed.omschrijving</td>
                <td>@item.opmerkingen</td>
                <td></td>
                <td></td>
            </tr>
        }
    but because the Speelgoed part is null, I get 

    NullReferenceException: Object reference not set to an instance of an object.

    AspNetCore.Views_Gezin_Details.ExecuteAsync() in Details.cshtml, line 89

    And that makes sense, of course..

    Monday, December 9, 2019 8:57 AM
  • User-2008955570 posted

    I know it's a workaround, and I would prefer continuing the chosen path, but I got it working like this:

    public ActionResult Details(int? id)
            {
                if (id == null)
                {
                    return NotFound();
                }
    
                var gezin = _context.tblGezin
                    .Where(m => m.Id == id).ToList();
                var uitleen = _context.TblUitleen.Where(uit => uit.lenerid == id).ToList();
                List<Speelgoed> myList= new List<Speelgoed>();
                foreach (var item in uitleen)
                {
                    Speelgoed toy = new Speelgoed();
                    var desc = _context.tblSpeelgoed.Where(t => t.Id == item.speelgoedId).Single();
                    toy.omschrijving = desc.omschrijving;
                    toy.Id = desc.Id;
                    toy.uitleenID = item.UitleenId;
                    myList.Add(toy);
                }
    
                var viewModel = new GezinUitleen()
                {
                    Gezin = gezin,
                    Uitleningen = _context.TblUitleen.Where(uit => uit.lenerid == id).ToList(),
                    ListSpeelgoed =myList
                };
                return View("Details", viewModel);
            }

    I am still open for suggestions.

    thanks to all who contributed. I will leave the question open for a few more days before marking an accepted answer.

    James

    Monday, December 9, 2019 10:13 AM
  • User-854763662 posted

    Hi James Boelens ,

    Can you elaborate on the relationship between the models Speelgoed, Gezin, and Uitleen?  

    What is the type of Speelgoed property in the ViewModel Speelgoed  or List<Speelgoed >?

    Best Regards ,

    Sherry

    Tuesday, December 10, 2019 2:06 AM
  • User-2008955570 posted

    Hi Sherry,

    you have correctly spotted the fact that Speelgoed is now a List<Speelgoed> At first, it was just a single Speelgoed object.

    The relationship is probably easier to understand when I translate it:

    Speelgoed= toy

    Gezin = family

    Uitleen = borrowing (like a book in the local library)

    So: a family borrows a toy. That borrowing 'contract' is an Uitleen item

    My view now shows the family data (from the Gezin part in the Model)

    and the Uitleen data (date of borrowing etc etc...)

    I had to include the Speelgoed objects because I wanted to display the Speelgoed name instead of just the Speelgoed ID

    Does this make more sense to you?

    thanks,

    James

    Tuesday, December 10, 2019 12:40 PM
  • User-854763662 posted

    Hi James Boelens ,

    From your explanation, each time a family borrows a toy, the borrowing contract is recorded once; of course, the same toy may be borrowed by different families. So the relationship between Speelgoed and Gezin  is many-to-many , your model design should be like below :

    1. Model and ViewModel , you could refer to Relationships in EF Core 

    public class Gezin
    {
          public int Id { get; set; }
          public string Naam { get; set; }
    
          public List<Uitleen> Uitleens { get; set; }
    }
    
    public class Speelgoed
    {
          public int Id { get; set; }
          public string omschrijving { get; set; }
          public int? batterijId { get; set; }
          public List<Uitleen> Uitleens { get; set; }
    
    }
    
    //you can represent a many-to-many relationship by including an entity class for the join table
    // and mapping two separate one-to-many relationships public class Uitleen { public int UitleenId { get; set; } public DateTime LenderTime { get; set; }
    public int lenerid { get; set; } [ForeignKey("lenerid")] public Gezin gezin { get; set; } public int speelgoedId { get; set; } [ForeignKey("speelgoedId")] public Speelgoed speelgoed { get; set; } }

    public class GezinUitleen
    {
    //public Speelgoed Speelgoed { get; set; }
    public List<Uitleen> Uitleningen { get; set; }
    public Gezin Gezin { get; set; }
    }

    2.DbContext

    public class MyDbContext:DbContext
        {
            public MyDbContext(DbContextOptions<MyDbContext> options):base(options)
            { }
    
            public DbSet<Gezin> tblGezin { get; set; }
            public DbSet<Uitleen> TblUitleen { get; set; }
            public DbSet<Speelgoed> TblSpeelgoed { get; set; }
    
            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
    
                modelBuilder.Entity<Uitleen>()
                    .HasOne(u => u.gezin)
                    .WithMany(g => g.Uitleens)
                    .HasForeignKey(u => u.lenderId);
    
                modelBuilder.Entity<Uitleen>()
                    .HasOne(u => u.speelgoed)
                    .WithMany(g => g.Uitleens)
                    .HasForeignKey(u => u.speelgoedId);
            }
        }

    3.Controller

    public ActionResult GezinUitleenDetails(int? id)
    {
                if (id == null)
                {
                    return NotFound();
                }
    
                var gezin = _context.tblGezin.SingleOrDefault(g=>g.Id==id);
    
                var viewModel = new GezinUitleen()
                {
                    Gezin = gezin,
                    Uitleningen = _context.TblUitleen.Include(uit=>uit.speelgoed).Where(uit => uit.lenderId == id).ToList()
                };
                return View( viewModel);
    }

    4.View

    @model MVC3_0.Models.TestViewModel.GezinUitleen
    
    <h1>GezinUitleenDetails</h1>
    
    <div>
        <dl class="row">
            <dt class="col-sm-2">
                @Html.DisplayNameFor(model => model.Gezin.Naam)
            </dt>
            <dd class="col-sm-10">
                @Html.DisplayFor(model => model.Gezin.Naam)
            </dd>
        </dl>
        <table>
            <thead>
                <tr>
                    <th>UitleenId</th>
                    <th>ToyName</th>
                    <th>Lender Date</th>
                </tr>
            </thead>
            <tbody>
                @foreach (var item in Model.Uitleningen)
                {
                    <tr>
                        <td>@item.UitleenId</td>
                        <td>@item.speelgoed.omschrijving</td>
                        <td>@item.LenderTime</td>
                        <td></td>
                    </tr>
                }
            </tbody>
        </table>
    </div>

    5.Result

    Best Regards ,

    Sherry

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 11, 2019 2:58 AM
  • User-2008955570 posted

    Hi Sherry,

    thank you for your efforts to help me with this issue.

    I am still in the process of trying to fully understand what you have constructed.

    A very big 'thank you' from Belgium !

    James

    Thursday, December 12, 2019 12:05 PM