TwoWay Binding DataGrid using LINQ to Entities

Mar 27, 2009 at 8:34 PM
Edited Mar 27, 2009 at 8:34 PM
Forgive me if I overlooked an already brought up discussion. I am trying to implement a LINQ to Entities model (my only solid option working with an Oracle DB) in a WPF application.

I think I am off to the right start but I cannot seem to make the two-way binding actually perform the CRUD functionality against the db.

Here's the XAML:

<toolkit:DataGrid Name="dgCustomers" AutoGenerateColumns="False" >
<toolkit:DataGrid.Columns>
<toolkit:DataGridTextColumn Header="First Name" Binding="{Binding FirstName, Mode=TwoWay}" />
<toolkit:DataGridTextColumn Header="Initial" Binding="{Binding MiddleInitial, Mode=TwoWay}" />
...
</toolkit:DataGrid.Columns>
</toolkit:DataGrid>

Here's the C#:

void Window1_Loaded(object sender, RoutedEventArgs e)
{
OracleTestEntities entities = new OracleTestEntities();
dgCustomers.ItemsSource = entities.Customers;
}



The data is displayed properly, and the data grid seems to work, allowing me to edit columns, add a new row, etc... but when I check back in Oracle and query the table, nothing is altered in any manner.

When using the LINQ to Entities style data source, do I need to manually write the C# code performed on those events? Anyone have ideas? Examples?

Thank you,
Coordinator
Mar 27, 2009 at 9:11 PM
Hi trov2k,

The changes made to the datagrid will update only the in memory entities (be it SQL or Oracle). Yes you need to write custom code to commit to the actual database.
Mar 27, 2009 at 9:42 PM
Edited Apr 1, 2009 at 11:47 PM
*** irrelevant ***
Mar 27, 2009 at 9:50 PM
Edited Mar 28, 2009 at 1:23 AM
*** irrelevant ***
Mar 28, 2009 at 1:18 AM
Ok... one step further... I knew something really simple was going wrong, and I am embarrassed to admit, it was an event firing, and an event handler properly programmed, but the two not wired... classic mistake. All it took was :

// XAML
<toolkit:DataGrid x:Name="dgCustomers"  
                AutoGenerateColumns="False"
                IsSynchronizedWithCurrentItem="True"
                RowEditEnding="dgCustomers_RowEditEnding"
                >
            <toolkit:DataGrid.Columns>
                <toolkit:DataGridTextColumn Header="First Name" Binding="{Binding FirstName, Mode=TwoWay}" />
                <toolkit:DataGridTextColumn Header="Initial" Binding="{Binding MiddleInitial, Mode=TwoWay}" />
                ...

// C# Loaded
dgCustomers.ItemSource = myEntities.Customers;

// C# RowEditEnding Event Handler
ICollectionView cv = CollectionViewSource.GetDefaultView(dgCustomers.ItemsSource);
IEditableCollectionView iecv = cv as IEditableCollectionView;
iecv.CommitEdit();
entities.SaveChanges();


That seems to do it... Where I am stuck now is what event handlers take care of operations like Add and Delete? I could not find something like RowAddEnding or something similarly intuitive. And if I add a row now, the RowEditEnding still fires and throws and exception on CommitEdit().

Anyone have some words of wisdom or examples for handling the add and delete operations?

Thank you.
Mar 28, 2009 at 1:25 AM
Just for testing I removed all three lines regarding the collection views from the event handler... "entities.SaveChanges()" was all it took to perpetuate row changes to the db.

Interestingly enough, If I add a new row via the grid, when it loses focus, nothing is added (but still remains on the grid)... and then edit a cell in an existing row, both the changes to that row, and the new row are perpetuated. Not good.
Coordinator
Mar 30, 2009 at 6:17 PM
Hello trov2k,

The problem here is RowEditEnding gets raised before the actual commit is performed on the Row. So in case of adding a new row, the row is not added to the itemssource yet. Hence it doesnt get saved to the DB. Unfortunately currently we dont have a RowEditEnded event. One of the options could be to do a Dispatcher.BeginInvoke which executes entities.SaveChanges, so that by the time it gets called row is already committed.
Apr 1, 2009 at 6:40 PM
VamseeP-
Thank you very much for your reply. Out of all my posts across dozens of relative sites, you are the only one yet. After what you said, I am concerned with my specific data model and how it is changing the connections with the DataGrid part. I never use views or commit changes, only save the entities in the ending event. Here's all I got:

        private void dgCustomers_RowEditEnding(object sender, Microsoft.Windows.Controls.DataGridRowEditEndingEventArgs e)
        {
            entities.SaveChanges();
        }


This code updates the grid visually immediately and the changes are posted to the database properly as soon as the row is completed (ex. clicking on another row, tab off the last field, or enter).

Is this a bad thing? If what you say is true, how would this work? If this code is enough, what are the event handlers I should use for Adds and Deletes from the grid?

Thanks.
Apr 2, 2009 at 12:12 AM
I've gotten one step further it seems:

        private void dgCustomers_RowEditEnding(object sender, Microsoft.Windows.Controls.DataGridRowEditEndingEventArgs e)
        {
            ICollectionView cv = CollectionViewSource.GetDefaultView(dgCustomers.Items);
            IEditableCollectionView iecv = (IEditableCollectionView)cv;

            if (iecv.IsAddingNew)
            {
                iecv.CommitNew();
            }
            if (iecv.IsEditingItem)
            {
                iecv.CommitEdit();
            }
            entities.SaveChanges();
        }

If I understand what you are saying, the CommitNew() function forces the new grid row into its items collection bound via ItemsSource, and thus when the entities are saved it is persisted back to the db. Tested and it seems to work. As always, not without complications. I am stuck/confused on two things now:

1. The CommitEdit() seems unnecessary. Whether that entire clause is present or just the entities.SaveChanges() call, the edits are pushed properly to the db and it all stays in sync. If I understand your point about timing, the edit is commited after this handler is run. So what's got me confused is why does it work without the explicit CommitEdit() function? If the only code in the handler is the single entities.SaveChanges() line, something is commiting them before... that or there is something about the way edits are performed against a bound EntityObject that I'm not understanding.

2. After adding a row to the database (works fine), but if I add a new row again or edit an existing row, it throws an InvalidOperationException. Note that the add or edit is committed successfully and reflected in the database table, but this exception is saying that it could not update the ObjectContext. The error is below:
The changes to the database were committed successfully, but an error occurred while updating the object context. The ObjectContext might be in an inconsistent state. Inner exception message: AcceptChanges cannot continue because the object's key values conflict with another object in the ObjectStateManager. Make sure that the key values are unique before calling AcceptChanges.
I understanding it is having commited an Add to the collection that caused this... but not quite sure about the logic behind that. If the DataGrid control commits and edit or add to it's collection, and that collection is syncing with the entity object it is bound to every time "SaveChanges" is called, how are they getting out of sync? Also, what is the "AcceptChanges" method/function? Where is it and what is firing it?

Thank you ahead of time. Hopefully these are legit inquiries to you guys. -T
Apr 7, 2009 at 9:50 PM
Any further information on these two topics? I feel like I am so close, but thoroughly stuck here.