1

I'm trying to identify and improve some hotspots in a WCF-service. One of the queries uses an awful lot of Include statements. SQL server performance is sunshine and lollipops, but EF performance is really bad.

Breaking this monster down into several smaller queries has already helped a lot, converting some of the queries to CompiledQueries also did wonders for the overall execution time.

Sadly EF seems to lack the possibility to properly handle an Include statement in a CompiledQuery, throwing an exception saying: 

LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[xx] Include[xxx](System.Linq.IQueryable`1[xxx], System.String)' method, and this method cannot be translated into a store expression.

The compiled query simply looks like this:

        private static readonly Func<OurContext, Guid, IQueryable<MyType>> GetResidenceAccessForSubscriber =
        CompiledQuery.Compile<OurContext, Guid, IQueryable<MyType>>(
        (context, value) => (
            from t in context.MyType.Include("Stuff.MoarStuff")
            where t.Id == value
            select t));

While the original looks like (and works):

var q = (
                        from tin container.MyType
                        where t.Id == id
                        select t)
                            .Include("Stuff.MoarStuff");

Any tips?

iCollect.it Ltd
  • 92,391
  • 25
  • 181
  • 202
fuaaark
  • 541
  • 8
  • 21
  • What if you make a wrapper around the compiled query which calls the compiled query proper, then does your `Include("Stuff.MoarStuff")`? – Mike Bailey Jun 06 '12 at 15:24
  • I'd quite catch your drift here, wouldnt a wrapper defeat the purpose of the compiledquery? – fuaaark Jun 08 '12 at 08:40
  • What I'm saying is place the `Include` statement somewhere *outside* of the `CompiledQuery`. Since LINQ to Entities doesn't allow you to do this, the only way you can get compiled queries is by including your `Include` at some other stage in the pipeline. One way would be to create a wrapper around your `CompiledQuery` which tacks on the `Include` then passes it off to the `CompiledQuery` proper. – Mike Bailey Jun 08 '12 at 12:47

0 Answers0