'System.Data.Entity.Core.EntityCommandExecutionException' in Linq Query

Question

I am developing my first WPF browser application.

I load invoices in a dataGrid then I filter with textBox or comboBox.

Because it takes few seconds to load, I'am trying to put a loading animation according the following example:

here

It doesn't work the first time I navigate to the page. My dataGrid remains empty. When I debug I have this following error which happens on my query in Get() function.

'System.Data.Entity.Core.EntityCommandExecutionException' occurred in mscorlib.dll but was not handled in user code

But the this query used to work well before I made the changes for the animation. So maybe the problem doesn't come from the query.

Exception:Thrown: "Connection must be valid and open." (System.InvalidOperationException) A System.InvalidOperationException was thrown: "Connection must be valid and open." Time: 11/20/2015 12:36:31 PM Thread:Worker Thread[13324]

public class ConsultInvoiceViewModel : ViewModelBase
{

    public Context ctx = new Context();

    private ICollectionView _dataGridCollection;
    private string _filterString;
    private ObservableCollection<Invoice> invoiceCollection;


    public ConsultInvoiceViewModel()
    {
        if (!WPFHelper.IsInDesignMode)
        {
            var tsk = Task.Factory.StartNew(InitialStart);
            tsk.ContinueWith(t => { MessageBox.Show(t.Exception.InnerException.Message); }, CancellationToken.None, TaskContinuationOptions.OnlyOnFaulted, TaskScheduler.FromCurrentSynchronizationContext());
        }
    }

    private void InitialStart()
    {
        try
        {
            State = StateEnum.Busy;
            DataGridCollection = CollectionViewSource.GetDefaultView(Get());
            DataGridCollection.Filter = new Predicate<object>(Filter);
            GetShop(); //load one comboBox
            GetSupplier(); //load one comboBox
        }
        finally
        {
            State = StateEnum.Idle;
        }

    }

    private ObservableCollection<Invoice> Get()
    {
        DateTime date2 = DateTime.Now.AddMonths(-2);

        var query = ctx.Invoices
                    .GroupBy(x => new { x.suppInvNumber, x.shop1, x.date, x.foodSupplier })
                    .ToList()
                    .Select(i => new Invoice
                    {
                        suppInvNumber = i.Key.suppInvNumber,
                        shop1 = i.Key.shop1,
                        date = i.Key.date,
                        foodSupplier = i.Key.foodSupplier,
                        totalPrice = i.Sum(t => t.totalPrice),
                    })
                    .Where(d => d.date >= date2)
                    .OrderByDescending(d => d.date)
                    .AsQueryable();

        invoiceCollection = new ObservableCollection<Invoice>(query);

        return invoiceCollection;
    }

    public ICollectionView DataGridCollection
    {
        get
        { 
            return _dataGridCollection; 
        }
        set 
        { 
            _dataGridCollection = value; 
            OnPropertyChanged("DataGridCollection"); }
    }

    public string FilterString
    {
        get 
        { 
            return _filterString; 
        }
        set 
        {
            _filterString = value;
            OnPropertyChanged("FilterString");
            FilterCollection();
        }
    }


    public static readonly PropertyChangedEventArgs StateArgs = ViewModelBase.CreateArgs<ConsultInvoiceViewModel>(c => c.State);
    private StateEnum _State;

    public StateEnum State
    {
        get
        {
            return _State;
        }
        set
        {
            var oldValue = State;
            _State = value;
            if (oldValue != value)
            {
                OnStateChanged(oldValue, value);
                OnPropertyChanged(StateArgs);
            }
        }
    }

    protected virtual void OnStateChanged(StateEnum oldValue, StateEnum newValue)
    {
    }



    private void FilterCollection()
    {
        if (_dataGridCollection != null)
        {
            _dataGridCollection.Refresh();
        }
    }

