WPF MVVM how to show data from many tables in one view

Igor Boras 41 Reputation points
2020-12-04T17:47:30.4+00:00

Hi,

I think this is very common scenario when you have relational database and classes witch represent each table as class (as is model)

  public class Role : AppEntityBase  
    {  
        public int RoleId { get; set; }  
        public string RoleName { get; set; }  
  
    }public class Person : AppEntityBase  
    {  
        public int PersonId { get; set; }   
        public string FirstName { get; set; }   
        public string LastName { get; set; }  
        //Foreign key  
        public int RoleId { get; set; }  
    }  
 public class PersonDetail : AppEntityBase  
    {  
        public int PersonDetailId { get; set; }  
        public int PersonId { get; set; }//FOREIGN KEY TO TABLE PERSON  
        public string Detail_1 { get; set; }  
        public string Detail_2 { get; set; }  
        public string Detail_3 { get; set; }  
        public string Detail_4 { get; set; }  
    }  
 public class AppEntityBase : SqlServerEntityBase   
    {  
        public DateTime CreateDateTime { get; set; }   
        public string CreateBy { get; set; }  
        public DateTime? UpdateDateTime { get; set; }  
        public string UpdateBy { get; set; }  
        public DateTime ValidFromDate { get; set; }  
        public DateTime? ValidToDate { get; set; }   
    }  

What is the best approach to show all data in single view?
45304-image.png
Write query with joining all 3 table Role Person PersonDetail is not option bcz of using reflection when colling sql database.

Windows Presentation Foundation
Windows Presentation Foundation
A part of the .NET Framework that provides a unified programming model for building line-of-business desktop applications on Windows.
2,670 questions
XAML
XAML
A language based on Extensible Markup Language (XML) that enables developers to specify a hierarchy of objects with a set of properties and logic.
762 questions
0 comments No comments
{count} votes

Accepted answer
  1. Peter Fleischer (former MVP) 19,231 Reputation points
    2020-12-04T19:28:56.287+00:00

    Hi Igor,
    you can extend Person class via partial keyword to add property with reference to PersonDetail.

    Try following demo:

    XAML:

    <Window x:Class="WpfApp1.Window018"  
            xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"  
            xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"  
            xmlns:d="http://schemas.microsoft.com/expression/blend/2008"  
            xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"  
            xmlns:local="clr-namespace:WpfApp018"  
            mc:Ignorable="d"  
            Title="Window018" Height="450" Width="800">  
      <Window.Resources>  
        <local:ViewModel x:Key="vm"/>  
      </Window.Resources>  
      <Grid DataContext="{StaticResource vm}">  
        <DataGrid ItemsSource="{Binding Persons}" AutoGenerateColumns="false">  
          <DataGrid.Columns>  
            <DataGridTextColumn Header="ID" Binding="{Binding PersonId}"/>  
            <DataGridTextColumn Header="Firstname" Binding="{Binding FirstName}"/>  
            <DataGridComboBoxColumn Header="Role name"   
                                    IsReadOnly="True"  
                                    ItemsSource="{Binding Roles, Source={StaticResource vm}}"  
                                    SelectedValueBinding="{Binding RoleId}"  
                                    SelectedValuePath="RoleId"  
                                    DisplayMemberPath="RoleName"/>  
            <DataGridTextColumn Header="Detail 1" Binding="{Binding Detail.Detail_1}"/>  
          </DataGrid.Columns>  
        </DataGrid>  
      </Grid>  
    </Window>  
    

    Code:

    using System;  
    using System.Collections.Generic;  
    using System.Linq;  
    using System.Windows;  
      
    namespace WpfApp018  
    {  
      public class ViewModel  
      {  
        public ViewModel()  
        {  
          Random rnd = new Random();  
          for (int i = 1; i < 10; i++) colPersons.Add(new Person() { PersonId = i, FirstName = $"First name {i}", RoleId = rnd.Next(1, 5) });  
          colRoles.Add(new Role() { RoleId = 1, RoleName = "Admin" });  
          colRoles.Add(new Role() { RoleId = 2, RoleName = "User" });  
          colRoles.Add(new Role() { RoleId = 3, RoleName = "Member" });  
          colRoles.Add(new Role() { RoleId = 4, RoleName = "Owner" });  
          for (int i = 1; i < 100; i++) colPersonDetails.Add(new PersonDetail() { PersonDetailId = i, PersonId = rnd.Next(1, 10), Detail_1 = $"Detail {i}" });  
          //  
          // connect data items  
          foreach (var person in colPersons)  
          {  
            var req = from det in colPersonDetails where det.PersonId == person.PersonId orderby det.PersonDetailId descending select det;  
            if (req.Count() > 0) person.Detail = req.First();  
          }  
        }  
        private List<Person> colPersons = new List<Person>();  
        private List<Role> colRoles = new List<Role>();  
        private List<PersonDetail> colPersonDetails = new List<PersonDetail>();  
        public List<Person> Persons { get => colPersons; }  
        public List<Role> Roles { get => colRoles; }  
      }  
      
      public partial class Person  
      {  
        public PersonDetail Detail { get; set; }  
      }  
      
      public partial class Person  
      {  
        public int PersonId { get; set; }  
        public string FirstName { get; set; }  
      
        public int RoleId { get; set; }  
      }  
      public class Role  
      {  
        public int RoleId { get; set; }  
        public string RoleName { get; set; }  
      }  
      public class PersonDetail  
      {  
        public int PersonDetailId { get; set; }  
        public int PersonId { get; set; }//FOREIGN KEY TO TABLE PERSON  
        public string Detail_1 { get; set; }  
      }  
    }  
    

    Result:

    45383-x.png

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful