Skip to main content

Compiled Query- Improve the performance of Linq to Entity Query


Most of the small or medium IT firms are using the Entity framework for the Data Access layer (DAL). If we write a complex linq to Entity queries performance will always be an issue. But with the Compiled Query Performance can be improved. This below definitions are from MSDN and more details can be found on the MSDN Link that is at the end of this post 


         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 QuoteRevision for a particular quotelineStatus, the quotelinestatus is specified at runtime. LINQ to Entities supports using compiled queries for this purpose.


              The compiled query class provides compilation and caching of queries for reuse. Conceptually, this class contains a CompiledQuery's Compile method with several overloads. Call the Compile method to create a new delegate to represent the compiled query. The Compile methods, provided with a object context(dbcontext)  and can provide list of parameter values , return a delegate that produces some result (such as an IQueryable instance). The query compiles once during only the first execution. The merge options set for the query at the time of the compilation cannot be changed later. Once the query is compiled you can only supply parameters of primitive type but you cannot replace parts of the query that would change the generated SQL.

               The LINQ to Entities query expression that the CompiledQuery's Compile method compiles is represented by one of the generic Func delegates, such as Func( Encapsulate a method that has diff parameters- ‘in our case object context, parameters and return type of T’, and a return value of the type specified).  
Maxcimum, the query expression can encapsulate an ObjectContext parameter, a return parameter, and 16 query parameters. If more than 16 query parameters are required, you can create a structure whose properties represent query parameters. You can then use the properties on the structure in the query expression after you set the properties.

Practical Example:-

Compiled Query:-
static Func<PricingToolEntities, Parameters, IQueryable<XX>> compiledQueryXXList =
         CompiledQuery.Compile<PricingToolEntities, Parameters, IQueryable<XX>>(
             (ctx, myparams) => (from x in ctx.XX
                                 join y in ctx.YY
                                 on x.Id equals y.xxId
                                 join z in ctx.ZZ
                                 on y.Id equals z.yyId
                                 join a in ctx.AA
                                 on z.AAId equals a.Id
                                 where x.ExpiryDate >= myparams.param1&& x.IssueDate >= myparams.param2&& a.Name.ToUpper().Trim() == myparams.param3.ToUpper().Trim()
                                              select qr).Distinct());

Parameters :-
struct Parameters
        {
            public DateTime param1;
            public DateTime param2;
            public string param3;
        }

Invoking
Parameters myParams = new Parameters();
myParams.param1= commenceDate;
myParams.param2= TodayDate;
myParams.param3= name;

IQueryable<XX> xxList = compiledQueryXXList.Invoke((Entities)dbContext, myParams);



For more information can be found in

Comments

  1. wondered how we missed this little tip. Surely hope this does help us in improving the performance with Linq queries. glad you put this in your blog.

    ReplyDelete

Post a Comment

Popular posts from this blog

How to write secure mvc application using encrypting URL

There are lots ways available to write the secure Mvc application. In my experience I came across lots of secure application in public facing. I want to tell about some of the mechanism I followed.
1- encrypting the URL Parameters and preserving id's encrypted on the client side
1- I always make sure if I pass Id or any sensitive data into the view always make sure it's encrypted. By doing so make sure if we forced to use HTML.hidden or HTML.hidden for have the encrypted values, in action link if I pass any parameters from the client side (eg:- edit or create or navigating between different actions we can make sure that all the values are encrypted)
During the design of the actions results if it's http get I usually encrypt the sensitive data
[httpget]
Public actionresult display()
{
TestModel testModel=new TestModel();
testModel.id= encrypt(id);
Return View(testModel);
}
[httppost]
Public ActionResult Display(string id)
{
Guid d_id= new Guid(decrypt(id));
// do operati…
Post a Comment
Read more

How to add a stored procedure in Entity Framework

Step1- Open the .edmx file
Step2- Right click in .edmx file and select Update Model from Database


Step3- Select Add tab, select Stored Procedures and select the procedure you want to add and click finish as shown in below figure a

Step4- Build the Entity Project to make sure nothing is broke

Step5- Right click on .edmx file and  select Model Browser



Step6- Model Browser Popup will appear as  below figure. Click on the Strored Procedures in PricingToolModel.Stroe and click the newly added SP (it will appear here)
Step7- The Add Function Import screen will appear

Based on the return we can set up.

If you are returning a multiple column Click on the Get Column Information First, then click the new Complex Type. After that you can see the Return of Collection will select the Complex and a complex type is generated by EF

If its returning scalar you can select which return type like int, string etc...

If its return entitty (means table) please select the correct table from the list..

Click…
Post a Comment
Read more

Can we store wifi and use it later?

I started using WIFI almost 8 years back (in 2006) when I moved to the UK. Before that, during the engineering degree I studied about WIFI energetically for achieving grades. Nowadays it’s a common friend and every day without her presence, it is awful to envisage. One day my data allowance on the mobile run out and I want to go to an event in central London and want to use the GPS. I had to take map printout and follow the route. I depend a lot on GPS navigation rather than a paper map. So I reached the location half an hour later in the event. Eventually I missed the event and while walking back to the tube station I was thinking about storing the WIFI somehow and using it later, like a battery. People might think why can’t I carry a portable modem and a laptop, but I am thinking about much sophisticated device than that like a battery for the phone. Did anyone think about a life without batteries now? It is there since most of us on this planet born. In 1749 Benjamin Franklin firs…
Post a Comment
Read more