none
confused here - need help with multiple sub-queries from a main query and keep it clientside RRS feed

  • Question

  • Here is what I want to do,

    I have a table on a sql server that I want to query for key information  (id,name,type) then I want to query that main query client side to create sub-lists of the 1st queries results.

    query 1
        dim AllMembers = from all in db.companies  where name>'' select all.id, all.name, all.type)

    query2
         dim allVotingMemebers = from allvote in allmembers where type="voting" select allvote

    query3
         dim allNonVotingMemebers = from allNonvote in allmembers where type="non-voting" select allNonvote

    So I want the 1st query to get from the server and any subsquent queries to only query the local data

    I have tried this numerous ways and it always creates another query to the SQL server for each.

    Here is my latest attempt

    Dim allmembers = (From c In db.Companies Where Name > "" Order By c.Name Select c.Name, c.CompanyID, c.CompanyType).AsEnumerable  
    Dim allTerminated = (From allterm In allmembers Where allterm.CompanyType Like "term*" Select allterm).AsEnumerable  
     
            ListBox1.DisplayMember = "name" 
            ListBox1.ValueMember = "companyid" 
            ListBox1.DataSource = allmembers.ToList  
            ListBox2.DisplayMember = "name" 
            ListBox2.ValueMember = "companyid" 
            ListBox2.DataSource = allTerminated.ToList 
            RadListBox2.DataSource = allTerminated.ToList

    This creates 2 calls to the SQL server. I have 10 different types of members that need to load when this application loads I really hate to make 11 calls to the server to populate these listboxes. Then on top of that I need to count for member type so I can display that at the top of each list...that creates 21 calls to the server just to get the app started...


    Any help would be appreciated as I have search high and low and still havnt figured things out.

    Thanks in advance

    Thursday, February 12, 2009 4:33 PM

Answers

  • Hi Storyteller12,

    You can materialize the results of your first query by calling a method like ToArray or ToList. Afterwards, you can execute all of your other queries on that list or array; those queries will execute only on the client side. For example--

    Dim allMembers = (From c in db.Companies Where Name > "" Order By c.Name Select c.Name, c.CompanyID, c.CompanyType).ToList  
    Dim allTerminated = (From m in allMembers Where m.CompanyType Like "term*" Select m) 

    Just make sure you are aware that whenever you do not materialize a query (with methods like ToArray, ToList, ToDictionary, and ToLookup), you will run the query every time you iterate its contents. So in your code sample above, calling ToList on "allTerminated" twice will run the query twice against SQL Server.

    Hope that helps,
    David
    Wednesday, February 18, 2009 12:25 AM
    Answerer