Binding a flattern EF entity over GridView and allow sync with repositories

Question

I'm using for the first time Entity Framework in a complex grid databinding situation and i need some strategies to accomplish this task. I've got these POCO classes autogenerated by "EF POCO reverse code first" (the same for mappings) and for each one there's also a repository that i use to query or do simple CRUD operations:

    public class Order
    {
        public int IdOrder { get; set; }
        public int IdCustomer { get; set; }
        public int IdOrderType {get; set;}
        public string ChOrder { get; set; }
        public decimal Total { get; set; }

        public Customer Customer { get; set; }
        public OrderType OrderType {get; set;}
    }

    public class OrderType 
    {
        public int IdOrderType {get; set;}
        public string ChOrderType {get; set;}
        public string Description { get; set; }
    }

    public class Customer
    {
        public int IdCustomer { get; set; }
        public string ChCustomer { get; set;}
        public string Name { get; set; }
        public string City { get; set; }
        public CustomerType CustomerType { get; set;}
    }

    public class CustomerType
    {
        public int IdCustomerType { get; set; }
        public string ChCustomerType { get; set; }
    }

Now i'd like to query OrderRepository and show all results in a DataGridView (in real case, its the DevExpress XtraGrid). This DataGridView must show "direct" Order informations (ChOrder, Total) but also "indirect" informations such the ones reachable by using navigation properties (ex. Customer.Name, Customer.City, Customer.CustomerType.ChCustomerType). What i want is a thing like that:

In a "Readonly DataGridView" scenario, this task is fairly simple. You only have to create a linq query that flattern the resultset of the repository and bind it to the grid like that:

this.OrderRepository.AsQueryable().Select(x => new { .ChOrder = x.ChOrder, 
                             .Total = x.Total, 
                             .ChOrderType = x.OrderType.ChOrderType, 
                             .ChCustomer = x.ChCustomer, 
                             .Name = x.Customer.Name, 
                             .City = x.Customer.City, 
                             .CustomerType = x.Customer.CustomerType}).ToList();

But the grid must have to be editable. In particular i'd like to add new orders, modify ChOrderType (and consequently its description), modify the ChCustomer (and consequently his Name, City and Type) and delete orders. I cannot bind LINQ result because:

1. LINQ anonymous type result are immutable (so the grid would be readonly)
2. LINQ generates a list where items can change (user can delete items, add new one, modify etc..) and, because of it is a list, it can't track wheter items has been deleted, added and modified (even if i use a better suitable BindingList or ObservableCollection). So i won't know which entities delete, update or insert.
3. LINQ result item does not implement INotifyPropertyChanged, so, if i change for example "ChCustomer" its relative data (Name, City ec..) won't be updated on the screen.

Due to those problems, i tought to bind directly the POCO OrderClass in this way:

this.OrderRepository.AsQueryable().Select()

And set "indirect" fields "DataPropertyName" like that:

Column "Name" -> Customer.Name

Column "City" -> Customer.City

But again, it doesn't fit because:

1. My POCO classes does not implement INotifyPropertyChanged, so, if i change for example "ChCustomer" its relative data (Name, City ec..) won't be updated on the screen.

2. When i edit for example ChCustomer, obviously databinding doesn't know how to get a Customer instance from its repository according on the ChCustomer the user has inserted and assign it to the current Order "Customer" property.

3. Sometimes editable fields (as ChCustomers) are part of composite keys and actually the edit of a composite key of an entity is not allowed in EF. This introduces more problems.

So, at the end of the story, it turns out that it is a difficult task to manage when EF is involved with databinding (and in our software there are thousand case like this).

I'd like to know if there is a good strategy to accomplish this task.

I tought about how to handle this by using a ViewModel (that also implement INotifyPropertyChanged) object to bind to the gridView like that:

public class OrderViewModel : INotifyPropertyChanged{

        public event PropertyChangedEventHandler PropertyChanged;

        //Order info
        public string ChOrder { get; set; }
        public decimal Total {get; set;}
        public string ChOrderType {get; set;}
        public string Description { get; set; }

