none
WCF Data service query RRS feed

  • Question

  • Hello Guys,

    Same question have been asked here

    I am using WCF data service to get data from sql database. I have written one query which takes longer time to execute. i want to improve this expression to get only the columns i need. I could get column from Table1 but not able to find column from Table2 in example below. Appreciate if anyone could help me with this.

    var list = DataContext.Table1.Expand("Table2").Where(T => T.Name = "Test").ToList();

    on the similar lines i have one expression which goes like:

    var list = DataContext.Table1.Expand("Table2/Table3").Where(T => T.Name = "Test").ToList();

    how to get specific columns from 3 tables. alternatively please suggest how can i reduce the response time.

    Saturday, February 3, 2018 5:18 PM

All replies

  • var list = DataContext.Table1.Expand("Table2").Where(T => T.Name = "Test").ToList();

    What is the statement supposed to be doing? Why is it a list being retuned when you have a Where clause on an equals value that indicates only one object should be returned and not a list? Why can't you do a Linq projection specifying what properties are to be retuned to formulae  an object?

    Sunday, February 4, 2018 9:23 AM
  • Hello,

    1. Table1 and Table2 has many to many relation and both table has huge number of records

    2. above query returns all the columns from both the tables

    3. i want to bring selected columns from both the tables as above query takes lot of time to execute

    4. i am able to find columns from Table1 only when tried this

    var list = DataContext.Table1.Expand("Table2").Where(=> T.Name = "Test").Select(T1=>new (T1.ID,T1.Name).ToList();

    Since i am new to this can you please explain in detail what is LINQ projection?

    All i am trying is to optimize above query as it takes lot of time to execute

    Sunday, February 4, 2018 1:19 PM
  • Hello Kodnil,

    When figuring out performance related to queries I find it best to capture the actual sql being sent down.  This will give you great insight into what EF is actually doing.  To do so you will need sql management studio and the profiler.  

    And enable the Text Data in the EventSelection

    Then you will be able to analyze the query to see what it is doing incorrectly, or I suspect you could solve this by a couple of indexes on your tables.

    As DA924x suggests, you might get better luck with a query along the lines of:

    var list = (from r1 in DataContext.Table1
                   join r2 in DataContext.Table2 on r1.Name equals r2.Name
                   select new { r1.ID, r1.Name}).ToList();


    Cheers, Jeff

    Sunday, February 4, 2018 8:08 PM
  • Hi Kodnil,

    For query specific columns in Table1 and Table2, will code below work for you?

    var result = DbContext.Images.Select(r=> new { ImageName = r.Name, TagsName = r.Tags.Select(t=>t.Name).ToList()}).ToList();
    

    If not, could you share us your current model design or table design, the source demo data for two tables, and the result you want to query.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 5, 2018 2:42 AM