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.

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

.

Comparing different Linq Providers

I’ve been playing with Linq and different "back ends" (not using "providers" since Linq to DataSets probably doesn’t qualify).

In all three examples below, I create the automatic default model with Northwind’s Products and Categories tables. The Linq queries require a join and a filter.

Linq to DataSet

Over the past year or so, I’ve been working fairly heavily what I call Linq To DataSets, you know, Linq on top of classic ADO.NET. I have written several blog entries and made a couple of Code Camp presentations on this subject.

What makes Linq to DataSets is that it uses a disconnected data model (data is eagerly loaded).

public static NorthwindDS CreateContext()
{
    NorthwindDS ds = new NorthwindDS();
    using (var catda = new CategoriesTableAdapter())
    {
        catda.Fill(ds.Categories);
    }
    using (var proda = new ProductsTableAdapter())
    {
        proda.Fill(ds.Products);
    }
    return ds;
}

public static void DoDemo()
{
    var ds = CreateContext();
    var list = from p in ds.Products
             join c in ds.Categories on p.CategoryID equals c.CategoryID
        where c.CategoryName == "Seafood"
        orderby p.ProductName
        select new
        {
            p.ProductID,
            p.ProductName,
            p.UnitPrice,
            c.CategoryName
        };

        Console.WriteLine("DataSet Demo");
        foreach (var item in list)
        {
            Console.WriteLine(
                string.Format("{0}\t{1:c}\t{2}\t{3}", 
                    item.ProductID, 
                    item.UnitPrice,
                    item.ProductName,
                    item.CategoryName));
        }
    }
}

This one requires real code to load. And everything is loaded. If I need to, I can filter at the DataAdapter level, but that requires fancy Designer work.

Linq to SQL

On my last project, we used Linq to SQL. It is easy to develop and capable, but limited. You will use SQL Server, You will not use Stored Procedures to read and write data (it does have some stored procedure support, but not even to the level of Classic ADO.NET).

public static NorthwindL2SDataContext CreateContext()
{
    return new NorthwindL2SDataContext();
}

public static void DoDemo()
{
    var context = CreateContext();
    var list = from p in context.Products
            join c in context.Categories on p.CategoryID equals c.CategoryID
        where c.CategoryName == "Seafood"
        orderby p.ProductName
        select new
        {
            p.ProductID,
            p.ProductName,
            p.UnitPrice,
            c.CategoryName
        };

    Console.WriteLine("Linq2Sql Demo");
    foreach (var item in list)
    {
        Console.WriteLine(
            string.Format("{0}\t{1:c}\t{2}\t{3}",
                item.ProductID,
                item.UnitPrice,
                item.ProductName,
                item.CategoryName));
    }
}

Creating the context in Linq to Sql is trivial, just new it up. The Linq is really similar to the Linq to DataSets that I’ve been working with for the last year.

The cool thing is that the data isn’t gotten until I ask for it in the Linq query and only the records I ask for.

Linq to Sql’s model is really close to the table structure of the underlining database. It is less capable than the Entity Framework (below), but it is also less complicated.

Entity Framework

I have also played with the Entity Framework. It is more powerful than the others but more difficult to use than the others.

public static NorthwindEntities CreateContext()
{
    return new NorthwindEntities();
}

public static void DoDemo()
{
    var ds = CreateContext();
    var list = from p in ds.Products
        where p.Categories.CategoryName == "Seafood"
        orderby p.ProductName
        select new
        {
            p.ProductID,
            p.ProductName,
            p.UnitPrice,
            p.Categories.CategoryName
        };

    Console.WriteLine("EntityFramework Demo");
    foreach (var item in list)
    {
        Console.WriteLine(
            string.Format("{0}\t{1:c}\t{2}\t{3}",
                item.ProductID,
                item.UnitPrice,
                item.ProductName,
                item.CategoryName));
    }
}

Again it is simple to make a new context.

The Entity Framework supports lazy loading and using stored procedures for Insert, Update and Delete operations.

Notice that there is no join in this query, the Category is imbedded in to the Products. The Entity Framework can work with data models that differ from the underlining data store.

The same treatment is applied to Mongo DB