LINQPad, LLBLGENPro and Npgsql

If you want to test LLBLGenPro/Npgsql queries with LINQPad, and you really should, you have to follow these steps.

  1. Install the official LLBLGenPro driver for LINQPad (Add connection, View more drivers..., ...)
  2. Reference LLBLGenPro generated assemblies (connection configuration), enter proper connection strings and, albeit optional, provide a .config file that contains DBProviderFactiories entry for Npgsql (watch out for version number)
  3. Once connection is created, add Npgsql NuGet package to query. The version should match the one configured in step #2.

And voila, beautiful interactive queries at your disposal. You can even use ORM Profiler.

Sample Npgsql factory entry:

....
<system.data>
    <DbProviderFactories>
      <!-- PostgreSql -->
      <add name="PostgreSql Client Data Provider" invariant="Npgsql" description=".Net Framework Data Provider for PostgreSql" type="Npgsql.NpgsqlFactory, Npgsql, Version=3.1.6.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
    </DbProviderFactories>
  </system.data>

Integrating MvcMiniProfiler and LLBLGenPro

MvcMiniProfiler is a lean and mean mini profiler for MVC 3 that shows the profiling results on each page displayed at runtime. Besides the custom steps you can seed everywhere in your code it supports database profiling as well. Out of the box are supported ado.net, Linq to SQL and Entity Framework. LLBLGenPro, my favorite ORM, isn’t supported though and it won’t work just like that.

Luckily, it turns out, it requires just a little effort to integrate MvcMiniProfiler into LLBLGenPro.

How does MvcMiniProfiler database profiling works

The way it works is that it wraps DbConnection, DbCommand and other Db[Stuff] and thus records the execution time by tracking their inner workings. Here is an example for MvcMiniProfiler documentation about how to start:

public static DbConnection GetOpenConnection()
{
    var cnn = CreateRealConnection(); // A SqlConnection, SqliteConnection ... or whatever

    // wrap the connection with a profiling connection that tracks timings 
    return MvcMiniProfiler.Data.ProfiledDbConnection.Get(cnn, MiniProfiler.Current);
}

If client calls DbConnection.CreateCommand on an ProfiledDbConnection instance returned from previous method it will get a wrapped whatever command original connection returns and so on. There is also a way to manually create DbCommand through ProfiledDbCommand constructor.

The support for Linq To SQL and Entity Framework is done in a similar manner.

This gets us to the point, why can’t I just use the same approach with LLBLGenPro?

Integrate MvcMiniProfiler with LLBLGenPro – why doesn’t work with same approach

The major problem with LLBLGenPro and MvcMiniProfiler integration is that LLBLGenPro doesn’t use DbConnection.CreateCommand method to create commands from existing connection. Instead it creates an instance of proper DbCommand derived class and assigns a connection to it. Thus it won’t work because it would try to assign a ProfiledDbConnection to a i.e. SqlCommand class.

So a bit more work is required to match them.

The code for adapter scenario

1. Create a DynamicQueryEngine derived class. Note: this class is database specific, thus if you work with i.e. SQL Server you’ll find it in SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll assembly.

public class ProfilingDynamicQueryEngine : DynamicQueryEngine
{
    protected override DbCommand CreateCommand()
    {
         DbCommand cmd = base.CreateCommand();
         ProfiledDbCommand pCmd = new ProfiledDbCommand(cmd, null, MiniProfiler.Current);
         return pCmd;
    }
}

Here the DbCommand creation is overriden. Note that I wrap the original cmd and pass a current MiniProfiler instance as arguments to ProfiledDbCommand constructor, while I pass a null for the connection instance because it will be assigned later.

2. Derive from DataAccessAdapter class. Note: this class is generated from a template and you’ll find it in DBSpecificLayer project generated by LLBLGenPro.

public class DataAccessAdapterEx: DataAccessAdapter
{    
    protected override System.Data.Common.DbConnection CreateNewPhysicalConnection(string connectionString)
    {
        DbConnection conn = base.CreateNewPhysicalConnection(connectionString);
        // return ProfiledDbConnection.Get(conn); Pre MvcMiniProfiler 1.9
        return new ProfiledDbConnection(conn, MiniProfiler.Current);
} protected override DynamicQueryEngineBase CreateDynamicQueryEngine() { return PostProcessNewDynamicQueryEngine(new ProfilingDynamicQueryEngine()); } }

