locked
Dynamic select from related tables RRS feed

  • Question

  • I have a central table with approximately 30 tables referenced by a FK to the main table.  On any given installation, there will be 2-5 of the 30 tables in use.  I need all columns from all enabled tables, but I won't know which of the ones (beyond the main table) that are enabled until runtime.  The reason I need a "dynamic" select is that when using EF, after a row is returned from the main table, a sub query is initiated against EVERY one of the 30 child tables which has a serious performance hit.  

    When I do a select into an anonymous object and explicitly list a set of columns from the tables I care about, the unnecessary queries are eliminated and I get the results and performance I want.  

    Code that generates sub query on every associated table:
    
    var v = from r in context.Records
    select r;
    
    Code that only pulls back data from tables I want
    
    var v = from r in context.Records
            select new { ID = r.ID, val1 = r.val1, val2=r.val2, val3 = r.child_table1.val1, r.child_table1.val2 };
    
    I need a way to create the select new { blah }; portion at runtime or another method that lets me determine which tables are queried from a basic select that should give me everything.

    I just can't hard code the selects for every possible table combination as that would be a maintenance nightmare.

    I have asked this question at stack overflow as well with more detailed output from the EF that I didnt want to repeat here.

    Everything I've found is mostly dynamically creating the Where portion, but I don't need to do anything there for my application.

    If there is further explanation required, I will do my best.

     Any suggestions or tips would be very much appreciated.

    Thanks in advance.

    Wednesday, December 4, 2013 6:40 PM

Answers

  • Hi,

    >>but I can't figure out a way to create the .Select lambda dynamically

    For the way to create the .Select lambda dynamically, please have a look the link below, we can use the Dynamic Linq Library:

    http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

    We can use it like below and then you can choice fields that you want.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by DBchBm Friday, December 6, 2013 6:12 AM
    Friday, December 6, 2013 5:53 AM

All replies

  • Hello DBchBm,

    In the link, I notice that each customer may have serval different tables. And according to your description, I know you would load all fields. For this, I have an idea that we can use a loop and the include() method.

    My idea is like below:

    List<string> tableNames = new List<string>() { "OrderDetails" };
    
    
                    DbQuery<Order> query = db.Orders;
    
    
                    foreach (string name in tableNames)
    
                    {
    
                        query = query.Include(name);
    
                    }
    
    
                    var result = from order in query
    
                                 where order.OrderID == 1
    
                                 select order;
    

    So the emphasis is how to get the table names which belong to customers. I do not know whether you already have such a table that logs customer accounts and their related table names. In my opinion, I will create one.

    Hope it be helpful to you.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 5, 2013 6:48 AM
  • Thank you for your idea.  That would load the data on the 1st query without the subsequent ones to the "enabled" tables, however, it doesn't prevent the subsequent queries that are occurring.  After some further testing, it appears my dilemma is due to DevExpress EntityInstantFeedbackSource that I am using to bind their WPF Grid to the data via EF.  EF created a class that is my base table which has a virtual property to each of the associated child tables.  Apparently behind the scenes they are creating a binding on all of the properties of that EF generated class and accessing all of them which is what is triggering the load on every child table. Since finding that, they have informed me there is no way to prevent those subqueries from those bindings.  Their suggestion was to create a select that only pulls in the columns I want into an anonymous object that I could then bind the InstantFeedbackSource to which does work as I want, but I can't figure out a way to create the .Select lambda dynamically.  Everything I can find for dynamic creation is related to the .Where.


    Thursday, December 5, 2013 7:51 PM
  • Hi,

    >>but I can't figure out a way to create the .Select lambda dynamically

    For the way to create the .Select lambda dynamically, please have a look the link below, we can use the Dynamic Linq Library:

    http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

    We can use it like below and then you can choice fields that you want.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by DBchBm Friday, December 6, 2013 6:12 AM
    Friday, December 6, 2013 5:53 AM
  • I've read Scott's article on that a dozen times in the last few days, but I never read the embedded image he referenced...  talk about right under my nose.

    MANY thanks for pointing it out to me.

    Mark

    Friday, December 6, 2013 6:14 AM