Webforms data binding with EF Code-First Linq query error

Question

In this example here, Scott shows doing a Linq query against the dbContext and binding the result directly to a GridView to show a list of products. His example is using the CTP4 version of the Code First stuff.

However, when I try do do the same thing using the latest version of EntityFramework 4.1, I get the following error:

Data binding directly to a store query (DbSet, DbQuery, DbSqlQuery) is not supported. Instead populate a DbSet with data, for example by calling Load on the DbSet, and then bind to local data.

I see that the DBQuery object is throwing this error on purpose in its implementation of IListSource.GetList(), which is used in databinding.

Any ideas why his example works? By the way, I know that I can make this work by putting in a projects.ToList(). My main question is whether something changed in the release version that makes this type of thing no longer work, or whether I'm missing something somewhere that can work around this error.

Just for reference, I'm referring to code like this:

MyDbContext db = new MyDbContext();

var projects = from p in db.Projects
               where p.AnotherField == 2
               select p;

grdTest.DataSource = projects;
grdTest.DataBind();

Harmonica

I don't know if anything changed but you should call ToList anyway because context is disposable and you want to dispose it once you don't need it. Many times this happens before the page is rendered (and query executed => exception). ToList will ensure that your query is executed when the context exists. – Stephi

While what you said is good general advice, this specific question is about the error above and how Scott's examples were working. The error I have is not related to a missing context b/c it is being thrown when the call to IListSource.GetList() is called on the DBQuery object during databinding. – Harmonica

Yes it is not the answer to your question. That is why I posted it as a comment. – Stephi

Answer 1

It is a long story, but I will try not to make it boring.

From the first version of EF we supported binding directly to queries. We earned enough experience about the pitfalls and confusion that this generated that we decided to explicitly disable it the new API we created for EF 4.1. The main issue for me was that WinForms and WPF data binding infrastructure assumes data sources are in memory and inexpensive to access. This has resulted in data binding often asking for the binding list more than once. On EF, binding to a reusable query necessarily implied wanting the latest results from the database, therefore we made it so that everytime the binding list is asked for we re-execute the query against the database. This caused at least two query executions everytime anyone bound to a query.

There were a few other aspects of binding to queries that were quite confusing or counterintuitive for many customers. I explore how things used to work in this blog post: http://blogs.msdn.com/b/diego/archive/2008/10/09/quick-tips-for-entity-framework-databinding.aspx

What you are supposed to do with DbContext API is to bind to local data directly and not to queries. For that we expose DbSet.Local which is an ObservableCollection that works pretty well for WPF and the ToBindingList method that wraps the collection in a BindingList for easier consumption in WinForms.

I can see that the exception message could be more explicit about the existence of the local property. I will consider filing a bug for that.

Hope this helps

Lachrymal

Thanks! How does this affect web scenarios, should we simply call .ToList() or .Local as well, I assume? – Fistic

Just an FYI, the full exception message is explicit about the Local property: "Data binding directly to a store query (DbSet, DbQuery, DbSqlQuery, DbRawSqlQuery) is not supported. Instead populate a DbSet with data, for example by calling Load on the DbSet, and then bind to local data. For WPF bind to DbSet.Local. For WinForms bind to DbSet.Local.ToBindingList()." – Brahmin

Thanks for the explanation. Pretty well with WPF. But how about poeple who works with winform? The exception suggests a solution for WPF even working with winforms. I had to do it the hard way when adding and deleting new rows. See this post: link – Bohol

Did you use the ToBindnigList() extension method (make sure you import system.Data.Entity namespace to see it). There are more details in the documentation: msdn.microsoft.com/en-US/data/jj592872. – Lachrymal

Are there suggestions for working with large datasets for paging on web pages since not practical to load all the data? – Tiro

Answer 2

Came across the same issue and found this topic. ToList() worked:

using (NorthwindContext context = new NorthwindContext())
{
    var products = from p in context.Products
                   where p.Discontinued == false
                   select p;

    gridView.DataSource = products.ToList();
    gridView.DataBind();
}

Foozle

As you'll see in the question, I said "I know I can make this work by putting in a products.ToList()". – Harmonica

Answer 3

In looking at the EF4 Feature CTP4 release dll in Reflector, I can see that its DBQuery object does not implement IListSource.GetList() and throw an exception as the EF 4.1 dll does. I guess somewhere along the line they had a reason to no longer allow binding directly to the query, even though it implements IEnumerable.

This does not answer WHY they made this change, but at least I can see that there is a reason it would work in the older version.

Harmonica