        //Customer info
        public string ChCustomer { get; set; }
        public string Name { get; set;}
        public string City { get; set;}
        public string ChCustomerType {get; set;}
    }

It works very well, but i don't know how to sync data with repositories. In particular how to track deleted items and delete them using repository? how to track added items? And the edit changes? And also...how to set all those properties that comes from navigation properties (such as name, city and so on)? When i call SaveChanges() of my UnitOfWork i'd like to have all my chages correctly persisted to DB.

Sorry if i talk lenght, but i tried to be the clearest i can. Thanks in advance.

Answer 1

You would use the ViewModel approach for glueing your presentation logic with the View. You wouldn’t use it to represent an individual record n your example of binding to a data grid and it is usually used in WPF applications due to support for two-way bindings.

Having a flattened approach to represent multiple objects is probably not the best idea as a Customer can have multiple Orders, therefore when you modify a customer’s details as suggested in your question, you would then need to force a PropertyChanged to the other records with the same Customer. What you probably want to implement is a master-detail grid view in your application, where you can expand a Customer row to view their Orders.

If you want to continue with your approach with a flattened structure you should create a wrapper class to contain the entities loaded from the repository. You would then expose properties on the wrapper class referencing the value properties from the underlying entities. Example:

public class BindableRecord : INotifyPropertyChanged
{
    Order _OrderObject;
    internal Order OrderObject
    {
        get { return _OrderObject; }
        set
        {
            _OrderObject = value;
            _OrderObject.PropertyChanged += OrderObject_PropertyChanged;
        }
    }

    void OrderObject_PropertyChanged(object sender, PropertyChangedEventArgs e)
    {
        if (this.PropertyChanged != null)
            this.PropertyChanged(this, e);
    }

    internal Customer CustomerObject { get; set; }

    internal CustomerType CustomerTypeObject { get; set; }

    public string CustomerName
    {
        get { return this.CustomerObject.Name; }
        set { this.CustomerObject.Name = value; }
    }

    public string CustomerType
    {
        get { return this.CustomerTypeObject.ChCustomerType; }
        set { this.CustomerTypeObject.ChCustomerType = value; }
    }

    public int OrderID
    {
        get { return this.OrderObject.IdOrder; }
    }

    public event PropertyChangedEventHandler PropertyChanged;
}

The entities should derive from INotifyPropertyChanged so you can send notifications to your BindableRecord which can then be relayed to the DataGridView. An example of deriving your entities from INotifyPropertyChanged is available here: How to get property change notifications with EF 4.x DbContext generator

You would then have a Repository which returns the BindableRecord’s

public class OrderRepository
{
    public IList<BindableRecord> Get()
    {
        using (EntityContext ctx = new EntityContext())
        {
            return (from c in ctx.Customers
                    join o in ctx.Orders on c.Order equals o
                    join ct in ctx.CustomerTypes on c.CustomerType equals ct
                    select new BindableRecord() { CustomerObject = c, CustomerTypeObject = ct, OrderObject = o }).ToList();
        }
    }

    public void Save(IEnumerable<BindableRecord> addOrUpdateEntities, IEnumerable<BindableRecord> deletedEntities)
    {
        using (EntityContext ctx = new EntityContext())
        {
            foreach (var entity in addOrUpdateEntities)
            {
                ctx.Entry(entity.CustomerTypeObject).State = entity.CustomerTypeObject.IdCustomerType == default(int) ? EntityState.Added : EntityState.Modified;
                ctx.Entry(entity.CustomerObject).State = entity.CustomerObject.IdCustomer == default(int) ? EntityState.Added : EntityState.Modified;
                ctx.Entry(entity.OrderObject).State = entity.OrderObject.IdOrder == default(int) ? EntityState.Added : EntityState.Modified;
            }
            foreach (var entity in deletedEntities)
            {
                ctx.Entry(entity.CustomerTypeObject).State = EntityState.Deleted;
                ctx.Entry(entity.CustomerObject).State = EntityState.Deleted;
                ctx.Entry(entity.OrderObject).State = EntityState.Deleted;
            }
            ctx.SaveChanges();
        }
    }
}

