L2S Extensions…MVC style

Four years later…I’ve finally come up for breath.  I hope to start a better pattern of trying to get some technical posts created once in a while.  Until I have my first inspiration – that I feel would be beneficial to anyone in the WWW, I thought I’d provide a little update to my L2S Extensions.

Here is what has changed since my last post 4 years ago.  I’m going to make the assumption that since there was really only three new improvements, that the code most have been pretty solid .  Some of the changes below were due to the fact that the main code I generate for my company has shifted to the ASP.NET MVC framework and have started leveraging the System.ComponentModel.DataAnnotations namespace to decorate pretty much everything.  Since I was querying some of those same classes, it made sense to update my L2S Extensions code to obey some attributes as well.  All the changes below occur in DumpCSV().

1. Control what is exported by leveraging the ScaffoldColumn attribute.
2. Exports all fields and properties of your IQueryable object.  Before, if the individual entities had properties, it only tried to export those and ignored fields.  (Thanks to a reader Jason D for the code change).
3. Only fields/properties whose memberType satisfies the following will be exported:
   

   new [] { typeof( string ), typeof( ValueType ) }
               .Any( t => t.IsAssignableFrom( memberType ) ) ) 

4. When determining column headers for output, it looks for a Display attribute and leverages that if available.
5. DumpCSV() has an override that takes a delegate for translating values.  This is useful, if your CSV dump should contain labels associated with the ‘keys’ stored in the database.
6. Finally, it was updated and compiled against .NET 4.0.  No code changes were really needed, so if you aren’t on that framework yet, I’d think you could just add the class files to a 2.0 and greater framework project and work just fine.

Here is an example of the Translate delegate I’ve used in some of my scripts.

[TypeMapping( typeof( Lookups ), "StateValue", "Key", "StateText" )]
[Display( Name = "State" )]
public virtual State? State { get; set; }


public object TranslateValue( MemberInfo m, object row )
{
    var p = m as PropertyInfo;
    var f = m as FieldInfo;
    
    var lookupAttr = m.GetAttribute<TypeMappingAttribute>();
    
    if ( p != null )
    {
        return lookupAttr != null
            ? Expression
                .Lambda( Expression.Constant( p.GetValue( row, null ) )
                .ConvertToData( lookupAttr ) )
                .Compile().DynamicInvoke()
            : p.GetValue( row, null  );
    }
    else
    {
        return lookupAttr != null
            ? Expression
                .Lambda( Expression.Constant( f.GetValue( row ) )
                .ConvertToData( lookupAttr ) )
                .Compile().DynamicInvoke()
            : f.GetValue( row );
    }
}

So this example looks a little crazy.  But essentially, we had a custom attribute (TypeMappingAttribute) that would associate a field with an Enum.  This attribute then had the information needed to get the text display (StateText in this case).  So whatever your need might be when translating a value from the actual database, if it can’t be accomplished directly with a L2S query, this override gives you the chance to massage your data before dumping it to the *.csv file.

As usual, the code can be found here.

I’ve Left Query Analyzer Hell For LINQPad Heaven

So now that LINQPad has enabled intellisense SQL Server Management Studio, Query Analyzer, and even Joseph Albahari’s (LINQPad creator) own QueryEx have all been zapped from my memory.  I’ll no longer flounder in antiquated ANSI SQL, but instead flourish in fully typed C#/LINQ code.

LINQ to SQL Batch Updates/Deletes: Fix for ‘Could not translate expression’

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).

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.

Batch Updates and Deletes with LINQ to SQL

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.

Source Control workarounds for Excel xla files

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.

Moving from an Excel xla add-in to a C# add-in

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