Using LINQ to SQL for ETL projects

As obvious and straightforward as that sounds, I'll admit I didn't do it in the initial version of my application. It was pointed out to me by a teammate during a code review, and to his credit, improved performance for the LINQ queries where I implemented it by 10x. I found a good number of opportunities to use this technique within loops: INSTEAD OF:

foreach (var Record in SomeCollection) {
    string Code = (
        from AS in DataContext.ActivityStatus
        where AS.StatusId == Record.StatusId
        select AS.Code
    ).FirstOrDefault();
} // ('Code' value will be taken from the database each time through the loop)

DO THIS:

var ActivityCodes = (
    from AS in DataContext.ActivityStatus
    select new {AS.StatusId, AS.Code};
);
foreach (var Record in SomeCollection) {
    string Code = (
        from AC in ActivityCodes
        where AC.StatusId == Record.StatusId
        select AC.Code
    ).FirstOrDefault();
} // ('Code' value will now be taken from memory each time through the loop)

Far fewer trips to the database yields much better performance. Duh.

6. Avoid using InsertOnSubmit() whenever possible

This function is by far, hands-down, unequivocally the biggest hindrance to moving your data in a reasonable amount of time. I learned the truth about InsertOnSubmit()the hard way in my project.

Here's an example - I had a simple table with an integer ID field and a varchar(50) string field. Oh, and it contained about 1.1 million records. Despite the size, you'd think that since the data fields are so simple, it should be pretty quick to move, right? Nope. On the first run of my application, it took a whole work week just to move this one table! That's a blazing speed of about 150 records per minute. *sigh*

So now what? Scrap the project as a learning experience and start over? Nah, I don't give up that easily. After a couple hours of research, I found the solution: SqlBulkCopy. There are a number of examples online of how to implement this class, but ultimately I decided to roll my own, since most of them had extra code I didn't need. My class is available here for download. To use it, you just need to create a List of your LINQ objects, then call BulkProcessor.Cram() when you're ready to add the contents to your database:

List<DataContext.LinqObject> ObjectsToInsert = new List<DataContext.LinqObject>();
foreach (var Record in SomeCollection)
{
    DataContext.LinqObject PersonObject = new LinqObject()
    {
        Name = "A. Person",
        Address = "123 Main St.",
        City = "Anytown",
        Zip = 12345,
        Notes = Record.TextValue,
    };
    ObjectsToInsert.Add(PersonObject);
}
// List has been built; now use SqlBulkCopy to insert!
BulkProcessor.Cram(ObjectsToInsert, "PersonDetail", "[db_connection_string]");

Simple as that. Now that million record table takes minutes to move instead of days.

Conclusion

Overall, I'm quite satisfied with the result. I think building an ETL process using C# and LINQ is certainly a viable option for the right project and situation. Some final thoughts:

• I liked how easily I could make changes to the process. When the development team made changes to the database schema, it was simple for me to update my OR map and migration code to account for it. When I found data a data issue, it was easy to alter the process to make sure it didn't happen again. Literally took a few minutes in most cases.
• I now have what can be considered a prototype and / or reusable code for future ETL needs for the web application. For example, a new client comes on and wants data from their old system moved over. Most of the work is already done; I'd just need to change the source DataContext and a few select statements. Maybe they want a set of data sent to them from the system? Again, we now have most of the code needed to do this.
• Just as a point of caution, the biggest database I've used this with so far is around 2GB. I'm not sure how it would perform with a dataset 5 to 10 times larger.
• Be sure to heed my warning about InsertOnSubmit(), but be pragmatic about it. Use my result of 150 records a minute as a benchmark to determine when it's OK for you to use it instead of SqlBulkCopy.

I'd love to hear from anyone else who's attempted this with either additional tips or pitfalls to share, or with questions about my approach that I may have omitted.