The Save method attaches the entities and then updates their state based upon whether they have been modified, added or deleted. You will have to maintain two lists, one for modified and added entities (this will be bound to your data grid) and the other for deleted entities (this should be added to when a record from your data grid is deleted). This will however update every field on the DB for that record; if you only want to update certain fields you could make your DB context long-lived.

If you prefer not to maintain separate lists you can control the state of your entities by deriving your POCOs from the following interface. You can control the state of this property in your UI, and then read this property in your repository Save method instead of checking primary keys for default values and maintaining separate lists.

interface IEntity
{
    EntityState State { get; set;}
}

You would then call the Get method on the repository and then bind the list of records to the data grid. You would have to explicitly call the Save method on the repository whenever you wanted to persist the changes back to the DB.

I hope this helps.

Answer 2

In order to solve this problem i used the ViewModel solution proposed by @User978139 in a little more complex structure. In particular i've created the OrderViewModel that wrap the order model whose CustomerId and EmployeeId properties are Readable and Writeable. Note that in its setters i've added some code to force Customer and Employee properties to change and their respective PropertyChanged() event raises. This is the code:

 public class OrderViewModel : IEntityViewModel, IDataErrorInfo
        {
            public event PropertyChangedEventHandler PropertyChanged;

            internal Order _order;
            INorthwindDbContext _ctx;

            public int OrderId
            {
                get { return _order.OrderId; }
            }
            public DateTime? OrderDate
            {
                get { return _order.OrderDate; }
                set
                {
                    _order.OrderDate = value;
                    RaisePropertyChanged("OrderDate");
                }
            }
            public string ShipName
            {
                get { return _order.ShipName; }
                set
                {
                    _order.ShipName = value;
                    RaisePropertyChanged("ShipName");
                }
            }
            public string ShipAddress
            {
                get { return _order.ShipAddress; }
                set
                {
                    _order.ShipAddress = value;
                    RaisePropertyChanged("ShipAddress");
                }
            }
            public string ShipCity
            {
                get
                {
                    return _order.ShipCity;
                }
                set
                {
                    _order.ShipCity = value;
                    RaisePropertyChanged("ShipCity");
                    ;
                }
            }

            public string CustomerId
            {
                get { return _order.CustomerId; }
                set
                {
                    _order.CustomerId = value;
                    var customer = this._ctx.Customers.Find(_order.CustomerId);
                    _order.Customer = customer;
                    RaisePropertyChanged("CustomerId");
                    RaisePropertyChanged("CompanyName");
                    RaisePropertyChanged("ContactName");
                }
            }
            public string CompanyName
            {
                get { return _order.Customer != null? _order.Customer.CompanyName : string.Empty; }
            }
            public string ContactName
            {
                get { return _order.Customer != null ? _order.Customer.ContactName : string.Empty; }
            }

            public int? EmployeeId
            {
                get { return _order.EmployeeId; }
                set
                {
                    _order.EmployeeId = value;
                    var employee = this._ctx.Employees.Find(_order.EmployeeId);
                    _order.Employee = employee;
                    RaisePropertyChanged("EmployeeId");
                    RaisePropertyChanged("LastName");
                    RaisePropertyChanged("FirstName");
                    RaisePropertyChanged("Title");
                }
            }
            public string LastName
            {
                get { return _order.Employee!=null? _order.Employee.LastName : string.Empty; }
            }
            public string FirstName
            {
                get { return _order.Employee!=null? _order.Employee.FirstName : string.Empty;  }
            }
            public string Title
            {
                get { return _order.Employee!=null? _order.Employee.Title : string.Empty;  }
            }

            public OrderViewModel(INorthwindDbContext ctx)
            {
                this.Init(new Order(), ctx);
            }

            public OrderViewModel(Order order, INorthwindDbContext ctx)
            {
                this.Init(order, ctx);
            }

            private void Init(Order order, INorthwindDbContext ctx)
            {
                this._order = order;
                this._ctx = ctx;
            }

            private void RaisePropertyChanged(string propname)
            {
                if (PropertyChanged != null)
                    PropertyChanged.Invoke(this, new PropertyChangedEventArgs(propname));
            }

            public void NotifyChanges()
            {
                foreach (var prop in typeof(OrderViewModel).GetProperties(BindingFlags.Public | BindingFlags.Instance).Where(x => x.CanRead))
                    RaisePropertyChanged(prop.Name);
            }



            public string Error
            {
                get { return null; }
            }

            public string this[string columnName]
            {
                get {
                    switch (columnName)
                    {
                        case "CustomerId":
                            if (_ctx.Customers.Find(CustomerId)==null)
                                return "Invalid customer";
                            break;

                        case "EmployeeId":
                            if (_ctx.Employees.Find(EmployeeId)==null)
                                return "Invalid employee";
                            break;


                    }
                    return null;
                }
            }

        } 