Within CreateDynamicQueryEngine I pass the class I’ve created in step #1. CreateNewPhysicalConnection will return a wrapped connection.

Instead of using DataAccessAdapter you should use the one created in step #2 - DataAccessAdapterEx. That’s it.

Conclusion

As it turns out, integrating MvcMiniProfiler with LLBLGenPro is quite easy. And the required coding might be added to LLBLGenPro templates by modifying them, so you won’t have to manually add the same code each time.

Let me know if you have feedback.

Update 19.9.2011: Updated the code because MvcMiniProiler introduced a breaking change in v1.9 (instead of ProfiledDbConnection.Get static method a constructor has to be used - thanks for swift response from David from LLBLGenPro support team)

Using extension methods to make code less cluttered

Here is an example where an extension method is useful. Take this [LGP] code for example:

public EntityCollection<CustomersEntity> GetCustomers() { using (DataAccessAdapter da = new DataAccessAdapter("Data Source=[SERVER];Initial Catalog=Northwind;Integrated Security=True")) { LinqMetaData context = new LinqMetaData(da); var q = from c in context.Customers select c; EntityCollection<CustomersEntity> result = ((ILLBLGenProQuery)q).Execute<EntityCollection<CustomersEntity>>(); return result; } }

It is a simple LINQ to LLBLGenPro select against (we all love) Northwind database. Pay attention at result assignment. To have data returned as a native [LGP] EntityCollection you have to resort to ILLBLGenProQuery interface. You have to use ILLBLGenProQuery.Execute method on IQueryable<T> q. A cast is obviously required because IQueryable<T> doesn't know anything about ILLBLGenProQuery. While this code works it is not exactly elegant - I am referring to the later cast of course.

So, the idea is to have something like this instead:

EntityCollection<CustomersEntity> result = q.Execute<EntityCollection<CustomersEntity>>();

Possible? Sure, just add this static class with a single extension method and you are good to go:

public static class Tubular { public static TResult Execute<TResult>(this IQueryable q) where TResult : IEntityCollection2 { return ((ILLBLGenProQuery)q).Execute<TResult>(); } }

Or perhaps, we can further reduce the cluttering by introducing this extension method:

public static class Tubular { public static EntityCollection<TEntity> Execute<TEntity>(this IQueryable q) where TEntity : EntityBase2, IEntity2 { return ((ILLBLGenProQuery)q).Execute<EntityCollection<TEntity>>(); } }

to achieve this, even less cluttered, line:

EntityCollection<CustomersEntity> result = q.Execute<CustomersEntity>();

Isn't this or former de-cluttered line more readable and more elegant than the original?

Righthand has joined LLBLGenPro partners

I am proud to announce that today, after years of experience with LLBLGenPro development, I've become an official LLBLGenPro partner.

 

As such I am offering consultancy and development with LLBLGenPro, the #1 ORM product for .net. Do you want to speed up your database application development, reduce the number of errors in code, develop a database independent application and, most importantly, reduce the development costs? I firmly believe that LLBLGenPro is an excellent product that addresses all of these challenges.

If you are interested, contact me through contact form or through e-mail or any other way...

LINQ to SQL showstoppers

