Simplest way to load data to DataGridView from parent and child table with possibility of sorting

Question

I working with C# and EF 6.4 and I am looking for clean and easy solution for my problem. I have simple base like that:

and I want to load data to DataGridView to be looked like that:

I have tried the following solutions:

1

//sorting working, but doesn`t show columns "name" and "title" 
Db.BooksAuthors.Include(x => x.Authors).Load();
DataGridView1.DataSource = Db.BooksAuthors.Local.ToBindingList;

2

//sorting not working, but shows columns "name" and "title" 
public class BooksAuthorsView
{
    public Int32 idBook { get; set; }
    public Int32 idAuthor { get; set; }
    public string BookTitle { get; set; }
    public string AuthorName { get; set; }
}
private void Show()
{
    var list = (from BA in Db.BooksAuthors
                select new BooksAuthorsView() { idBook = BA.idBook, idAuthor = BA.idAuthor, BookTitle = BA.Books.title, AuthorName = BA.Authors.name });
    DataGridView1.DataSource = new BindingSource() { DataSource = new BindingList<BooksAuthorsView>(list.ToList) };
}

EDIT: I checked this solution. It`s working, but is it the simplest solution?

Curious how you manage to invoke methods without putting () on the end; is this code converted from VB? — Caius Jard, Nov 29 '21 at 20:24
I'd have expected something like `DataGridView1.DataSource = Db.BooksAuthors.Select(BA => new BooksAuthorsView() { idBook = BA.idBook, idAuthor = BA.idAuthor, BookTitle = BA.Books.title, AuthorName = BA.Authors.name }).ToList()` to just work, tbh — Caius Jard, Nov 29 '21 at 20:29
@Caius Jard I only need readonly. Yes, it`s converted from VB — CzarnyLewis, Nov 30 '21 at 10:50

Jingmiao Xu-MSFT · Answer 1 · 2021-12-01T03:18:58.360

If you want to sort by clicking on the column header, you can refer to the following code:

public DataTable dvtodt(DataGridView dv)
    {
        DataTable dt = new DataTable();
        DataColumn dc;
        for (int i = 0; i < dv.Columns.Count; i++)
        {
            dc = new DataColumn();
            dc.ColumnName = dv.Columns[i].HeaderText.ToString();
            dt.Columns.Add(dc);
        }
        for (int j = 0; j < dv.Rows.Count; j++)
        {
            DataRow dr = dt.NewRow();
            for (int x = 0; x < dv.Columns.Count; x++)
            {
                dr[x] = dv.Rows[j].Cells[x].Value;
            }
            dt.Rows.Add(dr);
        }
        return dt;
    }

private int SortOrder_ = 0;

private void dataGridView1_ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
    {
        
        int nColumnIndex = e.ColumnIndex;
        DataTable d = dvtodt(dataGridView1);
        if (SortOrder_ == 0)
        {
            d.DefaultView.Sort = this.dataGridView1.Columns[nColumnIndex].Name + " ASC";
            SortOrder_ = 1;
        }
        else if (SortOrder_ == 1)
        {
            d.DefaultView.Sort = this.dataGridView1.Columns[nColumnIndex].Name + " desc"; 
            SortOrder_ = 0;
        }
        dataGridView1.Columns.Clear();
        dataGridView1.DataSource = d;
    }

Thank you. But sorting not working. When I click on header column, nothing happens. — CzarnyLewis, Nov 30 '21 at 10:54
@CzarnyLewis, I edited my answer. If you have any problem, please let me know. — Jingmiao Xu-MSFT, Dec 01 '21 at 03:22

Simplest way to load data to DataGridView from parent and child table with possibility of sorting

1 Answers1