Bind Data to Specific Columns of Datagridview - Entity Framework

Question

I don't know if I should take all the data or just the one I need :) I am doing it for the first time. I want to bind this data to Specific columns

See select

var SQLquery = (from artikel in db.DHH_Lagerverwaltung_Artikel
join hersteller in db.DHH_Lagerverwaltung_Hersteller on artikel.ID_Hersteller equals hersteller.ID_Hersteller
join kategorie in db.DHH_Lagerverwaltung_Kategorie on artikel.ID_Kategorie equals kategorie.ID_Kategorie
join bestand in db.DHH_Lagerverwaltung_Bestand on artikel.ID_Artikelnummer equals bestand.ID_Artikelnummer
join fach in db.DHH_Lagerverwaltung_Fach on bestand.ID_Fach equals fach.ID_Fach
join stellplatz in db.DHH_Lagerverwaltung_Stellplatz on fach.ID_Stellplatz equals stellplatz.ID_Stellplatz
join ebene in db.DHH_Lagerverwaltung_Ebene on stellplatz.ID_Ebene equals ebene.ID_Ebene
join regal in db.DHH_Lagerverwaltung_Regal on ebene.ID_Regal equals regal.ID_Regal
join lager in db.DHH_Lagerverwaltung_Lager on regal.ID_Lager equals lager.ID_Lager
//where lager.Raum == ""
select new {
 ArtikelBezeichnung = artikel.Bezeichnung,
  ArtikelEAN = artikel.EAN,
  BestandsMenge = bestand.Menge,
  MinMenge = bestand.Menge,
  Lagerort = lager.Raum + regal.RegalNr + ebene.Ebene + stellplatz.Stellplatz + fach.Fach,
  Hersteller = hersteller.Name,
  Kategorie = kategorie.Name
});

Looks good so far, although that's a *lot* of joins. What's the problem? — Robert Harvey, Apr 06 '20 at 16:49
now i want to bind the data to the columns in the datagrid, but i have no idea how to do it — Thomas, Apr 06 '20 at 16:51
Did you created columns manually in wizard or you are adding them in code ? — Samuel B., Apr 06 '20 at 16:55
@Thomas if I were you, I would create a stored procedure and just load the result. What column I want to bind should be easier — Tấn Nguyên, Apr 06 '20 at 17:00
Personally find datagridview easier to use with a datatable, and the easiest way of using a datatable is go go around EF rather than through it.. — Caius Jard, Apr 06 '20 at 17:05
@SamuelB. for me both options would be possible, I would not care. — Thomas, Apr 06 '20 at 17:06
@CaiusJard would be a possibility, but I would like to use only the entity framework — Thomas, Apr 06 '20 at 17:10
Hopefully then, EF will accurately translate that query as written! — Caius Jard, Apr 06 '20 at 17:13
@Thomas I mean create a `Stored Procedure SQL` for better performance and loading the result through EF because you have so many joins. You may test again if you have binded data and make comparison. — Tấn Nguyên, Apr 06 '20 at 17:22
@TấnNguyên very cool but i have no idea how to do it, i will have a look. if you want you can send me an example. — Thomas, Apr 06 '20 at 17:27
@Thomas take a look at https://www.tutorialspoint.com/entity_framework/entity_framework_stored_procedures.htm — Tấn Nguyên, Apr 06 '20 at 18:09

Caius Jard · Accepted Answer · 2020-04-08T09:29:22.083

1

Do this one line of code, underneath the query:

dataGridViewX.DataSource = new BindingSource(SQLquery.ToList(), null);

The BindingSource can work with the List<anonymoustype> the query will create

Alternatively, because you're working with anonymous types you could also make an extension method to generate you a BindingList instead:

static class ListExtensions
{
    public static BindingList<T> ToBindingList<T>(this IList<T> source)
    {
        return new BindingList<T>(source);
    }
}

You can bind a datagridview to a bindingList:

dataGridViewX.DataSource = SQLquery.ToList().ToBindingList();

Binding through a BindingSource gives some advantages for filtering, sorting, accessing the current item etc. It also allows you to arrange hierarchical data structures. If you're going to user BindingSource you should perhaps consider NOT using anonymous types, because they're compiler generated POCO classes that you don't really have any reliable access to if you wanted to dig your bindingSource's .Current object out and cast it to something you work with.