I have to say that I like LINQ to SQL, heck, I even do presentations about it. So, it is a cute ORM. What is that good about it features LINQ. However, there are some serious showstoppers in there. One are more serious than the others. I won't deal with what's good about LINQ to SQL, rather I'll dive into showstoppers as I see them. So, let's take a look at them:

  1. Support for SQL Server flavors only, not even Katmai (SQL Server 2008).
    This might or might be a showstopper for you. One of the great advantages of ORM products is that they are abstracted from database, meaning the same code works with different supported databases. You probably won't write an application that is totally abstracted and you'll have to write few database dependant stuff but it is still true for the majority of the code. So, why would I bother learning an ORM that deals with SQL Server only when I have plenty of other ORMs that supports a bunch of different databases? Why would I bother to tie my application to SQL Server only when I could have an application that works with all the major databases out there?
  2. Poor n-tier support
    A good ORM would let you send all the modified entities to the server tier with a few of method calls and store changes to the database with few other method calls. But not LINQ to SQL. Oh, no. Doing n-tier with LINQ to SQL requires a fair amount of coding and lot of manual work that should be done by LINQ to SQL itself. IOW LINQ to SQL is very client-server oriented. Looks like n-tier was added afterwards.
  3. Designer that doesn't sync with database
    Imagine you have a Context with myriad of entity types on it. That's very messy by itself since you can't isolate a certain set of entity types you might be interested in but you always have to look at all of them. But that's not the real issue here. The real problem arises when database metadata changes (right, you always define your database structure in stone before you start coding and you don't modify it ever) - perhaps this is a bad project management, but it does change through the life of the project. And when it does, LINQ to SQL won't help you modifying the Context. Nope, you are on your own. So, you can recreate the context manually and reapplying all the modifications done to it (you are certainly looking forward to this) which is not only boring, but error prone, too. Or you can manually sync the Context with database which is not that boring but error prone and tedious. So, this one is a huge showstopper if you ask me.
    Luckily there is [CodeSmith] with its PLINQ templates that supports database synchronization. While not a perfect solution it solves many problems. Furthermore it allows you to modify the templates to your requirements - IMO the [CodeSmith] path is the only path if you are serious about LINQ to SQL.
  4. No source code
    [MS] is probably releasing LINQ to SQL source code, but even if they does, you won't be able to fix problems as you can't modify it in any way. Not a big problem.
  5. Slow development cycle (tied to .net 3.5)
    If you need a fix quickly you will have problems as LINQ to SQL is part of .net 3.5 framework. And knowing the speed of [MS] applying bugfixes it might take years before official update is available.
  6. No direct execution against database
    Imagine you have a task that has to update all item prices for 10% for whatever reason (it acually happened last month in Slovenia for bread and milk items). All orders have to be fetched from the database to the application, each order has to be properly update in application and all of them have to be persisted back to database. Right, you can't execute the operation directly against database from the code. OK, you can issue a SQL statement or use a stored procedure but the former breaks strong typing (SQL statement is a string, compiler won't help you catching the errors) while the later is unnecessary burden.
  7. There is plenty of better competitors out there
    Apart from the (obviously) LINQ advantage there is plenty of better and proven ORM products out there. Even the LINQ, major LINQ to SQL's advantage will soon be integrated by the competition as some of them already feature LINQ (to Entities) while others will soon.

Again, don't take me wrong. LINQ to SQL has plenty of good features. It is just that it has too much of show stoppers for me and knowing problems of certain technology before using it is usually a valuable knowledge. So, what can you do? Pick an alternative of course, either one of commercial or free 3rd parties or wait for Entity Framework which will see light next year, sometime after .net 3.5 RTM.

What will I do? Actually nothing. I am very happy with [LGP] that is much superior to LINQ to SQL except for the LINQ. Also [LGP] is getting LINQ support supposedly this year so it shouldn't be long before I am be able to happily LINQing with ORM.

And let me know if there are other problems you foresee with LINQ to SQL.

LLBLGen Pro got even better

Frans released a new major version of his [LGP] ORM. Basically, a top notch ORM product got even better. Make sure you check the what's new list - and if you are looking for an (better) ORM, don't hesitate to give it a try, you won't regret it.

The only feature I miss is LINQ support which is supposed to appear in the next version. Actually, including LINQ support isn't that useful right now as Visual Studio 2008 aka Orcas isn't supposed to ship until very late 2007/beginning of 2008. Frans is actually wise to wait a little bit more with LINQ and instead concentrate on actual improvements which help developers right now. That said I am really waiting to see LINQ for [LGP] when VS 2008 gets released. I suspect LINQ will make [LGP] (and any other ORM product out there) code much much more readable that it is now (limited by current .net languages).

Keep up the excellent work, Frans.

Implementing more useful tracing for LLBLGenPro 2.0

[LGP] has an internal tracing mechanism that outputs data to Trace class using standard .net mechanism. To enable various tracing categories you have to switch one various TraceSwitches either at runtime or in config file.

But what if you want to execute the same select directly in Sql Server Management Studio perhaps? The reason might be your curiosity. More valid reason would be to check the generated sql statement as it might behave poorly of badly (or not at all) if it isn't constructed properly. And finding why in a huge statement soon becomes picking a needle in haystack.

Find the solution in this article.