Home > Extension Methods, LINQ > LINQPad: Preview DELETE and UPDATE SQL before it executes

LINQPad: Preview DELETE and UPDATE SQL before it executes

Like everyone else (or at least you should be), I use LINQPad throughout the day non-stop.  Primarily for database maintenance that would have previously been done by saving a *.sql script.  However, at first when I was new to LINQ (not that I’m a complete expert now), I’d get hesitant when I was about to perform a bunch of updates and/or deletes against my production data.  Being more comfortable in Transact SQL than in LINQ, I wanted to see the SQL statements that would execute before actually calling SubmitChanges().  As plenty of posts have mentioned, it is far more than simply a LINQ to SQL execution tool, but rather:

And LINQPad is more than just a LINQ query tool: it’s a code snippet IDE. Instantly execute any C# 3 or VB 9 expression or statement block!

– Joseph Albahari (creator of LINQPad)

With a couple of extension methods, you can do exactly that: preview the SQL before it executes.

LINQ to SQL makes me nervous, I want to see the UPDATE/DELETE statement before it executes!

During standard practices, where you get an IEnumerable<T>, iterate through it, apply updates and/or deletes, then call SubmitChanges(), you can not preview the SQL that LINQ to SQL will generate until after the command(s) have actually been applied.  You have a couple of options:

  • If you applied changes and/or deletes to every item in IEnumerable<T>, in LINQPad, you could just call the Dump() extension on the list before SubmitChanges() and you’d see all the items your query returned (albeit only the first 1000 are displayed in LINQPad).
  • If you applied changes and/or deletes conditionally to T items, you could call GetChangeSet().Dump() before calling SubmitChanges() and examine the output for that (again, only the first 1000 items).

Call me paranoid, but in my earlier LINQ days, while performing one of my database maintenance tasks, I needed to update more than 1000 rows, but I also had to ensure that I didn’t touch several other thousand rows.  Besides, the fact that I could not see all the rows that were going to be updated, this is where I also ‘discovered’ that both of the above methods will apply an update and/or delete using a simple primary key filter for each row.  Assuming you don’t exceed 1000 rows, examining those lists would give you a pretty good idea of what was going to happen.   However, I exceeded the 1000 max, and I didn’t ‘trust’ the delete/update statements without seeing the actual SQL that would be executed.

In one of my previous posts, I discussed creating extension methods to allow for batch updates and/or deletes.  If you are performing deletes/updates with one of the extensions discussed in the article (get the code), it is quite easy to preview the SQL of any batch delete/update.  The method signatures are as follows:

public static string DeleteBatchPreview&lt;TEntity&gt;( 
	this Table&lt;TEntity&gt; table,
	IQueryable&lt;TEntity&gt; entities ) where TEntity : class

public static string UpdateBatchPreview&lt;TEntity&gt;( 
	this Table&lt;TEntity&gt; table,
	IQueryable&lt;TEntity&gt; entities,
	Expression&lt;Func&lt;TEntity, TEntity&gt;&gt; evaluator ) where TEntity : class

In almost all my LINQPad query files, I have script that looks something like the following where I leverage a testMode variable:


Configuring LINQPad…

To use your own extension methods, you need to reference your assembly in LINQPad as well as setting a default namespace.  Pressing F4 in LINQPad brings up the Advanced Query Properties window where these settings can be applied.  You can see my settings below:

linqpad refs_2

linqpad ns_2

[Note: I haven’t confirmed, but in an e-mail from Joseph Albahari, I think he told me he was making the System.Xml.Linq.dll reference and System.Xml.XPath namespace automatically assigned, but I have just never taken them out of my default settings to test.]

So after setting these, you can click the Set as default for new queries button and forget about it.  No more worries about what LINQ to SQL is going to do to your database, simply use one of the *Preview() extension methods to double check what query will be generated.  Of course, if you take The LINQPad Challenge like I did, sooner rather than later you’ll probably start to ‘forget’ the SQL syntax and actually become more comfortable in LINQ rather than SQL! (at least that’s happen to me)

kick it on DotNetKicks.com

Categories: Extension Methods, LINQ
  1. palenshus
    April 29, 2016 at 8:22 pm

    Hey Terry, great work, thanks for doing all this! I wanted to use your code via nuget so LinqPad could pull it in without me having to build locally. I submitted a pull request to your github repo for that, hope that’s cool!

    • May 2, 2016 at 6:23 am

      Sounds great. But I think my repo is on Bitbucket…and I don’t see a pull request.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: