none
Query result is readonly when I select fields RRS feed

  • Question

  • I am new to Linq and I must missunderstand something of the concept of Linq. Working with Northwind, when I do:

     

            Dim orders = _
            From o In db.Orders
            Where o.CustomerID.Contains("A") _
            Select o

     

    With this code I am able to change fields and update with db.SubmitChanges() sucessfully.

    I can also bind it to a DataGridView, edit, update. That's really cool.

     

    But when I do:

            Dim orders = _
            From o In db.Orders
            Where o.CustomerID.Contains("A") _
            Select o.CutomerID, o.CompanyName

    The returned Fields become ReadOnly. Why is that? Would'nt it be cool to be able to bind this to a DataGridView, edit and update? Is there any easy way to do this?

     

    Similar if I do:

     

      Dim orders = _
          From o In db.Orders
           Where o.CustomerID.Contains("A") _
          Select New CustomerDatagrid With { _
              .CustomerID = d.CustomerID, _
              .CompanyName = d.Customers.CompanyName}

     

    With this query and the generated custom class I am able to bind and edit, but no changes are saved with db.SubmitChanges(). While it is very easy to constuct, bind and display a new custom class - why dos the connections to the datasource gets lost?

    After two days of reading, googeling and thinking I need your help!!

     

    Leo

     

     


    • Edited by Leo Vetterli Sunday, September 25, 2011 4:54 PM typo..
    Sunday, September 25, 2011 4:51 PM

Answers

  • Hi Leo;

    To your question, "•How can I return a Read/Write Type then? For example when I query with "Select o.CutomerID, o.CompanyName"?", The only way to return an Anonymous type that is Read/Write is to define it as follows :

    Select New With { .CustomerID = o.CutomerID, .CompanyName = o.CompanyName }

    To your question, "•Let's say I want to display all Orders. Since I get all the Customer-Object in the Orders-result (Tables with relations on CustomerID), it is very convenient to display Orders AND the for example Customer Name in one DataGridview. To do this I have to use a custom class. How do I return a Read/Write Type? ", Something like the following code snippet will do what you would like to do.

    Dim ctx As DataClasses1DataContext = New DataClasses1DataContext()
    
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    
        Dim dlo As DataLoadOptions = New DataLoadOptions()
        dlo.LoadWith(Of Order)(Function(c As Order) c.Customer)
        ctx.LoadOptions = dlo
    
        ' Get the Order Information to display
        Dim ord = From o In ctx.Orders
                  Select o
    
        With DataGridView1
            ' Suspend DataGridView Layout while we load the controls
            .SuspendLayout()
            ' Add a unbounded column to hold the CompanyName field from Customer
            .Columns.Add("CompanyName", "Company Name")
            ' Load the resultset from the query
            .DataSource = ord
            ' Hide the columns you do not want to display
            .Columns("OrderID").Visible = False
            .Columns("CustomerID").Visible = False
            .Columns("Customer").Visible = False
            ' Update the the row collection with the CompanyName
            UpDateCompanyName()
            ' Resume DataGridView Layout
            .ResumeLayout()
        End With
    
    End Sub
    
    Private Sub UpDateCompanyName()
        For Each row As DataGridViewRow In DataGridView1.Rows
            row.Cells("CompanyName").Value = CType(row.Cells("Customer").Value, Customer).CompanyName
        Next
    End Sub
    
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        ' Submit the changes made to the Order table
        ctx.SubmitChanges()
    End Sub
    

    To your question, "Why is the reason behind this design Microsoft choose? Results with complete Tables are connected, partial Tables not?" Well one reason would be that the partial Tables returned may or may NOT have the primary key of the record which is needed if an update is to be done. Another reason would be with concurrency issues.

     

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by Leo Vetterli Tuesday, September 27, 2011 7:52 AM
    Monday, September 26, 2011 6:27 PM