If instead you made your class a fully defined one in your own code, then you have:

collection.Select(c => new Whatever(){ Id = c.Id, Name = c.Name });

But you can work with it better:

var x = myBindingSource.Current as Whatever;

If you use anonymous types it's that as Whatever cast that you can't easily do, and you'll end up stuck with myBindingsource.Current being an object, needing either some dynamic workaround (which is not optimal when really this is a design time known class type) or a bit of a hack where you declare another anonymous type with the same order and type of parameters and rely on the compiler making them the same thing when it creates the anonymous types

edited Apr 08 '20 at 09:29

answered Apr 06 '20 at 17:10

Caius Jard

72,509
5
49
80

OK nice thanks. A question about the BindingList: I have to define the thread type in the parameter and I have several how do I do it? – Thomas Apr 06 '20 at 17:24
No sry I mean the data type that I have to define – Thomas Apr 06 '20 at 17:42
I'm not 100% sure what you're asking. You can add more things to your Select? – Caius Jard Apr 06 '20 at 19:11
@CaiusJard Nice and clean solution. I will use it in future projects. Thanks. – Samuel B. Apr 07 '20 at 09:42
@CaiusJard i was looking for exactly the solution. both work perfectly. I will then use the bindingsorce solution because of the advantages. Thanks a lot – Thomas Apr 07 '20 at 11:00
@SamuelB. No probs! Actually, I just realized that if you use a BindingSource you can skip the BindingList part too (made an edit) – Caius Jard Apr 07 '20 at 12:31
@Thomas You're welcome! Actually, I just realized that if you use a BindingSource you can skip the BindingList part too.. (made an edit) – Caius Jard Apr 07 '20 at 12:31
@CaiusJard you have helped me so much. thank you for letting me learn from you – Thomas Apr 08 '20 at 09:20

Samuel B. · Answer 2 · 2020-04-06T19:21:17.337

Hi I created a small demo code. You just need to re-write your code and It should work. Hope it will helps you.

private class Data
    {
        public int? Id { get; set; }
        public DateTime? DateTimeShipped { get;set;}
        public DateTime? DontNeed1 { get; set; }
        public DateTime? DontNeed2 { get; set; }
        public bool? Ok { get; set; }

        public Data()
        {

        }
        public Data(int? id, DateTime? dateTime, bool? Ok, DateTime? DontNeed1, DateTime? DontNeed2)
        {
            this.Id = id;
            this.DateTimeShipped = dateTime;
            this.Ok = Ok;
            this.DontNeed1 = DontNeed1;
            this.DontNeed2 = DontNeed2;
        }
    }

Class Data holds values from linq select. Also there is Dictionary which have list of desired columns including their types. This Dictionary is used for creating columns which should be in DataGridView.

Dictionary<string, Type> desiredCols = new Dictionary<string, Type>
{
     {"Id", typeof(int)},
     {"DateTimeShipped", typeof(DateTime)},
     { "Ok", typeof(bool)}
};

IEnumerable<Data> sqlList = (from data in dbContext.SomeTable
                  select new Data
                  {
                      Id = data.Id,
                      DateTimeShipped = data.DatumSuggestFrom,
                      Ok = data.Ok,
                      DontNeed1 = data.DatumSuggestFrom,
                      DontNeed2 = data.DatumSuggestTo
                  }).ToList();

var bindingList = new BindingList<Data>(sqlList.ToList());
foreach(var c in desiredCols)
{
    DataGridViewColumn col = new DataGridViewTextBoxColumn();
    col.Name = c.Key;
    col.HeaderText = c.Key;
    col.ValueType = c.Value;
    dataGridView1.Columns.Add(col);
 };

 foreach(var col in bindingList)
 {
     dataGridView1.Rows.Add(col.Id, col.DateTimeShipped, col.Ok);
 }

Thanks a lot. very interesting. – Thomas Apr 07 '20 at 10:57 — Thomas, Apr 07 '20 at 10:57

Bind Data to Specific Columns of Datagridview - Entity Framework

2 Answers2