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.

That was just my humble plug for LINQPad.  I’m sure almost everyone by now is using this incredible tool.  Do Joseph and yourself a favor and donate to his cause by purchasing an Autocompletion license.  It’s like having the lights turned on in a pitch black room.  Of course LINQPad gets even more productive when you start adding your own helpers via extension methods and referenced libraries.  This was evident in my previous posts about batch updating and deleting (see the Quick Link articles).  I’m going to explain a few more helpful extension methods to add to your toolbox to make LINQPad your most used and most productive software tool you use.

Quick Links to All Articles

Batch Updates and Deletes with LINQ to SQL
LINQ to SQL Batch Updates/Deletes: Fix for ‘Could not translate expression’
I’ve Left Query Analyzer Hell For LINQPad Heaven

Summary

Here are some quick links within this article to take you directly to the features listed below.

Bug Fixes
Missing Primary Key
Client Side Processing
Name Mapping

Improvements
Simple Where Clause Overrides
Batch Selecting
Delete By Primary Key
Query Analyzer SQL
LINQPad Dump To CSV

Bug Fixes

Since all my extension coding started with the batch updating and deleting extension methods, I’ll list a few bug fixes to those methods first.  Please take time to read the previous articles to gain a better understanding of the fix if you are not familiar with the batch extension methods.

Missing Primary Key – A major part of enabling my batch updating and deleting to work relies on the fact that the table you are working with has a primary key so the generated SQL can create a valid INNER JOIN.  I’ve improved the code to throw a more obvious error if a table is missing a primary key (as opposed to a simple null exception)

Client Side Processing – When using the batch updating, sometimes you’ll use C# functions that are not supported natively by the database engine (one of the main beauties of LINQ is the fact that you can do this without caring).  Take the following statements as an example:

var data =
from h in HistoryData
where h.his.Groups.gName == "GroupX"
select h;// Attempting to surround current hisIndex field with ! !
HistoryData.UpdateBatch(
data,
h => new HistoryData {
hisIndex = string.Format( "!{0}!", h.hisIndex )
} );

In using string.Format(), this has forced LINQ to SQL to do processing on the client machine because that function was not natively supported by the SQL Provider.  Luckily, it threw an error in my original code so you weren’t stuck with a bad update or anything, but the error was pretty vague.  I now throw a custom error explaining that you need to use alternate methods to perform your batch update.  In this example you could simply put “!” + h.hisIndex + “!” 🙂

Name Mapping – When using the batch updating, obviously the code needs to translate the name from the DataContext table/field to match the appropriate names in the database.  I had a slight bug where the mapping could not be found.  The following code listing displays the important bits where I changed my code (thanks Moez for catching this one).

private static string GetDbSetStatement<TEntity>(
MemberInitExpression memberInitExpression,
Table<TEntity> table,
DbCommand updateCommand ) where TEntity : class
{
...
// Used to look up actual field names when MemberAssignment is a constant,
// need both the Name (matches the property name on LINQ object) and the
// MappedName (db field name).
var dbCols =
from mdm in metaTable.RowType.DataMembers
select new { mdm.MappedName, mdm.Name };...

// Get the real database field name. binding.Member.Name is the 'property' name
// of the LINQ object so I match that to the Name property of the table
// mapping DataMembers.
string name = binding.Member.Name;
var dbCol = (
from c in dbCols
where c.Name == name
select c ).FirstOrDefault();

...

// use the MappedName from the table mapping DataMembers - that
// is field name in DB table.
if ( constant == null )
{
setSB.AppendFormat( "[{0}] = null, ", dbCol.MappedName );
}
else
{
// Add new parameter with massaged name to avoid clashes.
setSB.AppendFormat( "[{0}] = @p{1}, ",
dbCol.MappedName,
updateCommand.Parameters.Count );

updateCommand.Parameters.Add(
new SqlParameter(
string.Format( "@p{0}", updateCommand.Parameters.Count ),
constant ) );
}
...
}

Improvements

I want to say thanks to all the comments on the first two articles of this ‘series’ and many of the improvements below are from that direct feed back.  I just want to be clear I didn’t come up with all these ideas on my own 🙂

Simple Where Clause Overrides – In both batch updating and deleting, I added an override that takes an Expression<Func<TEntity, bool>> object instead of a IQueryable<TEntity>.  If you are only ever performing batch operations with *extremely* simple where conditions, the IQueryable<TEntity> is a bit too verbose.  Take the following example:

// Delete history records for GroupX
var deletes =
from h in HistoryData
where h.Profiles.Groups.gName == "GroupX"
select h;HistoryData.DeleteBatch( deletes );

// Alternatively, you can do the following
HistoryData.DeleteBatchPreview( h => h.Profiles.Groups.gName == "GroupX" );

Please see my comment for my reasoning for keeping in the IQueryable<TEntity> override.

