Lookup Tables do not need to get pulled several times


  • Hey guys,

    I'm developing in 2012 and start to use the entity framework. 

    In several cases I need to use reference tables. With reference tables I mean easy lookup-tables which won't change during runtime. With other words I would like to pull them just only one time from the SQL Server. This actually seems to work fine for this case:

        Private Sub cmd_comboInit_Click(sender As Object, e As EventArgs) Handles cmd_comboInit.Click
            With combo_linqLookupTable
                .ValueMember = "Id"
                .DisplayMember = "Name"
                .DataSource = linqSqlItemSheet.OrderTypes
            End With
        End Sub

    If I click this Button the first time, it will pull the information from the SQL-Server. If click the Button again, it is populating the same kind of data without pulling it from the database.

    But sometimes I need just a few entries of the table. So I wanted to use following code.

        Private Sub cmd_comboInitSelect_Click(sender As Object, e As EventArgs) Handles cmd_comboInitSelect.Click
            With combo_linqLookupTable
                .ValueMember = "Id"
                .DisplayMember = "Name"
                .DataSource = From p In linqSqlItemSheet.OrderTypes Where p.Id = 1 Or p.Id = 2 Select p
            End With
        End Sub

    Using the SQL Profiler you can see that it pulls data every time I initialize the combo box.

    Is there a possibility to force linq-statements not to pull the data again? And is there a possibility to force linq-statements to pull the information again (like a refresh)?

    Thank you!

    Monday, October 28, 2013 4:15 PM


All replies

  • Since you already have the full lookup table (I assume) available why not query it to get the items you need.

    Lloyd Sheen

    Monday, October 28, 2013 10:35 PM
  • How can I do that? So you kind of mean storing a local copy of this lookup-table-linq-object? If so how does that work?
    Tuesday, October 29, 2013 1:40 PM
  • You should be able to cache the data from linqSqlItemSheet.OrderTypes into a variable on the first access and then in the second case do the select from that cache.

    Lloyd Sheen

    Tuesday, October 29, 2013 2:52 PM
  • Ok. And what is the type of the cache? And how can I make sure, that it is not just a reference? Can you give me some code, please?


    Private cache_t As New ???Cache??? = linqSqlItemSheet.OrderTypes.???Copy???

    • Edited by thomasfischer25 Wednesday, October 30, 2013 1:49 PM hard to read
    Wednesday, October 30, 2013 1:48 PM
  • Since you haven't shown us what the OrderTypes returns I will assume that it is an object of type OrderType.  So in this case just create a list of OrderType.  Then use the following to populate the list.

    Dim OrderTypeCache as List(of OrderType) = (From p In linqSqlItemSheet.OrderTypes Select p).ToList()
    Then when you need to access the items in this table/view you query the OrderTypeCache.  This will not cause a database access other than the first populate action.

    Lloyd Sheen

    Wednesday, October 30, 2013 2:06 PM
  • Is there also a way to put it into a DataTable instead of a list and after that from a DataTable back into a IQueryable?
    Thursday, October 31, 2013 11:35 AM
  • I am sure  there is but I cannot imagine why you would want to do that.  Working with objects is so much easier both codingwise and ease of updates and access than datatables.

    Lloyd Sheen

    Thursday, October 31, 2013 10:16 PM
  • Ok guys... No I got it.

    Of course I don't want to use Datatables anymore. But I didn't know a better way to store linq results. I'm a beginner if it comes to linq. But I just figured out what I wanted to do.

    To Store a Single Line and send a request to the server just once I need to do that... 

    tblLineItemSpec 'is the Type of my Table

    Private itemSpec_t As tblLineItemSpec Private Sub cmd_linqObtainData_Click(sender As Object, e As EventArgs) Handles cmd_linqObtainData.Click Dim itemSheetResults_t = From itemSheet In linqSqlItemSheet_t.tblLineItemSpecs Where itemSheet.fdOrderNum = "9900990099" Select itemSheet itemSpec_t = itemSheetResults_t.FirstOrDefault End Sub Private Sub cmd_linqAccessData_Click(sender As Object, e As EventArgs) Handles cmd_linqAccessData.Click MsgBox(itemSpec_t.fdOrderType) End Sub

    Friday, November 08, 2013 7:19 PM
  • To store the whole table I do it like this now...

        Private items_t As IQueryable(Of OrderType)
        Private itemsCache_t As List(Of OrderType)
        Private Sub cmd_linqObtainData_Click(sender As Object, e As EventArgs) Handles cmd_linqObtainData.Click
            items_t = From cturv In linqSqlItemSheet_t.OrderTypes Select cturv
            itemsCache_t = items_t.ToList
        End Sub
        Private Sub cmd_linqAccessData_Click(sender As Object, e As EventArgs) Handles cmd_linqAccessData.Click
            For Each x As OrderType In itemsCache_t
        End Sub

    Just one request to the server and I can use the lookup table over and over without accessing the server every time I need it! 

    Thanks for the hints!

    Friday, November 08, 2013 7:22 PM
  • Is there a way to convert the list back to an IQueryable to use linq commands?
    Monday, November 11, 2013 4:26 PM
  • You can perform linq commands on a list.

    Lloyd Sheen

    Monday, November 11, 2013 10:24 PM