EF Eager Loading Using Include()

Setup for this post

(if you want to run the code as displayed)

• Building an EF 4 Model Assembly for Pubs DB – To setup my Pubs model.
• Create Entity Framework Data Context for LINQPad 4 – To set up EF in LINQPad.
• Since I’m using C# Expression, I don’t need to declare EF_CONNECTION_STRING. I’m not sure why LINQPad can pick up the connection for C# Expression but not C# Program.

Lazy Loading is cool; you don’t have to load things you don’t need. However, each item you load is more expensive for each item you load; it can be a net saving if you can avoid loading enough unneeded data.

If you, the smart human, knew that you need to load that data, wouldn’t it be nice to tell the Entity Framework to go ahead and get the data NOW. I can tell EF to Eager Load specific related tables with the Include method. All I have to do is pass in the name of the navigator property of the related object you would like to load.

NOTE: LINQPad 4 is good at displaying the results of these queries in a way that may help you visualize what is happening. I spent a good amount of time trying to include that display: it was ugly.

For example, if I ran this query (LINQPad: C# Expression):

from s in stores.Include("sales")
    select s

This query would fetch all the stores and related sales.

If I wanted to load more than related object I could chain includes like this:

from s in stores.Include("sales").Include("discounts")
    select s

I get all the stores, related sales and discounts.

If I wanted to load related objects to related objects I would put a dot (".") between each level of related objects like this:

from d in discounts.Include("store.sales")
    select d

Here I get the discounts and the stores and the sales per store; the sales will appear below the stores.

Getting TSQL Query With ToTraceString() in EF

Setup for this post

(if you want to run the code as displayed)

• Building an EF 4 Model Assembly for Pubs DB – To setup my Pubs model.
• Create Entity Framework Data Context for LINQPad 4 – To set up EF in LINQPad.
• Querying EF ObjectContext from LINQPad – Why am I using EF_CONNECTION_STRING

I use ObjectQuery.ToTraceString() to figure out what is going on behind the scenes. ToTraceString() returns the TSQL (assuming you are using the SQL Server provider) for that ObjectQuery.

Since I can see the TSQL code that EF generates I can poke around and figure what is going around behind the curtain.

For example if you ran this in LINQPad:

void Main()
{
    PubsModel.pubsEntities context = new PubsModel.pubsEntities(EF_CONNECTION_STRING);

    // Entity SQL:
    // Returns ObjectQuery<T> directly
    ObjectQuery<author> esqlq = context.CreateQuery<author>(
        "SELECT VALUE a FROM authors AS a");
    esqlq.ToTraceString().Dump("--CreateQuery");
    
    // Linq to Entities
    // ObjectQuery<T> implements IQueryable<T>
    // This statement returns an ObjectQuery<T>
    // casted as IQueryable<T>
    IQueryable<author> linqq = from a in context.authors select a;    
    // So it must be cast back to execute ToTraceString()
    ((ObjectQuery)linqq).ToTraceString().Dump("--Linq Query");
}

You would get:

--CreateQuery

SELECT 
[Extent1].[au_id] AS [au_id], 
[Extent1].[au_lname] AS [au_lname], 
[Extent1].[au_fname] AS [au_fname], 
[Extent1].[phone] AS [phone], 
[Extent1].[address] AS [address], 
[Extent1].[city] AS [city], 
[Extent1].[state] AS [state], 
[Extent1].[zip] AS [zip], 
[Extent1].[contract] AS [contract]
FROM [dbo].[authors] AS [Extent1]  

--Linq Query

SELECT 
[Extent1].[au_id] AS [au_id], 
[Extent1].[au_lname] AS [au_lname], 
[Extent1].[au_fname] AS [au_fname], 
[Extent1].[phone] AS [phone], 
[Extent1].[address] AS [address], 
[Extent1].[city] AS [city], 
[Extent1].[state] AS [state], 
[Extent1].[zip] AS [zip], 
[Extent1].[contract] AS [contract]
FROM [dbo].[authors] AS [Extent1]  

Querying EF ObjectContext from LINQPad

I have noticed that if I create an ObjectContext or a decendant of ObjectContext (like pubsEntities from Building an EF 4 Model Assembly for Pubs DB), LINQPad can’t get the connection string correctly.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <connectionStrings>
    <add name="pubsEntities" 
         connectionString="metadata=res://*/PubsModel.csdl|
         res://*/PubsModel.ssdl|res://*/PubsModel.msl;
         provider=System.Data.SqlClient;
         provider connection string='data source=.\SQLEXPRESS;
         attachdbfilename=&quot;C:\MDF\pubs.mdf&quot;;
         integrated security=True;connect timeout=30;
         user instance=True;multipleactiveresultsets=True;App=EntityFramework'" 
         providerName="System.Data.EntityClient" />
  </connectionStrings>
</configuration>

So I take the connection string from the app.config:

And copy it to a LINQPad C# Program .linq file as a constant on the top of the file (I am calling the constant EF_CONNECTION_STRING):

const string EF_CONNECTION_STRING = 
    @"metadata=res://*/PubsModel.csdl|res://*/PubsModel.ssdl|res://*/PubsModel.msl;" +
    @"provider=System.Data.SqlClient;" +
    @"provider connection string='data source=.\SQLEXPRESS;attachdbfilename=" + "\"" + 
    @"C:\MDF\pubs.mdf" + "\"" + 
    @";;integrated security=True;connect timeout=30;user instance=True;" +
    @"multipleactiveresultsets=True;App=EntityFramework'";

void Main()
{
    PubsModel.pubsEntities context = new PubsModel.pubsEntities(EF_CONNECTION_STRING);
    // Use Context Here
}

Create Entity Framework Data Context for LINQPad 4

In what I hope to be my last prerequisite post for Entity Framework on LINQPad, I will create a Data Context for my Entity Model Assembly.

I like to use LINQPad as a snippet editor because of its cool Dump() extension method. This platform makes it easy to play What If with a new technology.

For the purpose of these steps, I’m going to pretend you are using the assembly we created in [last post]. So here are the steps:

1. Open LINQPad 4
2. Click on Add Connection
3. In the Choose Data Context window, choose Entity Framework.
   
4. In the LINQPad Connection window, Browse for your EF Model Assembly
5. In the Choose Custom Type:
   
   • In Custom Type Name you should see PubsModel.pubsEntities
   • In Entity Data Model, choose From Same Assembly, PubsModel should be in the list below
   • Click OK
   
6. Accept the remaining defaults by clicking OK.

Building an EF 4 Model Assembly for Pubs DB

For this EF building block, I am going to build a Model Assembly. This assembly will contain the Model for the famous Pubs database. You can download Pubs along with Northwind from Microsoft.

I like the idea of separating my model for the rest of my app. I will use this model to LINQPad (and perhaps WinForms, WPF, etc.). I can link it directly to my UI or through a labyrinth of assemblies that I put between my UI (if I even have one).

Since you don’t have to write any actual code, the steps below should work for both C# and VB. They were tested on both Express Editions.

So here we go:

1. Create a new Project, call it PubsModel
   I like to use the pattern [SomeName]Model, where SomeName may be the name of the application or database.
2. Delete Class1.cs or Class1.vb
   Microsoft want to be helpful, I’ve probably destroyed a thousand Class1s
3. Project => Add New Item. (Alt+Shift+A)
4. Select ADO.NET Entity Data Model
5. Name it PubsModel.edmx
6. In the Entity Data Model Wizard, select Generate from database
7. In the next window, select pubs.mdf. If pubs.mdf is not on the list, you will need to click New Connection to add it to the list.
   Since this is Visual Studio Express, I am only allowed to make a Microsoft SQL Server Database File connection.
8. Set the value for Save entity connection settings in App.Config as: to pubsEntities.
9. Visual Studio asks you if you want to make a local copy of the database file.
   I usually answer “No” so I can play with the data with Microsoft SQL Server Management Studio. Of course any change I make to the data will affect the “Master” database.
10. Select Tables, Views and Stored Procedures.
11. Keep the defaults for Plurlaize or singularize generated object names and Include foreign key columns in the model.
12. Set Model Namespace to pubsModel
13. Click Finish.

After some churning and reformatting, you will get a model that looks like this

.

Starting LINQPad 4 from Visual Studio Express

As I am writing this year’s Code Camp presentation about Entity Framework, I’ve come across a problem: I want to use Visual Studio Express 2010 AND I want to demonstrate Linq to Entities using LINQPad 4. If I were using a commercial version of Visual Studio, I could use Start Action = Start external program. I can’t just launch LINQPad and edit my project because LINQPad would lock my assembly and Visual Studio would refuse to build a new one.

So, to work around this limitation, I added a console project that would launch LINQPad and wait for it to return.

C#:

using System.Diagnostics;
namespace LaunchLinqPad
{
    class Program
    {
        private const string LINQPAD_EXE = 
            @"C:\Program Files (x86)\LINQPad4\LINQPad.exe";
        static void Main(string[] args)
        {
            var process = new Process();
            process.StartInfo = new ProcessStartInfo(LINQPAD_EXE);>
            process.Start();
            // Without this, the program will exit immediately
            process.WaitForExit();
        }
    }
}

VB:

Module Module1
    Const LINQPAD_EXE = "C:\Program Files (x86)\LINQPad4\LINQPad.exe"
    Sub Main()
        Dim process As New Process()
        process.StartInfo = New ProcessStartInfo(LINQPAD_EXE)
        process.Start()
        ' Without this, the program will exit immediately
        process.WaitForExit()
    End Sub
End Module

A couple of issues:

• This doesn’t work with LINQPad 4 (and newer)
• I can’t step into my code.

Even with these limitations, I find this helpful because doing this endures that LINQPad is closed before I recompile, therefore preventing me from locking up the file that I am building.

Happy Groundhog Day!

Punxsutawney Phil, some rodent 2,000 miles from me sees his shadow so we get 6 more weeks of winter. Had he not seen it, winter would only 42 days left. I will have my traditional Groundhog Day dinner of pork sausage.