Batch Selecting – Thanks to the great article from Tony Wright, I simply completed my batch obsession by adding Batch Selects.  Read Tony’s article for a full explanation, but my code contains an extension method on a DataContext so you can write code like this (the code is pretty much the same as Tony’s):

var users =
from u in Users
where u.uAdmin == true
select new MyUser { Name = u.uAuthID, Email = u.uEmail };var groups =
from g in Groups
select g;

var mr = this.SelectMutlipleResults( new IQueryable[] { users, groups } );

var userResult = mr.GetResult<MyUser>();
var groupResult = mr.GetResult<Groups>();

userResult.Dump();
groupResult.Dump();

Delete By Primary Key – Omar AL Zabir  had come up with a simple yet clever extension idea to allow you to delete a single row from a table by passing a primary key ‘object’ to a method.  I improved on his code in the following ways

1. Constructing ANSI SQL by hand is in the best case scenario, scary.  I’ll leave that to Microsoft.  So similar to batch updating and deleting, I simply leverage the SQL Provider to do that for me.
2. I quoted object above because if you have a composite key, you need to pass in multiple values.  You can pass in objects (either an anonymous type or the Linq object type contained by the table being deleted from) that contain multiple properties to match each field that composes the primary key.
3. Personal preference, so I can’t really say it is an improvement, but I created my DeleteByPK method as an extension method against a Linq.Table.  It seemed more intuitive there.
4. Based on the data type of the primary key, I generate a more robust delete statement (i.e. placing ‘ ‘ around the value when needed).
5. Validiation of the primary key object being passed in…if its type does not match the data type of the database’s primary key field, an exception is thrown.
6. Of course, I package all this up with a *Preview method so you can verify the generated ANSI SQL before executing it.

The following are examples of how you could leverage the DeleteByPK extension method.

// Can pass in simply object types (i.e. int, Guid, string)
// if that is your primary key
Memberships.DeleteByPK( Guid.NewGuid() );/* Produces following SQL
DELETE [Membership]
WHERE [mcKey] = @p0
-- @p0: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0)
-- [94e51fd8-a95f-4aa3-81d9-7af599a684b2]
*/

// Can pass in a new object based on the entity type contained by the
// table the operation is executed against
MembershipImpersonations.DeleteByPK(
new MembershipImpersonation {
mimMembershipKey = Guid.NewGuid(),
mimClientKey = Guid.NewGuid() } );

// Note, can use anonymous types too, but the property names must
// match the property names on the MembershipImpoersonations object
MembershipImpersonations.DeleteByPK(
new {
mimMembershipKey = Guid.NewGuid(),
mimClientKey = Guid.NewGuid() } );

/* Both produce following SQL
DELETE [MembershipImpersonation]
WHERE [mimMembershipKey] = @p0, [mimClientKey] = @p1
-- @p0: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0)
-- [1eccdb99-1841-4a79-b273-eb43eb641c15]
-- @p1: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0)
-- [6f9b8ddb-acd2-42f3-9f75-eb35421e7574]
*/

Query Analyzer SQL – It’s just a sucky fact of life…you are still going to have to work with people who insist on using Query Analyzer and/or SQL Management Studio.  I’ve introduced a couple extension methods to allow you to create SQL that is ‘ready to run’ in these old fashioned software tools.  DataContext.PreviewSQL( IQueryable query ), Table.UpdateBatchSQL( … ), and Table.DeleteBatchSQL( … ) is the life line you need to ensure you never have to leave the comfort of LINQPad and LINQ to SQL.

var filtered =
from c in Customers
where c.Name.StartsWith( "SQL" )
select c;this.PreviewSQL( filtered ).Dump();

/* Generates this output:
DECLARE @p0 NVarChar( 4 ); SET @p0 = 'SQL%'

SELECT [t0].[ID], [t0].[Name]
FROM [Customer] AS [t0]
WHERE [t0].[Name] LIKE @p0
*/

LINQPad Dump To CSV – As any user of LINQPad knows, your result sets are limited to 1000 rows because LINQPad simply renders HTML to a browser window and Joseph made the decision to cut off at that point for performance reasons.  95% of the time, this is probably fine, but every once in a while I need to produce simple listings (i.e. 1-3 fields) and make sure I get all rows.  In my darker days, I used QueryEx, wrote ANSI SQL, copied and pasted the results to NotePad and shipped them off to whomever requested them, letting them deal with getting it in a format they wanted (almost always csv).  Well, those days are gone.  I’ve added an extension method to IQueryable that will dump the results (all of them) to CSV.  If one of the fields/properties selected from the IQueryable is an EntitySet (i.e. rows from a foreign relation), those values will not be dumped out.  Only the Type information will be dumped in this situation.

As a side note, I have to say thanks to Christian Liensberger for a little extension method to determine if a type is anonymous or not.  That bit of code helped me in a few of my extension methods.

So grab the code and let me know how it goes.  If you have any other favorite LINQ to SQL extension methods or helper methods to make LINQPad that much more productive, let me know!