none
How to use entities with DataGridView in visual basic

    Question

  • I'm obviously missing something simple, but how do I display data from an entity framwork model in a DataGridView? Nothing I have tried seems to work!

    Tuesday, February 07, 2012 5:39 PM

Answers

  • Hi markev;

    Just assign the results of the Linq query to the DataSource property of the DataGridView control.

    Dim query = From ...
    DataGridView1.DataSource = query.ToList()

    If it is not working for you please post your code.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by markev Wednesday, February 08, 2012 12:40 PM
    Tuesday, February 07, 2012 6:50 PM

All replies

  • Hi markev;

    Just assign the results of the Linq query to the DataSource property of the DataGridView control.

    Dim query = From ...
    DataGridView1.DataSource = query.ToList()

    If it is not working for you please post your code.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by markev Wednesday, February 08, 2012 12:40 PM
    Tuesday, February 07, 2012 6:50 PM
  • Thanks - that sorted it (I've tried so many things I've seen I don't know how I missed that)

    If I want to use the DataGridView to input data how should I do that?

    Mark

    Wednesday, February 08, 2012 12:37 PM
  • Hi markev;

    What I would do is to assign the query results to a BindingSource control and then assign the BindingSource control to the DataGridView control. This will allow the BindingSource control to keep the underlaying entities up to date. Then when all CRUD operations are done you will need to execute a SaveChanges to the ObjectContext so that the changes are sent to the database.

    Dim query = From ...
    Dim bs As New BindingSource()
    bs.DataSource = query
    DataGridView1.DataSource = bs


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Wednesday, February 08, 2012 3:05 PM
  • Thanks for the help, however when I set bs.DataSource = query the program throws the exception below

    A first chance exception of type 'System.NotSupportedException' occurred in EntityFramework.dll

     My code is currently

     Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load
    
            db = New EFContext
            Using db
                Dim item As Item = New Item With {.ItemTitle = "Test"}
                db.Items.Add(item)
                Dim itemQuery = (From d In db.Items Select d)
                Dim bs As New BindingSource()
                bs.DataSource = itemQuery
                'DataGridView1.DataSource = itemQuery.ToList'
    
                DataGridView1.DataSource = bs
    
            End Using
        End Sub

    Wednesday, February 08, 2012 7:27 PM
  • Assuming your declairing items as new list(of t)

    db = New EFContext Using db Dim item As Item = New Item With {.ItemTitle = "Test"} db.Items.Add(item) Dim itemQuery = (From d In db.Items Select d).tolist <---add this to the query DataGridView1.DataSource = itemquery End Using

    Thursday, February 09, 2012 1:48 PM
  • The code you posted should have worked. Can you tell me on what line of code did it throw the exception on?


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Thursday, February 09, 2012 4:14 PM
  • Hi

    Sorry my previous post was not well worded. The exception occoured on the line

    bs.DataSource = itemQuery

    I don't know if its related, but I also tried following a blog by Julie Lerman http://thedatafarm.com/LearnEntityFramework/tutorials/use-an-entity-framework-entity-as-a-winforms-data-source/ but that failed when I tried to drag the items object from the datasource window to the form with the following error.

    An error occurred while performing the drop:

    BindingSource unable to create list based on the Type specified in the DataSource property.

    Also don't know if it's relevant, but I am using EF 4.1 and visual basic 2010 express. I can't update to EF4.2 as the nugget tool isn't available for VB express.

    Many thanks for your help.

    Mark

    Thursday, February 09, 2012 7:28 PM
  • Hi Mark;

    I have no clue why it is throwing an exception on that line of code. If you can zip the project up with the test database and upload both to somewhere on the net so that I can download and have a look I will try and find the issue. If you have a Windows Live account you can upload to your SkyDrive at http://skydrive.live.com/.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Thursday, February 09, 2012 8:01 PM
  • Hi Fernando

    I have uploaded the file to skydrive -

    https://skydrive.live.com/redir.aspx?cid=c8f782ed6d38cb27&resid=C8F782ED6D38CB27!229&parid=C8F782ED6D38CB27!228&authkey=!AMfbOKy2BRocEQo

    I haven't uploaded a database as EF creates the db on the fly.

    Mark

    Friday, February 10, 2012 10:39 AM
  • Hi Mark;

    The error message that is returned from the exception 'System.NotSupportedException' is as follows :

    "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. For WPF bind to DbSet.Local. For WinForms bind to DbSet.Local.ToBindingList()."

    The issue is that a Linq query when NOT enumerated is NOT holding a result set but the query itself. To resolve the issue you need to enumerate the query by adding the method ToList() at the end of the query as follows:

    Dim itemQuery = (From d In db.Items Select d).ToList()

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Friday, February 10, 2012 1:45 PM
  • Hi Fernando

    Thanks for your help that's brilliant. Just got to sort the rest of the program logic!

    Mark

    Friday, February 10, 2012 2:44 PM
  • Hi Fernando,

    In EF4 there seemed to be no problem using a query as a datasource. Now in EF5 there seems to be a problem all of a sudden. Even more annoying is that I can use a query against a WCF service (DataServiceContext) as a datasource, but I can't achieve the same if I'm using EF5 DbContext.

    The following code works just fine:

    Uri endpoint = new Uri("http://localhost:21788/WcfDataService1.svc");
    ServiceReference1.Entities ent = new ServiceReference1.Entities(endpoint);
    var q = from log in ent.IMMS_ACTIVITY_LOG
            where log.CREATED_DATE >= new DateTime(2012, 7, 16)
            select log;
    entitiesBindingSource.DataSource = q;

    This code does not:

    using (ProtoOracleAccess.OcsEntities c = new ProtoOracleAccess.OcsEntities())
    {
       var oqery = from w in c.Weighbridges
                   select w;
       bindingSource1.DataSource = oqery; // Runtime error here obviously!
    }
    

    It does not make sense to me as to why the query can't implement IListsource.GetList. Surely, if we have to call ToList, then DbQuery can implement GetList? I find this really strange and would love to know the reasoning behind the teams explicit non support of this.


    dayneo http://blogs.dayneo.co.za

    Wednesday, October 10, 2012 3:04 PM
  • @dayneo;

    I wish I could give you an answer to your question but I don't work for Microsoft and not privy to any of that information, sorry.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Wednesday, October 10, 2012 7:38 PM
  • Thank you so much  - I was almost giving up programming and looking for a job as a waiter :-) The ToList() really does the trick.

    What I really think is amazing: This is the first place I found this solution (and I'm looking for 3 days now!!!)

    Thursday, April 24, 2014 12:34 PM