[RESOLVED] Setting a ComboBox DataSource using Entity Framework table column

[cory_jackson]

I have a simple table on a SQL server named Counties with columns ID, Name, and State. I added an ADO.NET Entity Data Model named Model1 and using the wizard created the model from the Db. The Db was named BD and I've done it twice so the context is currently BDEntites2. I have a ComboBox that I'm trying to bind to the Name column. Later I want to make it conditional but for the moment I'm just going to do this. Now I read that EF queries support iListEnermable so I should be able to link them directly but that didn't work. I saw online many examples where a LINQ expression was used and I tried my best to emulate this given later i want to make it conditional but I can't get it to work either.

Private Sub Form1_Load() Handles MyBase.Load
 
    cbCounty.DataSource = From x In eBD.Counties
                          Where x.State = "MT"
                          Select x.CountyName
    cbCounty.ValueMember = "CountyName"
    cbCounty.DisplayMember = "CountyName"
 
End Sub

The form doesn't even load this way and I get an exception if I put this in a button. What am I doing wrong?

[dday9]

What is the exception that is being thrown?

[cory_jackson]

An unhandled exception of type 'System.NotSupportedException' occurred in EntityFramework.dll

Additional information: 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(). For ASP.NET WebForms you can bind to the result of calling ToList() on the query or use Model Binding, for more information see http://go.microsoft.com/fwlink/?LinkId=389592.

I tried "Select x" as well. I'm trying to follow the exception message now.

[dday9]

You know what I realized, you're trying to select just the CountyName. What you need to do is return just the IQueryable(Of CountyName) and set the DisplayMember to CountyName, also there's no need to set the ValueMember if its the same as the DisplayMember:

cbCounty.DataSource = From x In eBD.Counties Where x.State = "MT"
cbCounty.DisplayMember = "CountyName"

[cory_jackson]

No exception now but also no items apparent in the list.

Just tried "cbCounty.DataSource = eBD.Counties" too.

[cory_jackson]

I think EF has the ability to work on the remote SQL data or local data. This could make sense. I'm reading on the DbSet.Local property now.

When I first started with DataSets we had to load a local table and any changes to that were later evaluated in Update and the changes were pushed back line by line. But it's looking like EF can work on a local DataSet like this and also the SQL server directly. This could be really cool for me if it does. I need to learn more.

[dday9]

When you initially declare a new instance of your entity, setup a breakpoint and see how many items are in eBD.Counties

[cory_jackson]

When I look at my DataSources window I don't see anything. I was assuming that is normal with EF. Maybe I was incorrect?

[cory_jackson]

Interesting. When I look at eBA.Counties I see two branches. One are SQL expressions and the other is "Local". And in Local there is one parameter and it's "Count=0".

[kareninstructor]

Looking at the query it's returning IQueryable(String) so there is no CountryName, add .ToList and you now have a List of strings.

[cory_jackson]

I tried adding ToList a couple places with no luck. Where do I add it?

Bear in mind I could create a list but I want something dynamic so that when I change the state value in another ComboBox the list changes in CountyName.

[kareninstructor]

In this case there is no properties as you are dealing with a string. You would access the current item via cb.County.Text, thinking drop down style is DropDownList.

Private Sub Form1_Load() Handles MyBase.Load
    cbCounty.DataSource = (From x In eBD.Counties  Where x.State = "MT" Select x.CountyName).ToList()
End Sub

The only time you would use Display and Value member is if you were selecting a row per-say e.g.

Private Sub Form1_Load() Handles MyBase.Load
    cbCounty.DataSource = (From x In eBD.Counties  Where x.State = "MT" Select x).ToList()
End Sub

[cory_jackson]

That works great!

BTW I don't need to modify the data used in this control. Perhaps it's a bad practice to do it this way. What do you think?

I'm not sure how to do the next step where I want the state ComboBox to filter the county. I'll do some research now but any advice would be welcome. I see a few examples where they create a local DataTable. I sure wish I understood this deal with EF being local or not. Time to study more.

[cory_jackson]

I read another post here where John explained ValueMemeber and DisplayMemeber in a way I could understand. Now it makes sense why I don't need them. I thought perhaps there was no need for DataSource either so I looked at using just items. But I'd have to clear it each time and so using the DataSource seems simpler. What I have is working well but I wonder if it's best way. Any comments? Is this the MS 'best practice' for this?

Private Sub Form1_Load() Handles MyBase.Load
    cbState.DataSource = (From x In eBD.Counties Select x.State).Distinct.ToList
End Sub
 
Private Sub cbState_SelectedIndexChanged() Handles cbState.SelectedIndexChanged
    cbCounty.DataSource = (From x In eBD.Counties Where x.State = cbState.SelectedItem.ToString Select x.Name).ToList
End Sub

PS I just realized I change the column name from "CountyName" to "Name".