How to combine three tables as a data source for Binding in a DataGrid?

Question

I am creating an WPF app with MVVM. I have three tables as sources of my data which I need to display in the DataGrid through the ItemsSource="{Binding}". How and where do I combine this three tables so I could use the combined data set for my binding?

To add more details.

In the DataGrid I need to display the list of all books with column1 as book title (table Titles) and column2 as all authors of the book (table Authors) so I am thinking I need to perform a natural join operation of the table Titles on table AuthorISBN on table Authors. This part I get, but in where and how, I don't.

Any insight would be greatly appreciated.

Accepted Answer

Hi,
try following demo:

XAML:

And classes:

using System.Collections.ObjectModel;  
using System.ComponentModel;  
using System.Linq;  
using System.Windows;  
using System.Windows.Data;  
  
namespace WpfApp038  
{  
  public class ViewModel  
  {  
    public ViewModel() =>cvs.Source = GetData();     
  
    ObservableCollection colData = new ObservableCollection();  
    CollectionViewSource cvs = new CollectionViewSource();  
    public ICollectionView View { get => cvs.View; }  
    private ObservableCollection GetData()  
    {  
      var colISBN = LoadAuthorISBN();  
      var colAuthors = LoadAuthors();  
      var colTitles = LoadTitles();  
      var query = from isbn in colISBN  
                  join author in colAuthors on isbn.AuthorID equals author.AuthorID  
                  join title in colTitles on isbn.ISBN equals title.AuthorISBN  
                  select new Data() { Author = $"{author.FirstName} {author.LastName}", ISBN = isbn.ISBN, Title = title.Title };  
      return new ObservableCollection(query);  
    }  
  
    private ObservableCollection LoadAuthorISBN()  
    {  
      ObservableCollection col = new ObservableCollection();  
      for (int i = 1; i < 10; i++) col.Add(new AuthorISBN() { AuthorID = i, ISBN = $"ISBN {i}" });  
      return col;  
    }  
  
    private ObservableCollection LoadAuthors()  
    {  
      ObservableCollection col = new ObservableCollection();  
      for (int i = 1; i < 10; i++) col.Add(new Authors() { AuthorID = i,FirstName = $"FirstName{i}", LastName = $"LastName{i}" });  
      return col;  
    }  
  
    private ObservableCollection  LoadTitles()  
    {  
      ObservableCollection col = new ObservableCollection();  
      for (int i = 1; i < 10; i++) col.Add(new Titles() { AuthorISBN = $"ISBN {i}", Title=$"Title {i}" });  
      return col;  
    }  
  }  
  
  public class Data  
  {  
    public string Title { get; set; }  
    public string ISBN { get; set; }  
    public string Author { get; set; }  
  
  }  
  
  internal class AuthorISBN  
  {  
    internal int AuthorID { get; set; }  
    internal string ISBN { get; set; }  
  }  
  internal class Authors  
  {  
    internal int AuthorID { get; set; }  
    internal string FirstName { get; set; }  
    internal string LastName { get; set; }  
  }  
  internal class Titles  
  {  
    internal string AuthorISBN { get; set; }  
    internal string Title { get; set; }  
  }  
}

Result:

Answer

I make a demo based on your description as below:

public class ViewModel  
    {  
        public ObservableCollection models { get; set; } = new ObservableCollection();  
        public ViewModel()  
        {  
            string connsql = @"Data Source = (localdb)\ProjectsV13; Initial Catalog = *******.......";  
            try  
            {  
                using (SqlConnection conn = new SqlConnection())  
                {  
                    conn.ConnectionString = connsql;  
                    conn.Open();  
                    String sql = "select T.Title,T.ISBN,A.FirstName,A.LastName from dbo.Titles as T inner join dbo.AuthorISBN as AI on T.ISBN = AI.ISBN inner join dbo.Author as A on AI.AuthorID = A.AuthorID";  
                    SqlCommand cmd = new SqlCommand(sql, conn);  
                    SqlDataReader dr = cmd.ExecuteReader();  
                    while (dr.Read())  
                    {  
                        Model model = new Model();  
                        model.FirstName = dr["FirstName"].ToString();  
                        model.LastName = dr["LastName"].ToString();  
                        model.Title = dr["Title"].ToString();  
                        models.Add(model);  
                    }  
                    conn.Close();  
                }  
            }  
            catch (Exception ex)  
            {  
                Debug.WriteLine(ex.Message.ToString());  
            }  
        }  
  
    }  
    public class Model  
    {  
        public string Title { get; set; }  
        public string FirstName { get; set; }  
        public string LastName { get; set; }  
    }

The Xaml is:

The result picture is:

Did I miss any info for your requirement? If I did, please point out.

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer

@Dmitriy

IMHO, what you need to send is a custom class/object with properties in a List the List is blindable.

You can do a join sure, but you should learn how to use a datareader.

The link below should help you understand how to do the join and then select the column names via the datareader. It doesn't matter that it talking MS SQL server when you are using Access, becuase the use of T-SQL and using a ADO.NET datareader are the same even if using Access.