See what Entity Framework is doing beneath the hood with SQL Server Profiler.

Imagine one were to query from Entity Framework and then cast the results to a series of regular C# objects such as this one:

namespace OldSchool.Models
{
   public class Dto
   {
      public string Foo { get; set; }
      public string Bar { get; set; }
      public string Baz { get; set; }
      public string Qux { get; set; }
   }
}

 
 

I have a friend who is facing the prospect of trying to optimize code that does just that. Code like so:

using System.Collections.Generic;
using System.Linq;
using System.Web.Mvc;
using OldSchool.Models;
namespace OldSchool.Controllers
{
   public class HomeController : Controller
   {
      public ActionResult Index()
      {
         using (RepoEntities myStuff = new RepoEntities())
         {
            List<Dto> myGunk = (from a in myStuff.Yangs join i in myStuff.Yings on a.YingId
                  equals i.YingId select new Dto()
               {
                  Foo = i.foo,
                  Bar = a.bar,
                  Baz = a.baz,
                  Qux = a.qux
               }).ToList();
            return View(myGunk);
         }
      }
   }
}

 
 

In the name of trying to figure out how to optimize, it is probably best to spy on the SQL generated with SQL Server Profiler like so:

 
 

There is one query:

SELECT
1 AS [C1],
[Extent2].[foo] AS [foo],
[Extent1].[bar] AS [bar],
[Extent1].[baz] AS [baz],
[Extent1].[qux] AS [qux]
FROM [dbo].[Yang] AS [Extent1]
INNER JOIN [dbo].[Ying] AS [Extent2] ON [Extent1].[YingId] = [Extent2].[YingId]

 
 

Well, is there even anything to change here? There is not much to vary while having the same outcome. Things I can think to try are:

1. taking the casting to the intermediate object out of the same line of code that does the query
2. getting rid of the join
3. both things at once

 
 

The first alteration...

using System.Collections.Generic;
using System.Linq;
using System.Web.Mvc;
using OldSchool.Models;
namespace OldSchool.Controllers
{
   public class HomeController : Controller
   {
      public ActionResult Index()
      {
         using (RepoEntities myStuff = new RepoEntities())
         {
            var y = from a in myStuff.Yangs join i in myStuff.Yings on a.YingId equals i.YingId
                  select a;
            List<Dto> myGunk = new List<Dto>();
            foreach (Yang a in y)
            {
               myGunk.Add(new Dto()
               {
                  Foo = a.Ying.foo,
                  Bar = a.bar,
                  Baz = a.baz,
                  Qux = a.qux
               });
            }
            return View(myGunk);
         }
      }
   }
}

 
 

...just makes things worse. Look, we have an n+1 problem by which I mean a separate query is run for every child referenced by a parent collection. Observe:

• SELECT
  [Extent1].[YangId] AS [YangId],
  [Extent1].[bar] AS [bar],
  [Extent1].[baz] AS [baz],
  [Extent1].[qux] AS [qux],
  [Extent1].[YingId] AS [YingId]
  FROM [dbo].[Yang] AS [Extent1]
   
• exec sp_executesql N'SELECT
  [Extent1].[YingId] AS [YingId],
  [Extent1].[foo] AS [foo]
  FROM [dbo].[Ying] AS [Extent1]
  WHERE [Extent1].[YingId] = @EntityKeyValue1',N'@EntityKeyValue1
        uniqueidentifier',@EntityKeyValue1='B1AD1D61-C399-4235-A072-
        A8D98BCD11AC'
   
• exec sp_executesql N'SELECT
  [Extent1].[YingId] AS [YingId],
  [Extent1].[foo] AS [foo]
  FROM [dbo].[Ying] AS [Extent1]
  WHERE [Extent1].[YingId] = @EntityKeyValue1',N'@EntityKeyValue1
        uniqueidentifier',@EntityKeyValue1='CE23A311-551D-42AD-B95D-
        53D60A612551'
   
• exec sp_executesql N'SELECT
  [Extent1].[YingId] AS [YingId],
  [Extent1].[foo] AS [foo]
  FROM [dbo].[Ying] AS [Extent1]
  WHERE [Extent1].[YingId] = @EntityKeyValue1',N'@EntityKeyValue1
        uniqueidentifier',@EntityKeyValue1='276D0065-9680-4E3F-84EF-
        1EFACEF07ED9'

 
 

One of the extra queries is seen here in SQL Server Profiler:

 
 

Boo! Well, can we get just rid of the join?

using System.Collections.Generic;
using System.Linq;
using System.Web.Mvc;
using OldSchool.Models;
namespace OldSchool.Controllers
{
   public class HomeController : Controller
   {
      public ActionResult Index()
      {
         using (RepoEntities myStuff = new RepoEntities())
         {
            List<Dto> myGunk = (from a in myStuff.Yangs select new Dto()
               {
                  Foo = a.Ying.foo,
                  Bar = a.bar,
                  Baz = a.baz,
                  Qux = a.qux
               }).ToList();
            return View(myGunk);
         }
      }
   }
}

 
 

Yes, we are back to the query we had to begin with with no performance loss! Win!

SELECT
1 AS [C1],
[Extent2].[foo] AS [foo],
[Extent1].[bar] AS [bar],
[Extent1].[baz] AS [baz],
[Extent1].[qux] AS [qux]
FROM [dbo].[Yang] AS [Extent1]
INNER JOIN [dbo].[Ying] AS [Extent2] ON [Extent1].[YingId] = [Extent2].[YingId]

 
 

What if we both take out the join and the casting to our object from the call to Entity Framework.

using OldSchool.Models;
namespace OldSchool.Controllers
{
   public class HomeController : Controller
   {
      public ActionResult Index()
      {
         using (RepoEntities myStuff = new RepoEntities())
         {
            List<Dto> myGunk = new List<Dto>();
            foreach (Yang a in myStuff.Yangs)
            {
               myGunk.Add(new Dto()
                  {
                     Foo = a.Ying.foo,
                     Bar = a.bar,
                     Baz = a.baz,
                     Qux = a.qux
                  });
            }
            return View(myGunk);
         }
      }
   }
}

 
 

This takes us back to multiple queries be generated. I won't show you. Use your imagination.