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:
1: public static string DeleteBatchPreview<TEntity>( this Table<TEntity> table,
2: IQueryable<TEntity> entities )
3: where TEntity : class
7: public static string UpdateBatchPreview<TEntity>( this Table<TEntity> table,
8: IQueryable<TEntity> entities,
9: Expression<Func<TEntity, TEntity>> evaluator )
10: 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:
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:
[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)