Refresh data when data binding using EF core and c# data binding-CodePudding

I am using VS 2019, c# application with Win forms and Entity framework core 5.0

In my app I have a System.Windows.Forms.DataGridView used for displaying and updating data in a MySQL database. Data is bound to the DataGridView by using a System.Windows.Forms.BindingSource myBindingSource and binding the EF table data using

myDbContext.SomeEntities.Load();
myBindingSource.DataSource = myDbContext.SomeEntities.Local.ToBindingList();

This does correctly display the data, once I change some data in the grid and call myDbContext.SaveChanges() it does save the data to the database.

So, as long as the app runs standalone it works fine.

However, what I want to achieve is that the form containing the grid refreshes the data whenever any other action outside my app changes the data. So if any update to the data happens outside my app I want these changes to be immediately visible in the open form without the need for the user to close and re-open the form containing the DataGridView. I know that, of course, I need to have a trigger for these changes. That might be a timer or an external signal. For now it is a timer.

In the timer I do a

foreach( var rec in (BindingList<SomeEntities>)this.DataSource)
{
  DbContext.Entry(rec).Reload();
}

and afterwards I do a

CurrencyManager cm = (CurrencyManager)((myDataGridView).BindingContext)[(ctrl as DataGridView).DataSource];
if (cm != null) cm.Refresh();

This works fine for external update of an existing record. If a record ist inserted or deleted, however, it fails. When externally inserting, the new record is simply not known in the existing BindingList and thus not refreshed; when a record is externally deleted the Reload fails (because it is no longer existing in the database). Both is understandeable enough for what is happening.

What would be the correct way to not only refresh the existing entities but also refresh the content of the collection myDbContext.SomeEntities

When searching for an answer I often read "use short lifetime of the DbContext". Understandeable, but I do need the DbContext for being able to call myDbContext.SaveChanges() in order to save any changes being made in the grid. Do I? Or is there another way? If the DbContext is to be used only during loading of the grid, how could I use it as a data source for the grid using regular data binding?

With EntityFramework 6 there was

_myObjectContext.RefreshAsync(RefreshMode.StoreWins, GetAll())

Don't know whether this would have helped since I did not try using EntityFramework 6, but in EF core there is no equivalent to this, anyway. So are there any suggestions?

CodePudding user response：

I think that in such scenario - when you have your data grid open all the time (as I understood), this should be done more manually.

For example, using MVVM, I would create a ViewModel with some observable collection of view items, for example:

Let's say that this is your db model:

public class DbItem
{
  public int Id {get;set;}
  public string Name {get;set;}
}

Now, I would create some data to be used in view model:

public class ItemData: INotifyPropertyChanged
{

  public ItemData(DbItem item)
  {
     id = item.Id;
     name = item.Name; //notice that I use here backup field
  }
  public bool Modified {get; private set;}

  int id;
  public int Id 
  {
     get { return id; }
     set
     {
         if(id != value)
         {
            id = value;
            NotifyPropertyChanged();
         }
     }
  }

  string name;
  public string Name
  {
     get { return name; }
     set
     {
         if(name != value)
         {
            name = value;
            NotifyPropertyChanged();
         }
     }
  }
}

Here I used INotifyPropertyChanged, but it all comes down rather to your needs. You could just updated field Modified to true, or everytime the record changes, just update it in db (SQL UPDATE/INSERT)

Now in my ViewModel I would do:

public class ViewModel
{
  public ObservableCollection<ItemData> DataSource {get; set;}
}

I doubt that you can use ObservableCollection in WinForms as you can in WPF, so instead of that you could create some binding collection I think.

Anyway now when you read the data from database, you should convert them to your items:

public class ViewModel
{
  public void ReadData()
  {
     DataSource.Clear();
     List<DbItem> dbItems = service.GetDataFromDatabaseWithNoTracking();
     foreach(var item in dbItems)
     {
        DataSource.Add(new ItemData(item));
     }
  }
}

Now, when you need to update something, then just:

public class ViewModel
{

  public void UpdateData(ItemData data)
  {
     //if data.Modified...
     DbItem db = new DbItem();
     db.Id = data.Id;
     db.Name = data.Name;
     service.UpdateItem(db);
  }

}

And in EF:

public void UpdateItem(DbItem item)
{
  var entry = dbContext.Entry(item);
  dbContext.Save();
}

All comes down that you won't be tracking the records from database. You should do this manually.

What do you think about this solution?

CodePudding user response：

I'm not sure if this would be a nice interface. Suppose the operator is happily editing a row, and suddenly you decide to reload the table: all his changes lost? And what if the operator just changed a value from 4 to 5, while someone else just changed the same value from 4 to 3, which one should we keep? And what if someone else decide to remove a row, because he thought

So I would recommend not to do an automatic refresh, add a button for this. Something like the reload button in a browser. Add F5 button to start the refresh and your interface is compatible with most windows application that might show outdated data.

However, this has the disadvantage, that if the operator edited a value, that someone else also edited, or even deleted, you should decide what to do. My advice would be: ask the operator:

If operator edited a row that someone else also edited: which row should we keep?
If operator edited a row that someone else deleted?
If operator deleted a row that someone else just changed?

Let's assume your datagridview shows Products:

class Product
{
    public int Id {get; set;}
    public string Name {get; set;}
    public decimal Price {get; set;}
    public int Stock {get; set;}
    ...
}

Using visual studio designer, you've added a DataGridView and some columns. In your constructor you can assign properties to the columns:

public MyForm() : Form
{
    InitializeComponents()

    this.columnId.DataPropertyName = nameof(Product.Id);
    this.columnName.DataPropertyName = nameof(Product.Name);
    this.columnPrice.DataPropertyName = nameof(Product.Price);
    ...
}

