Home > C#, Extension Methods, LINQ > I’ve Left Query Analyzer Hell For LINQPad Heaven

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!

kick it on DotNetKicks.com

About these ads
Categories: C#, Extension Methods, LINQ
  1. Dan H
    December 10, 2008 at 1:54 pm | #1

    Great stuff! Thank you for the update to your LINQ extension library. I just ran into the name mapping bug today,and was relieved to see you already addressed it. I like the batch select too.

  2. Kelly
    January 25, 2009 at 6:15 am | #2

    LinqPad uses a lot of memory. Just opening it and doing nothing and it’s already the second biggest hog on my machine.

  3. July 4, 2009 at 3:16 am | #3

    I’m not having any luck figuring out how to get this to work in LINQPad.
    I’ve built the dll and added the reference but:

    var filtered =

    from b in BLogs

    where b.Dt.Date==DateTime.Today

    select b;
    PreviewSQL( filtered ).Dump();
    [\code]
    says "The name ‘PreviewSQL’ does not exist in the current context"

  4. July 4, 2009 at 4:01 am | #4

    var filtered =
    from p in Profiles
    where p.pDateUpdated > DateTime.Today
    select p;

    this.PreviewSQL( filtered ).Dump();

    This works for me. So you added the reference, did you also add the namespace in the ‘Advanced Query Properties’? You should add ‘BTR.Core.Linq’ in there.

  5. July 4, 2009 at 9:57 am | #5

    No I thought the namespace would be somethe DLL name, figured it would more easily import straight into LingPad.

  6. July 4, 2009 at 6:47 pm | #6

    I have linqPad and dropped your link to http://www.aneyfamily.com/terryandann/BlogAssets/LinqBatchPost.zip into visual studio 2008. Built that into a DLL which came out as LinqPost.dll. I had not added anything to the Additional namespace imports, but looking at some of your other posts perhaps the DLL should be built differently or I’m missing some code? I have now tried adding what you listed BTR.Core.Linq as a namespace import, and it does not complain like it does if I try LinqPost. So it’s finding the namespace, and the DLL is added, but it still says, "The name ‘PreviewSQL’ does not exist in the current context".

  7. July 4, 2009 at 6:48 pm | #7

    Also this code

    void Main()
    {
    var filtered =

    from b in BLogs

    where b.Dt.Date==DateTime.Today

    select b;
    BTR.Core.Linq.PreviewSQL( filtered ).Dump();
    }

    says The type or namespace name ‘PreviewSQL’ does not exist in the namespace ‘BTR.Core.Linq’

  8. July 8, 2009 at 11:54 pm | #8

    Brandon, I sent you an email asking for you *.linq script along with your assembly so I can take a look. Will comment more after looking at the code.

  9. July 10, 2009 at 12:15 am | #9

    This is great. I believe the sooner to convert, the better it is and more professional it is. The neater the better.

  10. August 21, 2009 at 12:48 am | #10

    "LinqPad uses a lot of memory." That’s saying it lightly… Linqpad sucks the living essence from you computer.

  11. May 10, 2011 at 12:05 am | #12

    I’ve updated the code base to fix a problem a user named Hongwei discovered when you end up with a DBML with a table like this – dbo.[TableName] and then columns with [ ] around the column names. My code was mistakenly adding two [[ or ]] to the *Batch statements. Thanks Hongwei.

  12. Ben
    May 25, 2012 at 6:30 pm | #13

    I love the changes, nice work…

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 163 other followers

%d bloggers like this: