How can I display multiple tables into a datagrid using binding?

Question

I have 3 tables:

--------------
| Cardealer  |
--------------
| dID | name |
--------------

-----------------------------------
| Garage                          |
-----------------------------------
| gID | gname | dealerID (FK:dID) |
-----------------------------------

-----------------------------------------
| Repair                                |
-----------------------------------------
| rID | date | cost | garageID (FK:gID) |
-----------------------------------------

These 3 tables are all linked with eachother in some way. In C# I made a datagrid, which has to be filled like this:

--------------------------------------------------
| rID | date | cost | Garagename | Cardealername |
--------------------------------------------------

I want to do this using Binding. This is my XAML code:

<DataGrid.Columns>
            <DataGridTextColumn Binding="{Binding rID}" ClipboardContentBinding="{x:Null}" Header="rID"/>
            <DataGridTextColumn Binding="{Binding date}" ClipboardContentBinding="{x:Null}" Header="Date"/>
            <DataGridTextColumn Binding="{Binding cost}" ClipboardContentBinding="{x:Null}" Header="Costs"/>
            <DataGridTextColumn Binding="{Binding Garage.gname}" ClipboardContentBinding="{x:Null}" Header="Garagename"/>
        </DataGrid.Columns>

This gives me:

----------------------------------
| rID | date | cost | Garagename |
----------------------------------

I just linked 1 foreign table to the datagrid, but i need 1 more table to be added to the datagrid. Does anyone know a way to do this?

If there is a different way, let me know.

Answer 1

Short Answer: Using Linq query is one way: preparing the result table and binding it to a datagrid.

    Table = from repair in repairs
            join garage in garages on repair.garageID equals garage.gID
            join dealer in dealers on garage.dealerID equals dealer.iID
            select new 
            { 
                ID = repair.rID, 
                Date = repair.date, 
                Cost = repair.cost, 
                GarageName = garage.gname, 
                DealerName = dealer.name
            };

Detailed Answer:

System: Windows 10, WPF app, .Net FrameWork 4.7.2, Visual Studio 2019

I created some classes to mock your tables

class CarDealer
{
    public int iID { get; set; }
    public string name { get; set; }
}

class Garage
{
    public int gID { get; set; }
    public string gname { get; set; }
    public int dealerID { get; set; }
}

class Repair
{
    public int rID { get; set; }
    public DateTime date { get; set; }
    public double cost { get; set; }
    public int garageID { get; set; }
}

The view model class is something like this:

class ViewModel
{
    // This is the result table called from View.
    // Type Object is used because the output of linq query 
    // is anonymous type.
    public IEnumerable<object> Table { get; set; }

    public ViewModel()
    {

        // Define some examples 
        var dealers = new List<CarDealer>()
        {
            new CarDealer(){iID=0, name="dealer0"},
            new CarDealer(){iID=1, name="dealer1"},
            new CarDealer(){iID=2, name="dealer2"},
        };

        var garages = new List<Garage>()
        {
            new Garage(){gID = 0, gname="garage0", dealerID=0},
            new Garage(){gID = 1, gname="garage1", dealerID=1},
            new Garage(){gID = 2, gname="garage2", dealerID=2},
        };

        var repairs = new List<Repair>()
        {
            new Repair(){rID=0, date=new DateTime(2019,09,01), cost=00.99, garageID=2},
            new Repair(){rID=1, date=new DateTime(2019,09,02), cost=10.99, garageID=1},
            new Repair(){rID=2, date=new DateTime(2019,09,03), cost=20.99, garageID=0},
            new Repair(){rID=3, date=new DateTime(2019,09,04), cost=30.99, garageID=1}
        };

        // Create Linq query
        Table = from repair in repairs
                    join garage in garages on repair.garageID equals garage.gID
                    join dealer in dealers on garage.dealerID equals dealer.iID
                    select new { 
                        ID = repair.rID, 
                        Date = repair.date, 
                        Cost = repair.cost, 
                        GarageName = garage.gname, 
                        DealerName = dealer.name};
    }

}

Make sure bind your view model, here for simplicity I used MainWindow as View

public partial class MainWindow : Window
{
    public MainWindow()
    {
        InitializeComponent();
        DataContext = new ViewModel();
    }
}

In Xaml file use below datagrid

<DataGrid ItemsSource="{Binding Table}"/>

After running the application you should see something like this