Then i've wrapped the "Local" property exposed by my OrderRepository (returns in-memory loaded and added entities) with a custom BindingList implementation (BindingViewModelList), that i use for the grid binding, that sync itself with the internal repository.Local property (that's an ObservableCollection<>) and vice versa. So, if you add things in the ObservableColleciton you'll see the changes in the BindingList too, otherwise, if you add something to the BindingList (for example with the grid that is binded to), the changes are reflected to the ObservableCollection too. In this way i don't have to track down the state for ViewModels objects and reflect changes manually to the repository that is a really painfull job. With this solution it's like the Local property of the repository is directly tied to the binding list that i use for binding. Adding and removing record from the grid consist of entity removal either from BindingList and Local property. So if i call SaveChanges() all changes are persisted to DB without explicit sync.

I hope there was a better clean solution to map Models collections to ViewModels collections, but the proposed solution works well. This is the code of my BindingViewModelList:

public class BindingViewModelList<TViewModel, TModel> : BindingList<TViewModel>
{
    private readonly ObservableCollection<TModel> _source;
    private readonly Func<TModel> _modelFactory;
    private readonly Func<TModel, TViewModel> _viewModelFactory;
    private readonly Func<TViewModel, TModel> _getWrappedModel;

    private bool isSync = false;

    public BindingViewModelList(ObservableCollection<TModel> source, Func<TModel, TViewModel> viewModelFactory, Func<TModel> modelFactory, Func<TViewModel, TModel> getWrappedModel)
        : base(source.Select(model => viewModelFactory(model)).ToList())
    {
        Contract.Requires(source != null);
        Contract.Requires(viewModelFactory != null);

        this._source = source;
        this._modelFactory = modelFactory;
        this._viewModelFactory = viewModelFactory;
        this._getWrappedModel = getWrappedModel;

        this._source.CollectionChanged += OnSourceCollectionChanged;
        this.AddingNew += BindingViewModelList_AddingNew;
    }


    protected virtual TModel CreateModel()
    {
        return _modelFactory.Invoke(); 
    }

    protected virtual TViewModel CreateViewModel(TModel model)
    {
        return _viewModelFactory(model);
    }


    private void BeginSync()
    {
        this.isSync = true;
    }

    private void EndSync()
    {
        this.isSync = false;
    }


    void BindingViewModelList_AddingNew(object sender, AddingNewEventArgs e)
    {
        e.NewObject = CreateViewModel(CreateModel());
    }


