Fluent NHibernate Subquery Stuff

Summary

I was converting some Entity Framework Linq to use NHibernate when I came across a strange error.  At first I was baffled by the error message because it was telling me that there were too many input parameters.  Doing an internet search led me to articles about how to limit the number of parameters I was passing to a stored procedure, etc. etc.  We’ve all run into these types of issues, the error message was not quite right.  Or at least until I figured out what was really going on.


First, the Error Message

The error message I was getting was this:

“The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.”

The query that produced this message had only one parameter, but I can recreate the error message (now that I know what causes it), and here’s an example of some code that can cause the error:

List<int> personList = 
    (from p in db.Query<Person>() 
     select p.id).ToList();

var departmentList =
    (from d in db.Query<Department>()
     where !personList.Contains(d.id)

     select d.name).ToList();

First, I had to create a bunch of person records, in fact I inserted over 3000 records, just to test this.  I also created 4 department records, but they don’t really matter.  When I checked what the second query was generating I realized that the first query was generating a list of id’s (duh) that were put into a “NOT IN” statement in the second query, which looks something like this:

select department0_.name as col_0_0_ from sampledata..department department0_ where  not (department0_.id in (@p0 , @p1 , @p2 , @p3 , @p4 , @p5 …

So I did some more searching on the internet and there were some insane ideas, like looping the second query and pumping 1000 variables in at one time… Here’s an example: how-can-i-use-nhibernate-to-retrieve-data-when-the-where-in-have-thousands-o.  Yikes.

Let’s face it, MS SQL server can handle a subquery with ease, so I decided to short-cut this special case (and let’s face it, subqueries are special cases only) by calling the query directly.  Here’s the code I used:

var departmentList =
  db.CreateSQLQuery(@”SELECT name 
           FROM department 
           WHERE id NOT in 
           (SELECT department FROM person)”)
    .SetResultTransformer(
        Transformers.AliasToBean<DepartmentNameRecord>())
    .List<DepartmentNameRecord>()
    .ToList();
The result will be the list of departments not containing any person records.  If you download the code and experiment with it, you can use these query commands in MS SQL to clear your tables:
delete from department
DBCC CHECKIDENT (department, RESEED, 0)
delete from person
DBCC CHECKIDENT (person, RESEED, 0)

If you don’t have the tables setup in your sampledata database, you can use these queries to create them before running your code:

USE [sampledata]
GO
/****** Object:  Table [dbo].[person]    Script Date: 05/18/2014 11:44:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[person](
[id] [int] IDENTITY(1,1) NOT NULL,
[first] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[department] [int] NOT NULL,
 CONSTRAINT [PK_person] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[department](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_department] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
Did I also mention that the above code is blazing fast?  Oh yeah.  It’s always best to profile your code after you get it working.  Then dump the raw SQL that your ORM is producing so you can analyze it in MS SQL management studio.  If it’s running fast in the ORM, then you’re good.  If there are performance problems, then maybe you need a workaround.

Download the code here: FluentNhibernate2100ErrorTest.zip

 

Fluent NHibernate More Stored Procedures Without Mapping

I did a sample post on how Fluent NHibernate executes a stored procedure, but I didn’t dig into the subject and show some details.  The sample I showed returned a list of tables that were already mapped and it made the whole sample easy, but not realistic.  In most cases we want to execute a stored procedure to return some result that is not already mapped.  So I’m going to show how this is done.


The Stored Procedure:

Use this code to generate an MS SQL stored procedure in your sampledata database (assuming you have one):

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[ReadPersonNames]

AS
BEGIN
SET NOCOUNT ON;

SELECT first, last FROM person

END


The Stored Procedure Return Class

You’ll need to create a class to contain the data type that will be returned.  Here’s what I created for this example:

public class PersonNameRecord
{
    public virtual string first { get; set; }
    public virtual string last { get; set; }

}


Call the Stored Procedure

Here’s my call to the stored procedure:

var personNameList = db.CreateSQLQuery(“exec ReadPersonNames“)
 .SetResultTransformer(Transformers.AliasToBean<PersonNameRecord>())
    .List<PersonNameRecord>()

    .ToList();


You’ll need to include a “using” statement at the top to make this work:

using NHibernate.Transform;


That’s it.  You can download the sample code here:

FluentNHibernateStoredProc2.zip

 

Fluent NHibernate Table Dump Utility

Summary

So I’ve been posting a lot about NHibernate over the past few months (Fluent NHibernate to be exact).  Mostly motivated by frustrations with Entity Framework.  I have spent the past week working with NHibernate in a production environment as a proof of concept example.  The first problem with NHibernate is the fact that you have to manually generate the classes and mappings.  So I came up with a simple and dirty solution to this problem…


The Table Dump Utility

I threw together a console application that can be used to dump all the tables of designated databases into directories.  Each table generates a “.cs” file with the class and mapping code in it.  You can download the console application here:

GenerateNHibernateTables.zip

To use the utility, change the “ConnectionString” at the top to point to your database.  Then you can change the parameter of the “DumpDatabase()” method to contain the name of the database you wish to generate tables for.  You can add additional “DumpDatabase()” methods if you want to dump a bunch of databases.  Each database will be contained in a subdirectory named the same as the database name.  All the subdirectories will be contained inside a directory on your C: drive named “NHDatabase”.  Obviously, you can hack up this program to suite your needs.  


What this program will generate

This program will generate a “.cs” file for each table that will contain the class and mapping for the table.  I have quickly thrown in some data types and what the matching class virtual getter will be (i.e. “int”, “string”, “DateTime”, etc).

Composite keys are recognized and the “Equals” and “GetHashCode” methods are automatically inserted into the class with the proper field names.

The “Table” mapping is included for convenience and can be altered.  This is setup for MS SQL server, containing the database name as well as the table name.

Nullable and not Nullable fields are accounted for.

String lengths are accounted for.


What this program will not generate

No foreign key constraints are generated.

No one-to-many or many-to-many mappings are setup.

Only the most generic field types are recognized.

No context code is generated.


What can you use this program for?

I wrote this program to reduce some of my tedious data-entry work.  You can get about 90% of the code you need for your Fluent mappings and table classes with this utility, then spend a few minutes cleaning up anything that doesn’t quite fit.  If a field type is not recognized, then the program will spit out an “unknown(sql field name)” field name.  You can decide what to name this variable and type over the unknown name.

Also, don’t forget to correct the namespace.  I threw a generic “databasename.NameSpace” text for the name space.  You should change this to match your project namespace.  You can do it in the program before you generate tables to save yourself some typing.

Also, you can rerun this program to write over the tables that exist.  Make sure you don’t have a directory open before you do it (otherwise you might lock the program out from making changes).

To use the files, just drag the “.cs” files into your project, tweak the namespace, correct any syntax errors and add your tables to your own context class.

That’s it!

 

MiniProfiler

I’m going to switch gears here for a post or two and talk about software profiling.  For those of you who have never used a profiler before, or have no idea what I’m about to talk about, here’s a quick run-down:

A profiler is an add-on program that will measure the performance of your code.  Typically, this software allows the developer to drill-down into slow sections of code to see what is hogging all the CPU cycles.  One package I’ve used in the past, and is an excellent profiler, is Redgate’s Ants profiler.  It’s worth every penny.  However, if you’re like me, you have a limited number of pennies to throw around (yes, Ants is a bit on the pricey side).  So I’m always on the lookout for something cheap but easy to use.  This is where MiniProfiler comes in.

Miniprofiler is easy to install.  It took me about 15 minutes to install it into a demonstration MVC4 application.  If you go to the MiniProfiler website: http://miniprofiler.com/, you can follow the directions step-by-step.  Use the NuGet package manager to get the versions of MiniProfiler that you need.

For a generic MVC4 program, you’ll need the following setup steps:

1. Install the NuGet packages for MiniProfiler (PM> Install-Package MiniProfiler), the MVC package (PM> Install-Package MiniProfiler.Mvc4), and if you are using Entity Framework 6 (PM> Install-Package MiniProfiler.EF6 -Pre).  There is also a package for NHibernate (which I will be testing in a future post).  I discovered the NHibernate version when I typed “miniprofiler” in the search box of the NuGet package install window.

2. Next, just like the instructions show you on the MiniProfiler.com website:

@using StackExchange.Profiling;
  ...

@MiniProfiler.RenderIncludes()

Obviously, in an MVC application, the head and body tags are not shown.  Just declare 
the using statement at the top of the view and put the RenderIncludes() method at 
the bottom.

3. Add this to the global.asax file:

using StackExchange.Profiling;

protected void Application_BeginRequest()
{
if (Request.IsLocal)
{
MiniProfiler.Start();
}
}

I also added this:

protected void Application_EndRequest()
{
MiniProfiler.Stop();
}

4. One last thing for the setup, open your Web.config and add this:

  <system.webServer>    
<validation validateIntegratedModeConfiguration="false" />
<handlers>

<add name="MiniProfiler" path="mini-profiler-resources/*" verb="*
           type="System.Web.Routing.UrlRoutingModule" resourceType="Unspecified"  
           preCondition="integratedMode" />

Now you’ll need to add some “using” statements to your code if you expect to profile sections of your code.


var profiler = MiniProfiler.Current; // it’s ok if this is null
using (profiler.Step(“Title to show for this code“))
{
… your code
}
OK, now run your MVC4 program, and you should see something like this:
You’ll notice in the upper left corner of your browser will be a tiny button with the time it took to execute your code.  Click on this button and you’ll see something like this:


You can click on the “more columns” to see more detailed information about the profile.  In my example MVC4 application, I used a controller named Home with a view named “Index”.  I also put “Read person list” inside my profiler step title, which took 20.2 ms according to the information above.
OK, now, you can decorate your program with “using” statements to wrap each granular section of code in a profiler step.  Then you can turn off your profiler by controlling the MiniProfiler.Start() method in the global asax.   
Adding Entity Framework Support

Install the MiniProfiler using NuGet 
(PM> Install-Package MiniProfiler.EF6 -Pre)

Now open your Global.asax file and add the following using statement to the top:


using StackExchange.Profiling.EntityFramework6;

Now add the initialize method to your Application_Start() method (this method should already exist):

protected void Application_Start()
{
  …
  MiniProfilerEF6.Initialize();
}
Now run your program and you should see an additional column of numbers for your sql calls (assuming you setup an EF6 data context and called a query that is wrapped in a miniprofiler step):
Notice the links in the sql column?  Click on one of these and you’ll get a break-down of what was executed:
ReadAllrecords is a method name that I created to read records from a table called “person”.  The first entity framework query looks like this:

var query = (from p in db.people 
             select p.first).ToList();

and the second query looks like this:

var query = (from p in db.people 
             select p).ToList();

The profiler spits out the actual SQL that was generated by my LINQ queries, which can be convenient to determine what is going on at the SQL server level.  You can also copy the SQL text and execute it in the MS SQL server console window to verify what is going on.

Next time, I’m planning to try out the NHibernate version.  I’ll be setting up a demo NHibernate project, populate with a large list of data (maybe I’ll copy one of my previous NHibernate projects) and I’ll show how the MiniProfiler behaves.



 

Fluent NHibernate Composite Keys

Summary

In a previous blog post, I demonstrated a composite key on a table that was used as a reference between two other tables (a many-to-many example).  This time I’m going to demonstrate the basic mapping of a composite key for a table that will be defined as a class.  This is a one-to-many example, but I’m going to ignore that part of the process and skip right to the meat of the situation.  I did some research on the internet and stumbled across this article: MyWiki: Fluent NHibernate Samples, which has exactly the example I was looking for.


The ERD

I setup a pair of tables with the minimum fields I needed to demonstrate how to do some basic CRUD operations.  The ProductId and ProductType fields inside the Product table are a composite primary key.  I did not make either key an identity key.  I’m going to just put numbers into these fields when I insert data.  I would not recommend this practice since two concurrent operations might cause a duplicate key error on insert.




The ProductType Table  Class and Mapping

I’m going to show the ProductType table definition first.  It’s just a straight mapping of the most basic kind.  There is one primary key that is an identity called “Id” and a description string field that is nullable.

public class ProductType
{
    public virtual int Id { get; set; }
    public virtual string Description { get; set; }
}

public class ProductTypeMap : ClassMap<ProductType>
{
    public ProductTypeMap()
    {
        Id(u => u.Id).GeneratedBy.Identity();
        Map(u => u.Description).Nullable();

        Table(“sampledata..ProductType“);
    }
}


The Product Table Class and Mapping

The tricky table is the product table since it has a composite primary key.

public class Product
{
    public virtual int ProductId { get; set; }
    public virtual int ProductType { get; set; }
    public virtual string Name { get; set; }
        
    public override bool Equals(object obj)
    {
        if (obj == null || GetType() != obj.GetType())
        {
            return false;
        }

        Product that = (Product)obj;

        return this.ProductId == that.ProductId && 
            this.ProductType == that.ProductType;
    }

    public override int GetHashCode()
    {
        return ProductId.GetHashCode() ^ 
            ProductType.GetHashCode();
    }
}

public class ProductMap : ClassMap<Product>
{
    public ProductMap()
    {
        CompositeId()
        .KeyProperty(u => u.ProductId)
        .KeyProperty(u => u.ProductType);

        Map(u => u.Name).Nullable();

        Table(“sampledata..Product“);
    }
}

The first thing you’ll notice is that there are two overridden methods: The “Equals” method and the “GetHashcode” method.  These must be manually handled by your code.  Next, the mapping must contain the “CompositeId()” method to define which fields belong to the composite key.  This replaced the “Id” mapping method.


The CRUD Code

I wrote a select, an insert and a delete example.  My starting data included the following:



The top 4 rows are the Product table and the bottom 4 rows are the product types.  When I do my insert, I’m going to intentionally insert a key that contains a Product Id of 1, but a different ProductType key.  This will make the composite key unique, but ensure that there is nothing messed up with the primary key being setup as the ProductId.


using (ISession db = MSSQLSessionFactory.OpenSession())
{
    var query =
        (from p in db.Query<Product>()
         join pt in db.Query<ProductType>() on 
            p.ProductType equals pt.Id
         select p).ToList();

    foreach (var item in query)
    {
        Console.WriteLine(item.Name.Trim());
    }
}

// insert new records
using (ISession db = MSSQLSessionFactory.OpenSession())
{
    Product product = new Product()
    {
        ProductId = 1,
        ProductType = 2,
        Name = “Onion
    };

    db.Save(product);
    db.Flush();
}

// delete one record
using (ISession db = MSSQLSessionFactory.OpenSession())
{
    var product =
        (from p in db.Query<Product>()
         where p.Name == “Onion
         select p).ToList();

    using (db.BeginTransaction())
    {
        foreach (var item in product)
        {
            db.Delete(item);
        }
        db.Transaction.Commit();
    }
}

If you run this example, you can put a break-point before the delete command and see that the “Onion” product was insert into the product table.  Then you can continue and the product will be deleted from the table.

You can download the sample code here:

FluentNhibernateCompositeKey.zip