All replies

  • Hi Leo;

    In Visual Basic there are two types of Anonymous type, one being a return set of ReadOnly and the other of Read/Write. The one using the New With keywords is of the Read/Write type and the reason why in the first case you were not able to modify the values.

    To your question, "With this query and the generated custom class I am able to bind and edit, but no changes are saved with db.SubmitChanges(). While it is very easy to constuct, bind and display a new custom class - why dos the connections to the datasource gets lost?", It is NOT that the connection gets lost but that the result set from the query is a collection of Anonymous type and NOT of Orders and therefore are NOT tracked by the DataContext. If you need to update the database you should return a collections of Orders and then remove those columns from bing displayed with something like the following.

    DataGridView1.DataSource = ord
    DataGridView1.Columns.Remove("OrderID")
    DataGridView1.Columns.Remove("CustomerID")

    The the Orders will be tracked by the DataContext and then db.SubmitChanges() should work.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Sunday, September 25, 2011 6:47 PM
  • Hi Fernadoo

    Thanks for your answer.

    • How can I return a Read/Write Type then? For example when I query with "Select o.CutomerID, o.CompanyName"?

     

    Regarding your suggestion filling a DataGridView and removing Columns:

    • Let's say I want to display all Orders. Since I get all the Customer-Object in the Orders-result (Tables with relations on CustomerID), it is very convenient to display Orders AND the for example Customer Name in one DataGridview. To do this I have to use a custom class. How do I return a Read/Write Type?

     

    Why is the reason behind this design Microsoft choose? Results with complete Tables are connected, partial Tables not?

     

    Leo

    Sunday, September 25, 2011 7:08 PM
  • Hi Leo;

    To your question, "•How can I return a Read/Write Type then? For example when I query with "Select o.CutomerID, o.CompanyName"?", The only way to return an Anonymous type that is Read/Write is to define it as follows :

    Select New With { .CustomerID = o.CutomerID, .CompanyName = o.CompanyName }

    To your question, "•Let's say I want to display all Orders. Since I get all the Customer-Object in the Orders-result (Tables with relations on CustomerID), it is very convenient to display Orders AND the for example Customer Name in one DataGridview. To do this I have to use a custom class. How do I return a Read/Write Type? ", Something like the following code snippet will do what you would like to do.

    Dim ctx As DataClasses1DataContext = New DataClasses1DataContext()
    
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    
        Dim dlo As DataLoadOptions = New DataLoadOptions()
        dlo.LoadWith(Of Order)(Function(c As Order) c.Customer)
        ctx.LoadOptions = dlo
    
        ' Get the Order Information to display
        Dim ord = From o In ctx.Orders
                  Select o
    
        With DataGridView1
            ' Suspend DataGridView Layout while we load the controls
            .SuspendLayout()
            ' Add a unbounded column to hold the CompanyName field from Customer
            .Columns.Add("CompanyName", "Company Name")
            ' Load the resultset from the query
            .DataSource = ord
            ' Hide the columns you do not want to display
            .Columns("OrderID").Visible = False
            .Columns("CustomerID").Visible = False
            .Columns("Customer").Visible = False
            ' Update the the row collection with the CompanyName
            UpDateCompanyName()
            ' Resume DataGridView Layout
            .ResumeLayout()
        End With
    
    End Sub
    
    Private Sub UpDateCompanyName()
        For Each row As DataGridViewRow In DataGridView1.Rows
            row.Cells("CompanyName").Value = CType(row.Cells("Customer").Value, Customer).CompanyName
        Next
    End Sub
    
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        ' Submit the changes made to the Order table
        ctx.SubmitChanges()
    End Sub
    

    To your question, "Why is the reason behind this design Microsoft choose? Results with complete Tables are connected, partial Tables not?" Well one reason would be that the partial Tables returned may or may NOT have the primary key of the record which is needed if an update is to be done. Another reason would be with concurrency issues.

     

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by Leo Vetterli Tuesday, September 27, 2011 7:52 AM
    Monday, September 26, 2011 6:27 PM