Using LINQPad’s SQL Tab To Investigate EF’s Crud Commands

I like LINQPad. One thing I like to do with it is to use to figure out what queries the Entity Framework is generating when I do basic tasks. In this post, I will look at what is going on for basic CRUD operations (actually CUD, since the Read operations are self evident). For this example, I am going use the method outlined in Querying EF ObjectContext from LINQPad; I am using the PUBS database and the model from Building an EF 4 Model Assembly for Pubs DB.

Whenever you run a query in LINQPad you can click on the SQL to see what has been sent to the server. I find this method more useful than Getting TSQL Query With ToTraceString() in EF; I can run code that is not a LINQ query and see what happens.

 

Add

Given this function:

void addTitle(PubsModel.pubsEntities context)
{
     var newTitle = new title();
     newTitle.title_id = "myTtl";
     newTitle.title = "Hamlet";
     newTitle.type = "Literature";
     newTitle.pubdate = DateTime.Today;
     newTitle.publisher = 
          context.publishers.Where(p => p.pub_id == "9901").Single();

     context.titles.AddObject(newTitle);

     context.SaveChanges();
}

I get this SQL from LINQPad:

SELECT TOP (2) 
[Extent1].[pub_id] AS [pub_id], 
[Extent1].[pub_name] AS [pub_name], 
[Extent1].[city] AS [city], 
[Extent1].[state] AS [state], 
[Extent1].[country] AS [country]
FROM [dbo].[publishers] AS [Extent1]
WHERE '9901' = [Extent1].[pub_id]
GO

-- Region Parameters
DECLARE @0 VarChar(6) = 'myTtl'
DECLARE @1 VarChar(80) = 'Hamlet'
DECLARE @2 Char(12) = 'Literature'
DECLARE @3 Char(4) = '9901'
DECLARE @4 DateTime2 = '2012-04-07 00:00:00.0000000'
-- EndRegion
insert [dbo].[titles]([title_id], [title], [type], [pub_id], [price], [advance], [royalty], [ytd_sales], [notes], [pubdate])
values (@0, @1, @2, @3, null, null, null, null, null, @4)

In the top part, EF is getting the linked record from the publishers table. For this specific add, the get is probably not necessary. I could avoid that call setting the pub_id directly as opposed to setting the publisher navigation property to the row.

The second part is a basic INSERT query with parameters. Other than the cryptic names and explicitly referencing null in the VALUES clause, this is pretty close to what I would have written.

Edit

Given this function:

void editTitle(PubsModel.pubsEntities context)
{
     var myTitle = context.titles.Where(t => t.title_id == "myTtl").Single();

     myTitle.price = 100m;
     myTitle.advance = 1000000m;
    
     context.SaveChanges();
}

I get this SQL from LINQPad:

SELECT TOP (2) 
[Extent1].[title_id] AS [title_id], 
[Extent1].[title] AS [title], 
[Extent1].[type] AS [type], 
[Extent1].[pub_id] AS [pub_id], 
[Extent1].[price] AS [price], 
[Extent1].[advance] AS [advance], 
[Extent1].[royalty] AS [royalty], 
[Extent1].[ytd_sales] AS [ytd_sales], 
[Extent1].[notes] AS [notes], 
[Extent1].[pubdate] AS [pubdate]
FROM [dbo].[titles] AS [Extent1]
WHERE 'myTtl' = [Extent1].[title_id]
GO

-- Region Parameters
DECLARE @0 Decimal(7,4) = 100
DECLARE @1 Decimal(11,4) = 1000000
DECLARE @2 VarChar(6) = 'myTtl'
-- EndRegion
update [dbo].[titles]
set [price] = @0, [advance] = @1
where ([title_id] = @2)

Not much to see here. It is getting the data with a SELECT command and then editing it with the data with an UPDATE command. Again this is pretty close to what I would do.

Delete

Given this function:

void delTitle(PubsModel.pubsEntities context)
{
     var myTitle = context.titles.Where (t => t.title_id == "myTtl").Single ();
     context.DeleteObject(myTitle);
     context.SaveChanges();
}

I get this SQL from LINQPad:

SELECT TOP (2) 
[Extent1].[title_id] AS [title_id], 
[Extent1].[title] AS [title], 
[Extent1].[type] AS [type], 
[Extent1].[pub_id] AS [pub_id], 
[Extent1].[price] AS [price], 
[Extent1].[advance] AS [advance], 
[Extent1].[royalty] AS [royalty], 
[Extent1].[ytd_sales] AS [ytd_sales], 
[Extent1].[notes] AS [notes], 
[Extent1].[pubdate] AS [pubdate]
FROM [dbo].[titles] AS [Extent1]
WHERE 'myTtl' = [Extent1].[title_id]
GO

-- Region Parameters
DECLARE @0 VarChar(6) = 'myTtl'
-- EndRegion
delete [dbo].[titles]
where ([title_id] = @0)

This one is a little bit more controversial. Do I really need to get the doomed record before I delete it? Does it really make that much difference? I probably wouldn’t get the record before I deleted it. I have seen examples where you send an update command through EF that deletes the record without ever reading it.

LINQPad Code

const string EF_CONNECTION_STRING = 
@"metadata=res://*/PubsModel.csdl|res://*/PubsModel.ssdl|res://*/PubsModel.msl;" +
@"provider=System.Data.SqlClient;" +
@"provider connection string=" +
@"'data source=WIN-SEVEN-02\SQLEXPRESS;initial catalog=pubs;" +
@"integrated security=True;" + 
@"MultipleActiveResultSets=True;App=EntityFramework'";
void Main()
{
PubsModel.pubsEntities context = new PubsModel.pubsEntities(EF_CONNECTION_STRING);
addTitle(context);
showMyTitle(context, "After Add");
editTitle(context);
showMyTitle(context, "After Edit");
delTitle(context);
showMyTitle(context, "After Delete");

}
void addTitle(PubsModel.pubsEntities context)
{
var newTitle = new title
{
title_id = "myTtl",
title1 = "Hamlet",
type = "Literature",
pubdate = DateTime.Today,
publisher = context.publishers.Where(p => p.pub_id == "9901").Single()
// If I set the ID, I can avoid a SELECT command
//pub_id = "9901"
};
context.titles.AddObject(newTitle);
context.SaveChanges();
}
void editTitle(PubsModel.pubsEntities context)
{
var myTitle = context.titles.Where(t => t.title_id == "myTtl").Single();

//    myTitle.EntityState.Dump("EntityState Before Edit");
isDirty(myTitle).Dump("isDirty Before Edit");
myTitle.price = 100m;
myTitle.advance = 1000000m;
//    myTitle.EntityState.Dump("EntityState After Edit");
isDirty(myTitle).Dump("isDirty After Edit");

context.SaveChanges();
}
void delTitle(PubsModel.pubsEntities context)
{
var myTitle = context.titles.Where (t => t.title_id == "myTtl").Single ();
context.DeleteObject(myTitle);
context.SaveChanges();
}
void showMyTitle(PubsModel.pubsEntities context, string message)
{
context.titles.Include("publisher").Where(t => t.title_id == "myTtl").Dump(message);
}
bool isDirty(EntityObject entity)
{
return entity.EntityState == EntityState.Added || 
entity.EntityState == EntityState.Modified || 
entity.EntityState == EntityState.Deleted;
}

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.

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.