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;
}