c# - When should I use a CompiledQuery?

Question

I have a table:

-- Tag

ID  | Name
-----------
1   | c#
2   | linq
3   | entity-framework

I have a class that will have the following methods:

IEnumerable<Tag> GetAll();
IEnumerable<Tag> GetByName();

Should I use a compiled query in this case?

static readonly Func<Entities, IEnumerable<Tag>> AllTags =
    CompiledQuery.Compile<Entities, IEnumerable<Tag>>
    (
        e => e.Tags
    );

Then my GetByName method would be:

IEnumerable<Tag> GetByName(string name)
{
    using (var db = new Entities())
    {
        return AllTags(db).Where(t => t.Name.Contains(name)).ToList();
    }
}

Which generates a SELECT ID, Name FROM Tag and execute Where on the code. Or should I avoid CompiledQuery in this case?

Basically I want to know when I should use compiled queries. Also, on a website they are compiled only once for the entire application?

Answer 1

You should use a CompiledQuery when all of the following are true:

• The query will be executed more than once, varying only by parameter values.
• The query is complex enough that the cost of expression evaluation and view generation is "significant" (trial and error)
• You are not using a LINQ feature like IEnumerable<T>.Contains() which won't work with CompiledQuery.
• You have already simplified the query, which gives a bigger performance benefit, when possible.
• You do not intend to further compose the query results (e.g., restrict or project), which has the effect of "decompiling" it.

CompiledQuery does its work the first time a query is executed. It gives no benefit for the first execution. Like any performance tuning, generally avoid it until you're sure you're fixing an actual performance hotspot.

2012 Update: EF 5 will do this automatically (see "Entity Framework 5: Controlling automatic query compilation") . So add "You're not using EF 5" to the above list.