locked
When my compile query won't hit? RRS feed

  • Question

  • Hello All,

    I am using Compiled query in my project and it is below.

    public static Func<Entities, IQueryable<Patient>> getPatientList = CompiledQuery.Compile(
          (Entities ctx) => (
                       from p in ctx.Patient
                       where
                       p.IsActive == true && p.IsDeleted == false
                       select p
                       ).AsQueryable()
           );

    Now my question is can I use same compiled query in another Controller where I need to add more condition in compiled query?

     var res = getPatientList(context).Where(p => p.City == "City").ToList();

    As my Patient query has return all the active patient and if I further query on it by filter customer city wise, will my compiled query be hit?

    I mean to further query on compiled query will keep benefits of compiled query or it will failed as we have perform further query on it?

    Also can anyone will provide me list of operations which will break benefits of compiled query?

    Thanks in Advance,

    Dharmesh Solanki


    • Edited by dmsolanki Tuesday, October 16, 2012 7:31 AM
    Tuesday, October 16, 2012 7:30 AM

Answers

  • Hi Dharmesh,

    Welcome to the MSDN forum.

    Executing different normal query lead to that the query is be complied each time when it is executed. Comparing with the normal query, the complied query is only compiled for the first time it is executed. For your problem,  the second call also execute the linq query (generate the corresponding sql query). You can use this to avoid executing twice:

    var result = getProducts().ToList();

    var resultCount = result.Count();

    Have a nice day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Alexander Sun Friday, November 9, 2012 9:09 AM
    Friday, October 26, 2012 8:57 AM

All replies

  • Hi Dharmesh,

    Welcome to the MSDN forum.

    When you have an application that executes structurally similar queries many times in the Entity Framework, you can frequently increase performance by compiling the query one time and executing it several times with different parameters. For example, an application might have to retrieve all the customers in a particular city; the city is specified at runtime by the user in a form. LINQ to Entities supports using compiled queries for this purpose. Please refer to this page: http://msdn.microsoft.com/en-us/library/bb896297(v=vs.100).aspx

    For your problem, since the return type is IQueryable<Patient>, you can call any extension methods supported in IQueryable<T> and your comipled query method will be hit.

    Best Regards,


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, October 17, 2012 7:30 AM
  • Hello Alexander,

    Thanks a lot for such nice reply.

    I have read somewhere while searching for Compiled Query that some of operations like IQueryable<T>.Count() on existing compile query which is not returning "Count" at all, may not be hit Compiled Query which is already in cache but query will be executed as normal query with re-compile and executed against SQL Server.

    So that I wanted some of guidelines or operation list that can break Compiled query optimization and run as a normal query.

    Thanks again,

    Dharmesh

    Thursday, October 18, 2012 5:53 AM
  • Anyone having idea ?
    Friday, October 19, 2012 6:19 AM
  • Hi Dharmesh,

    The Count() method works on my side. Could you please show me the related code snippet where Count() method does not work?

    Have a nice day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    Friday, October 19, 2012 7:13 AM
  • Hello Alexander,

    All code works running my end very well. What I just want to convey is result will be returned using cached compile query or using normal query?

    For ex. I have compiled query that returns IQueryable<Product>. So for ex. if I am writting below code then result will be fetching using cached compile query or normal query which compiled first before return result to code.

    // this result will be returned from compiled query
    
    var result = getProducts().ToList();
    
    // Will this statement will be excuting using Compile query?
    var result = getProducts().Count();

    NOTE: If u have idea, is there any way by which we come to know that particular LINQ to SQL query is used Compile Query or normal query.Please assume that I have already created compiled query.I want to know at run time what happened.

    Regards,

    Dharmesh



    • Edited by dmsolanki Tuesday, October 23, 2012 8:00 AM
    Tuesday, October 23, 2012 7:58 AM
  • Hi Dharmesh,

    Welcome to the MSDN forum.

    Executing different normal query lead to that the query is be complied each time when it is executed. Comparing with the normal query, the complied query is only compiled for the first time it is executed. For your problem,  the second call also execute the linq query (generate the corresponding sql query). You can use this to avoid executing twice:

    var result = getProducts().ToList();

    var resultCount = result.Count();

    Have a nice day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Alexander Sun Friday, November 9, 2012 9:09 AM
    Friday, October 26, 2012 8:57 AM