    protected override void OnListChanged(ListChangedEventArgs e)
    {
        if (!this.isSync)
        {

            if (e.NewIndex >= 0 && e.NewIndex < this.Count)
            {
                bool ok = true;
                TViewModel item = default(TViewModel);
                try
                {
                    item = this[e.NewIndex];
                }
                catch (IndexOutOfRangeException ex)
                {
                    ok = false;
                }

                if (ok)
                {
                    switch (e.ListChangedType)
                    {
                        case ListChangedType.ItemMoved:
                            BeginSync();
                            this._source.Move(e.OldIndex, e.NewIndex);
                            EndSync(); 
                            break;

                        case ListChangedType.ItemAdded:
                            TModel modelAdded = _getWrappedModel(item);
                            BeginSync();
                            this._source.Add(modelAdded);
                            EndSync();
                            break;

                        case ListChangedType.ItemChanged:
                            //TModel modelChanged = _getWrappedModel(item);
                            //BeginSync();
                            //this._source[e.NewIndex] = modelChanged; 
                            //EndSync(); 
                            break;

                        case ListChangedType.Reset:
                            BeginSync();
                            this._source.Clear();
                            for (int i = 0; i < this.Count; i++)
                            {
                                TModel model = _getWrappedModel(this[i]);
                                this._source.Add(model);
                            }
                            EndSync();
                            break;
                    }
                }
            }
        }


        base.OnListChanged(e);
    }

    protected override void RemoveItem(int index)
    {
        if (!isSync) {
            TModel model = _getWrappedModel(this[index]);
            BeginSync();
            this._source.Remove(model);
            EndSync();
        }

        base.RemoveItem(index);
    }


    private void OnSourceCollectionChanged(object sender, NotifyCollectionChangedEventArgs e)
    {
        if (isSync) return;

        switch (e.Action)
        {
            case NotifyCollectionChangedAction.Add:
                BeginSync();
                for (int i = 0; i < e.NewItems.Count; i++)
                {
                    this.Insert(e.NewStartingIndex + i, CreateViewModel((TModel)e.NewItems[i]));
                }
                EndSync();
                break;

            case NotifyCollectionChangedAction.Move:
                if (e.OldItems.Count == 1)
                {
                    BeginSync();
                    this.Swap(e.OldStartingIndex, e.NewStartingIndex);
                    EndSync();
                }
                else
                {
                    List<TViewModel> items = this.Skip(e.OldStartingIndex).Take(e.OldItems.Count).ToList();
                    BeginSync();
                    for (int i = 0; i < e.OldItems.Count; i++)
                        this.RemoveAt(e.OldStartingIndex);

                    for (int i = 0; i < items.Count; i++)
                        this.Insert(e.NewStartingIndex + i, items[i]);
                    EndSync();
                }
                break;

            case NotifyCollectionChangedAction.Remove:
                BeginSync();
                for (int i = 0; i < e.OldItems.Count; i++)
                    this.RemoveAt(e.OldStartingIndex);
                EndSync();
                break;


            case NotifyCollectionChangedAction.Replace:
                // remove
                BeginSync();
                for (int i = 0; i < e.OldItems.Count; i++)
                    this.RemoveAt(e.OldStartingIndex);

                // add
                for (int i = 0; i < e.NewItems.Count; i++)
                    this.Insert(e.NewStartingIndex + i, CreateViewModel((TModel)e.NewItems[i]));
                EndSync();
                break;

            case NotifyCollectionChangedAction.Reset:
                BeginSync(); 
                Clear();
                for (int i = 0; i < e.NewItems.Count; i++)
                    this.Add(CreateViewModel((TModel)e.NewItems[i]));
                EndSync();
                break;

            default:
                break;
        }
    }



    public void Swap(int first, int second)
    {
        TViewModel temp = this[first];
        this[first] = this[second];
        this[second] = temp;
    }
}

You just have to provide the ViewModel type, the Model type, the Model factory method, the ViewModel factory method and a method that specifies how to get the wrapped model from a viewmodel object. This is an example:

Func<Order> modelCreator = () => new Order();
Func<Order, OrderViewModel> viewModelCreator = (model => new OrderViewModel(model, _ctx));
Func<OrderViewModel, Order> modelGetter = (viewModel => viewModel._order);
var _viewModelCollection = new BindingViewModelList<OrderViewModel, Order>(_ctx.Orders.Local, viewModelCreator, modelCreator, modelGetter);

It is not a fairly simple solution but as i said, it works well, so i mark this thread as answered. Hope that someone can find a better solution..