Entity Framework - MergeOption.NoTracking and CompiledQuery.Compile
Two things that people tell you about Entity framework
performance are, use the Compiled Queryies and potentially remove tracking
when its not needed. You can achieve this by using the CompiledQuery.Compile
function to obtain a function that is your compiled query and to set the
MergeOption of the relevant entities on the ObjectContext.
I've just finished banging my head against a wall when trying to assess the
performance of these together.
This was my simple code. Create a static readonly function that is the
compiled query and then in by test function checking the status of a check box
to set the MergeOption to MergeOption.NoTracking.
static readonly Func<MobileMinderEntities,
int, IQueryable<Job>>
JobSelect
= CompiledQuery.Compile<MobileMinderEntities,
int, IQueryable<Job>>
((ctx,
jobid) => from j
in
ctx.Job
where
j.Person.PersonId == jobid
&& ctx.JobCompleted.Any(c => c.Signature == null)
select
j);
private void
btnTest_Click(object
sender, EventArgs
e)
{
int var =
1;
using
(MobileMinderEntities me = new MobileMinderEntities())
{
if
(!chkTrack.Checked)
{
me.Job.MergeOption = MergeOption.NoTracking;
}
var
l = JobSelect2(me, var);
OutputTraceQuery(l);
var l_l =
l.ToList();
}
}
I couldn't figure out why I wasn't getting much difference, so I started
profiling the code to see what was going on. Not much there (but I'm not a
profiling expert). I then started hacking code to debug what was going on, what
I couldn't figure was that when I turned tracking on, my object context was
reporting no tracked objects. Uhh.
Can you spot whats wrong?
After making a small dent in the wall I figured the only way that this was
happening was if the traking was being cached. Initially I thought it was the
object context but then thought that its disposed every time, its not a
singleton. So then looked at the function, ok so this is a static readonly, so
only one but surely only the execution tree is cached.
Well it appears not. It appears that the compiled query also includes the
tracking options.
That sort of put my test in a difficult position.The solution I came up with
was to use two functions one that would only be used for tracking and one for
non tracked execution. This worked a treat.
So a word to the wise if you are using CompiledQueries or ObjectQuery.Compile
be careful if you are setting the mergeoption on your
ObjectContext