I’ve found and posted a new fix in the code from my original post: Batch Updates and Deletes with LINQ to SQL. I’m not sure of the etiquette for this sort of thing: new post (like I’m doing) or just a comment in the original post. But since I did get a fair amount of hits to the article but minimal comments, people who may have downloaded the code wouldn’t get an update notification and I want to be sure to make them aware of an issue/fix (assuming they are monitoring via a RSS feed).
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.
A couple weeks ago, I read the article, LINQ to SQL Extension: Batch Deletion with Lambda Expression by Jeffrey Zhao. In case you didn’t read the article, it discusses the downside of most O/R Mapping frameworks when it comes to multiple updates or deletes. He states the fact that a SQL statement for each row flagged as update/delete in the entity set is created. I went about implementing something similar to what Jeffrey envisioned and I’ll explain some of the hurdles I had to overcome to achieve it.
In my last post I explained reasons and gotchas to be concerned with when deciding to migrate from an Excel *.xla add-in to a C# add in. One of the reasons revolved around not having an easy way to use a source control product (we use Visual SourceSafe) to manage the actual code files of a VBAProject. This is because the *.xla is a binary file and using SourceSafe (and I would assume other products) you can not compare differences of a binary file (not to mention the VBAProject files that are part of the binary blob).
I mentioned that I automated getting the code files out of the VBAProject and into SourceSafe. I am sure there third party add-ins that allow a source control product to integrate into Excel, but the code is fairly simple to automate it yourself.
At my day job, we use Microsoft Excel spreadsheets as a pseudo "specification document" (spec sheet) for the websites, which are actuarial in nature, we create. At the time (several years back), since we chose Excel, obviously we needed an add-in for the few automated processes we supported and we needed something immediately (you know how it goes in small companies). The easiest way for us to create the add-in we needed was to create an Excel add-in file (*.xla). My background (5-6 years ago) was from VB6 anyway, so even though I’d switched to C#, VB6 was still fresh in my mind and writing VBA was a breeze – whether the code was clean or not, I’ve got not comment ;). I’ve recently made the decision to migrate an existing Microsoft Excel Add-In (*.xla) file to managed C# code. There were several motivating factors to this decision along with almost as many speed bumps