You also need a method to fetch the Products that must be displayed from the database, and later refetch them again, to see which Products are changed. Of course you hide that they are from a database.

IEnumerable<Product> FetchProductsToDisplay()
{
    ... // Fetch the data from the database; out-of-scope of this question
}

To display the fetched Products, use the DataSource and a BindingList:

BindingList<Product> DisplayedProducts
{
    get => (BindingList<Product>)this.dataGridView1.DataSource;
    set => this.dataGridView1.DataSource = value;
}

Initially you show the Products:

void OnFormLoading(object sender, ...)
{
    this.DisplayedProducts = this.FetchProductsToDisplay()
}

So now the operator happily can edit existing Products, maybe add some new ones, or remove some Products. After a while he wants to refresh the Products with the data others entered:

private void OnButtonRefresh_Clicked(object sender, ...)
{
    this.UpdateProducts();
}

private void UpdateProducts()
{
    IEnumerable<Product> dbProducts = this.FetchProductsToDisplay();
    IEnumerable<Product> editedProducts = this.DisplayedProducts();

We have to compare the Products from the database with the Products in the DataGridView. Match the Products by Id: same Id, expect same Product.

Id is zero, and thus only in editedProducts, it has been added by the operator, but not added to the database yet.
non-zero Id is only in editedProducts: it has been deleted by someone else
Id is both in dbProducts and in editeProducts, but values not equal: either edited by the operator, or by someone else

A difficult one:

Id is only in dbProducts: it has been added by someone else, or deleted by operator.

To be able to ask the operator the correct question, it seems that we also need the Products that were displayed before the operator started to edit.

private IEnumerable<Product> OriginalProducts => ...

So now we are able to detect which products are added / removed / changed, by the operator and/or in the database:

Id in originalProducts editedProducts dbProducts
          yes              yes          yes     compare values to detect edits
          yes              yes          no      someone else deleted
          yes              no           yes     operator deleted.
          yes              no           no      both operator and someone else deleted

      no               no            yes       someone else added
      no               yes           no        operator added
      no               yes           yes       both operator and someone else added

The procedure to detect changes:

private void DetectChanges(IEnumerable<Product> originalProducts,
                           IEnumerable<Product> editedProducts,
                           IEnumerable<Product> dbProducts)

{
    // do a full outer join on these three sequences:
    var originalDictionary = originalProducts.ToDictionary(product => product.Id);
    var dbDictionary = dbProducts.ToDictionary(product => product.Id);

    // some Ids in editedProducts have value zero:
    var addedProducts = editedProducts.Where(product => product.Id == 0);
    var editedDictionary = editedProducts.Where(product => product.Id != 0)
        .ToDictionary(product => product.Id);

    var allUsedIds = originalDictionary.Keys
        .Concat(dbDictionary.Keys)
        .Distinct();

Note: all editedProducts with Id != 0 already existed in the database when they were last fetched, so their Ids are already in originalDictionary. I used Distinct to remove duplicate Ids

    foreach (int id in allUsedIds)
    {
        bool idInDb = dbDictionary.TryGetValue(id, out Product dbValue)
        bool idInOriginal = originalDictionary.TryGetValue(id, out Product originalValue);
        bool idInEdited = editedDictionary.TryGetValue(id, out Product editedValue);

Use the table from above, and the values idInDb / idInOriginal / idInEdited to find out if the value has been added or removed / changed.

Sometimes it will be enough to just Add / Edit / Change the value in the DataGridView; sometimes you'll have to ask the operator.

Advice:

Changes made by someone else:

If added by someone else: just add it to the DataGridView
If removed by someone else, not edited by operator: just remove from DataGridView
If removed by someone else, edited by operator: ask operator what to do

Changes made by the operator:

If added by operator (Id == 0): insert in the database
If removed by operator, remove it from database
If edited by operator, not by someone else: update the database
Changes made by the operator and by someone else: ask operator which one to keep. Either update the database or the DataGridView.

Detect Product Changes: use IEqualityComparer

To detect change, you need an equality comparer that compares by value:

public class ProductComparer : EqualityComparer<Product>
{
    public static IEqualityComparer<Product> ByValue {get} = new ProductComparer();

    public override bool Equals(Product x, Product y)
    {
        if (x == null) return y == null;              // true if both null
        if (y == null) return false;                  // because x not null
        if (Object.ReferenceEquals(x, y) return true; // same object
        if (x.GetType() != y.GetType()) return false; // different types

        return x.Id == y.Id
            && x.Name == y.Name   // or use StringComparer.CurrentCultureIgnoreCase
            && x.Price == y.Price
            && ...
    }

    public override int GetHashCode(Product x)
    {
        if (x == null) return 87742398;

        // for fast hash code: use Id only.
        // almost all products are not edited, so with same Id have same values
        return x.Id.GetHashCode();
    }
}

Usage: IEqualityComparer productValueComparer = ProductComparer.ByValue;

After detecting which products are in which dictionary:

if (idInDb && idInEdited && )
{
    // one of the Products in the DataGridView already exists in the database
    // did the operator edit it?

    bool operatorChange = !productValueComparer.Equal(originalValue, editedValue);
    bool dbChange = !productValueComparer.Equal(originalValue, dbValue);

    if (!productValueComparer.Equal(operatorValue, dbValue);)
    {
        // operator edited the Product; someone change it in the database
        // ask operator which value to keep
        ...
    }
    else
    {
        // operator edited the Product; the same value is already in the database
        // nothing to do.
    }

So for every Id, check if it was already in the database, check if it is still in the database and check if the operator kept it in the dataGridView. Also check which values are changed, to detect whether you need to add / remove / update the database, or the DataGridView or maybe you have to do nothing.