none
Linq to SQL Newbie - How to prevent multiple SQL requeries RRS feed

  • Question

  • Hi

    I'm new to L2S and I'm thinking I'm doing it all wrong.

    I'm trying to import a list of (potentially) new product descriptions from an Excel file (examples much simplified) into tblCMProduct table.

    Basic problem I've got is in Import1, every time it does the 'Dim drProduct = (From f In Products Where f.Name = CurProduct).FirstOrDefault' line it re-queries the database, when in real world, I'm quite happy to read all the data in once (it will only be a couple of thousand rows) then requery on that for each iteration - and adding new products when not known (to the DB and CM.tblCMProducts for the further iterations).

    In Import2, I used a .ToList, which is used for the search, but this means I have to then maintain the list as well as the SQL when adding a new product (to ensure a product isn't duplicated in the Excel).  I know I can use .Distinct but the real Excel has 22 columns, and the product name is frequently duplicated with other columns adding together to form a PK.

    I suppose what I'm asking is how to say to L2S; run this query and get the starting data, then let me apply whatever CRUD functions I want on the resultant data, in as many iterations as I want, but don't re-query each time cos you'll just be bringing the same data back?

    I'm from a Dataset background, if you hadn't guessed :-)

    Many thanks

    Mike

        Public Sub Import1(dtExcel As DataTable)

            Using CM As New CMDataContext
                Dim Products = (From f In CM.tblCMProducts)

                For Each ExcelRow As DataRow In dtExcel.Rows
                    Dim CurProduct = ExcelRow("ProductName").ToString.Trim

                    Dim drProduct = (From f In Products Where f.Name = CurProduct).FirstOrDefault
                    If drProduct Is Nothing Then
                        drProduct = New CM.tblCMProduct With {.Name = CurProduct}

                        CM.tblCMProducts.InsertOnSubmit(drProduct)
                    End If
                Next

                CM.SubmitChanges()
            End Using
        End Sub

        Public Sub Import2(dtExcel As DataTable)
            Using CM As New CMDataContext
                Dim Products = (From f In CM.tblCMProducts).ToList

                For Each ExcelRow As DataRow In dtExcel.Rows
                    Dim CurProduct = ExcelRow("ProductName").ToString.Trim

                    Dim drProduct = (From f In Products Where f.Name = CurProduct).FirstOrDefault
                    If drProduct Is Nothing Then
                        drProduct = New CM.tblCMProduct With {.Name = CurProduct}

                        Products.Add(drProduct)
                        CM.tblCMProducts.InsertOnSubmit(drProduct)
                    End If

                Next
     
                CM.SubmitChanges()
            End Using
        End Sub

    Saturday, September 22, 2012 12:03 PM

Answers

  • Hi Mike

    If you write a query like this and the run time executes the line then what is in the variable Products is a query that will be sent to the server one you iterate over the query.  

    Dim Products = (From f In CM.tblCMProducts)

    When the query gets executed, and this only happens when you iterate over the query, then the results are stored in the data context. In order to get access to those values you need to maintain a collection of the results. This can be done by assigning the results of the query to a collection like a List collection as in the following statement.

    Dim Products = (From f In CM.tblCMProducts).ToList()

    When the run time executes this line of code Products is no longer a query that will be executed later but is executed immediately and Products becomes a List(Of tblCMProducts). This list of objects points to the objects that are in the data context and therefore if you modify any object in this list and save the data context back to the server all the updated properties will be saved. If you need to create new objects and add them to the data context and then save the data context then those new objects will be added to the database

      


    Fernando (MCSD)

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


    Tuesday, September 25, 2012 2:02 PM

