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
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