    private bool Filter(object obj)
    {
        var data = obj as Invoice;

        if (data != null)
        {
            if (!string.IsNullOrEmpty(_filterString))
            {
                return data.suppInvNumber.Contains(_filterString);

            }
            return true;
        }
        return false;
    }

    private void SearchFilter()
    {
        IOrderedEnumerable<Invoice> invs;
        invs = ctx.Invoices
                   .Where(s => s.shop == Shop && s.supplier == Supplier && s.date >= From && s.date <= To)
                   .GroupBy(x => new {x.suppInvNumber, x.shop1, x.date, x.foodSupplier })
                   .ToList()
                   .Select(i => new Invoice
                   {
                       suppInvNumber = i.Key.suppInvNumber,
                       shop1 = i.Key.shop1,
                       date = i.Key.date,
                       foodSupplier = i.Key.foodSupplier,
                       totalPrice = i.Sum(t => t.totalPrice),
                   })
                   .OrderByDescending(d => d.date);
        }

        invoiceCollection.Clear();
        if (invs != null)
           foreach (var inv in invs)
           {
               invoiceCollection.Add(inv);
           }     
        FilterCollection();   
    }

    #region combobox
    private void GetShop()
    {
        ctx.shops.ToList().ForEach(shop => ctx.shops.Local.Add(shop));
        SShop = ctx.shops.Local;
    }

    private void GetSupplier()
    {
        ctx.foodSuppliers.ToList().ForEach(supplier => ctx.foodSuppliers.Local.Add(supplier));
        FoodSupplier = ctx.foodSuppliers.Local;
    }


    private IList<foodSupplier> supplier;

    public IList<foodSupplier> FoodSupplier
    {
        get
        {
            if (supplier == null)
            GetSupplier();
            return supplier;
        }
        set
        {
            supplier = value;
            OnPropertyChanged("FoodSupplier");
        }
    }

    private IList<shop> shop;

    public IList<shop> SShop
    {
        get
        {
            return shop;
        }
        set
        {
            shop = value;
            OnPropertyChanged("SShop");
        }
    }


    private int _shop;

    public int Shop
    {
        get
        {
            return _shop;
        }
        set
        {
            _shop = value;
            OnPropertyChanged("Shop");
            SearchFilter();
        }
    }

    private int _supplier;

    public int Supplier
    {
        get
        {
            return _supplier;
        }
        set
        {
            _supplier = value;
            OnPropertyChanged("Supplier");
            SearchFilter();
        }
    }

    #endregion

    #region "Command"

    private ICommand searchCommand;

    public ICommand SearchCommand
    {
        get
        {
            return searchCommand ?? (searchCommand = new RelayCommand(p => this.Search(), p => this.CanSearch()));
        }
    }

    private bool CanSearch()
    {
       return true;
    }

    #endregion
}

Answer 1

The exception you are getting indicates an error connecting to the database. It's hard to diagnose this because of the way you keep a single Context reference for the life of the application. That connection could be failing at any point.

Try wrapping your data access in a new Context for each logical operation like this. Keeping one Context around for the life of an application is an Anti-pattern that can lead to all kinds of errors, especially when trying to do things in the background.

  private ObservableCollection<Invoice> Get()
    {
        using (var ctx = new Context())
        {
        DateTime date2 = DateTime.Now.AddMonths(-2);

        var query = ctx.Invoices
                    .GroupBy(x => new { x.suppInvNumber, x.shop1, x.date, x.foodSupplier })
                    .ToList()
                    .Select(i => new Invoice
                    {
                        suppInvNumber = i.Key.suppInvNumber,
                        shop1 = i.Key.shop1,
                        date = i.Key.date,
                        foodSupplier = i.Key.foodSupplier,
                        totalPrice = i.Sum(t => t.totalPrice),
                    })
                    .Where(d => d.date >= date2)
                    .OrderByDescending(d => d.date)
                    .AsQueryable();

        invoiceCollection = new ObservableCollection<Invoice>(query);
        }
        return invoiceCollection;
    }