All replies

  • Hi Mike;

    This is the way I would do it for Import1. This way you query the database once and add those that were not found.

    Public Sub Import1(dtExcel As DataTable)
        ' Create a List of strings of all the names from the Excel
        Dim possibleNewProds = new List<string>()
        ' Iterate through all the rows and get the Name's
        For Each ExcelRow As DataRow In dtExcel.Rows
            Dim CurProduct = ExcelRow("ProductName").ToString.Trim
            possibleNewProds.Add(CurProduct)
        Next
        
        ' Now query the database for all the names in the list that was created above
        Using CM As New CMDataContext
            Dim products  = (From f In CM.tblCMProducts
                             Where possibleNewProds.Contains(f.Name)
                             Select f.Name).ToList()
    
            ' Now compare the names from the possibleNewProds with the product names
            ' returned in the above query and just return the ones not found.
            Dim needToAdd = possibleNewProds.Except(products)
            
            ' Now add all the names in the needToAdd list to the database
            If needToAdd.Count <> 0 Then
                For Each newProd As String in needToAdd
                    Dim drProduct = New CM.tblCMProduct With {.Name = newProd}
                    CM.tblCMProducts.InsertOnSubmit(drProduct)
                Next
                CM.SubmitChanges()
            End If        
        End Using
    End Sub
    

      


    Fernando (MCSD)

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

    Saturday, September 22, 2012 4:44 PM
  • Hi Fernando, many thanks for the reply.

    The sample I showed was to import a single field (Product name), but as I said, the reality is I've got 22 fields with hierarchical structure to import so it's not a code-around for a single column, it's a fundamental question.

    As I said, I'm from a dataset background where I could happily do somthing like

        Dim ta as new DS.tblProductTableAdapter
        Dim dt as new DS.tblProductDataTable

        ta.fill(dt)

        For ExcelRow In dtExcel.Rows
                    Dim CurProduct = ExcelRow("ProductName").ToString.Trim

                    Dim drProduct = (From f In dt Where f.Name = CurProduct).FirstOrDefault
                    If drProduct Is Nothing Then
                        drProduct = dt.NewRow
                        drProduct.Name = CurProduct
                        dt.Rows.Add(drProduct)
                    End If
        Next    

        ta.Update(dt)

    This would require a single call to populate dt initially - ta.fill(dt), which for the small number of records I have is no biggie.

    There would then be no further trips to the database until the ta.Update(dt) call to insert any new records.

    The thing I can't grasp is how to do 'Dim drProduct = (From f In Products Where f.Name = CurProduct).FirstOrDefault' without it going to the database each time when it doesn't need to (as in the dataset example). 

    If the Products table started empty and I imported 1000 records, it would need 1000 queries to the database with LINQ.  With datasets it would need 1.

    I'm definitely missing something but not sure what.

    Reagrds

    Mike

    Saturday, September 22, 2012 6:20 PM
  • Hi Mike

    First let me ask this question in the Excel records how many records would be in this data source that you need to check to see if it needs to be added to the database?

      


    Fernando (MCSD)

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

    Saturday, September 22, 2012 6:42 PM
  • Hi Fernando

    Apologies for the delay - was away for a couple of days.

    I guess it's more of a generic question - how can I get LINQ to query a cached copy of the data, rather than going back to the database each time.

    If I do a

            Using CM As New CMDataContext
                Dim Products = (From f In CM.tblCMProducts)

                For Each Product In Products
                    ... do some stuff
                Next

                For Each Product In Products
                    ... do some more stuff stuff
                Next

            End Using

    The second For .. Each re-queries the database when I'm quite happy that the initial results are still good to work with.

    If it's read-only then I can do a .ToList so it only reads once, but if I'm also inserting records it means I have to add to the list and to the DC.

    As I said, I'm probably doing this all wrong.

    Regards

    Mike

    Tuesday, September 25, 2012 1:33 PM
  • Hi Mike

    If you write a query like this and the run time executes the line then what is in the variable Products is a query that will be sent to the server one you iterate over the query.  

    Dim Products = (From f In CM.tblCMProducts)

    When the query gets executed, and this only happens when you iterate over the query, then the results are stored in the data context. In order to get access to those values you need to maintain a collection of the results. This can be done by assigning the results of the query to a collection like a List collection as in the following statement.

    Dim Products = (From f In CM.tblCMProducts).ToList()

    When the run time executes this line of code Products is no longer a query that will be executed later but is executed immediately and Products becomes a List(Of tblCMProducts). This list of objects points to the objects that are in the data context and therefore if you modify any object in this list and save the data context back to the server all the updated properties will be saved. If you need to create new objects and add them to the data context and then save the data context then those new objects will be added to the database

      


    Fernando (MCSD)

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


    Tuesday, September 25, 2012 2:02 PM