Data binding linq query to datagridView in Entity Framework 5.0

Question

I am learning the Entity Framework (5.0 and VSExpress 2012) and I'm having real trouble binding my query to a dataGridView in WinForms. I have the below code and it displays my query okay when I start the application but I don't know what I need to do to update the dataGridView after changing the data in the underlying database. What's the best way of doing this? What am I doing wrong here?

private void Form1_Load(object sender, EventArgs e)
    {
        using( var ctx = new TimeKeepEntities())
        {

            var qLoggedIn = from r in ctx.tblTimeRecords
                        where (r.tblEmployee.Active && !r.ClockOut.HasValue) || System.Data.Objects.EntityFunctions.DiffDays(r.ClockOut, DateTime.Now)<30
                        select new { Name = r.tblEmployee.Last + ", " + r.tblEmployee.First, r.tblProject.ProjName, r.ClockIn, r.ClockOut };

            dataGridView1.DataSource = qLoggedIn.ToList();

        }
    }

score 11 · Accepted Answer · edited Dec 20 '12 at 18:03

11

Pho please note that they are using winforms not asp.net. According to MSDN you can do the following:

BindingSource bindingSource1 = new BindingSource();
bindingSource1.DataSource = (from r in ctx.tblTimeRecords
                        where (r.tblEmployee.Active && !r.ClockOut.HasValue) || System.Data.Objects.EntityFunctions.DiffDays(r.ClockOut, DateTime.Now)<30
                        select new { Name = r.tblEmployee.Last + ", " + r.tblEmployee.First, r.tblProject.ProjName, r.ClockIn, r.ClockOut }).ToList();

dataGridView1.DataSource = bindingSource1;

see: msdn documentation

edited Dec 20 '12 at 18:03

dalexsoto

3,412
1
27
43

answered Sep 19 '12 at 20:16

Lance

178
6

3

I did this. The binding was correct and I was able to edit data from the datagridview when using a save button with: context.SaveChanges(). The problem is when I have to add a new row of data or delete it. Those kind of changes are not reflected in the database when SaveChanges. Maybe I'm forgeting some property set... ?¿ – Alejandro del Río Dec 31 '12 at 19:21
1

I'm having the same problem @AlejandrodelRío. Now I'm going to spider all your questions and answers to see if you solved it. Wish me luck! – Robino Jan 10 '14 at 18:06
@Robino I don't have this answered, but this aproach is not going to help you to add or delete objects directly from the datagridview. Instead you have to create a BindingList, where ObjectToShowInDatagrid is an object with the atributes you want to show and it have an instance of the database entity. You also have to add the notifyPropertyCHanged method to all the "set". This way the changes will be reflected to the object and the database. I will explain this further in an answer. – Alejandro del Río Jan 14 '14 at 14:06
@Robino Take a look at http://stackoverflow.com/questions/21117217/best-aproach-to-bind-a-datagridview-to-database-entity-ies – Alejandro del Río Jan 14 '14 at 15:27

score 0 · Answer 2 · answered Sep 19 '12 at 19:20

0

You have to bind the data with dataGridView1.DataBind();:

...
dataGridView1.DataSource = qLoggedIn.ToList();
dataGridView1.DataBind();
...

answered Sep 19 '12 at 19:20

pho

520
3
9

I don't see .DataBind() method available in my intellisense. I am using Entity Framework 5 and maybe they changed it to something else. – Mr1159pm Sep 19 '12 at 19:24
I am not too sure as to why you don't see it in your editor, but I know it has to be bound after assigning a datasource :) – pho Sep 19 '12 at 19:28
The thing is, this is more of a .NET method on the GridView control itself, and not anything to do with Entity Framework. – pho Sep 19 '12 at 19:30
If still no luck, it might be that the data assigned to the GridView is not expected by the GridView (you either have to set the GridView to AutoGenerateColumns or pre-assign data-bound columns on the grid and then bind the data to the it - with columns matching object properties). Have a look at this: https://github.com/pauloosthuysen/int/blob/master/Int/Home.aspx.cs & https://github.com/pauloosthuysen/int/blob/master/Int/Home.aspx – pho Sep 19 '12 at 19:40
3

.DataBind is not required in Winforms with the DataGridView the same way it is with WebForms GridView. – Jim Wooley Sep 19 '12 at 20:46

score 0 · Answer 3 · answered Sep 19 '12 at 20:49

0

.Net uses a disconnected model. When you fetch information from the database, it is a snapshot at that point in time. If you change data in the underlying data store, those changes won't be reflected unless you explicitly re-query the database and rebind your UI.

When you save changes in your UI, EF can check to see if anyone else changed the row that you are modifying (for concurrency concerns) and let you know if there is a potential conflict.

answered Sep 19 '12 at 20:49

Jim Wooley

10,169
1
25
43

1

Interesting. Could you post some example? It would be usefull. – Alejandro del Río Dec 31 '12 at 19:28
How does one encourage EF to perform this check? – Robino Jan 10 '14 at 18:08

score 0 · Answer 4 · answered Nov 01 '15 at 10:29

 IEnumerable<DataRow> query =( from p in orginalList.AsEnumerable()
                                    where p.Field<long>("Category") == 2
                                    select p).ToList();
                        DataTable boundTable = query.CopyToDataTable<DataRow>();

                        dataGridView1.AutoGenerateColumns = false;

                        dataGridView1.DataSource = boundTable;

Data binding linq query to datagridView in Entity Framework 5.0

4 Answers4

Linked