Building a MVC2 Template, Part 16, Fun With NHibernate

Oct 04

Eric.Net, Development Asp.Net Mvc, Nehemiah Project, NHibernate 2 Comments

In the last post on building an MVC2 Application Template we stopped just short of implementing the repository for the membership, role, and profile providers. I have all the code and specs written for an NHibernate implementation of the provider repository. Issues started arising when I began validating the specifications. Those issues are presented here.

A Review

The membership provider utilizes three tables, User, Role, and a mapping table named UserToRole. There are two classes used in the provider repository, User and Role. User has a many-to-many mapping as shown below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FluentNHibernate.Mapping;
using Nehemiah.Data.Models;

namespace Nehemiah.Data.NHibernate.Mappings
{
    public class UserClassMap : ClassMap<user>
    {
        public UserClassMap()
        {
            Table("[User]");
            Id(x => x.UserId).Column("UserId").Unique().GeneratedBy.Guid();
            Map(x => x.ApplicationName).Column("ApplicationName").UniqueKey("AppUser").Not.Nullable().Length(50);
            Map(x => x.UserName).Column("UserName").UniqueKey("AppUser").Not.Nullable().Length(50);
            Map(x => x.Email).Column("Email").Not.Nullable().Length(255);
            Map(x => x.Comment).Column("Comment").Nullable().Length(255);
            Map(x => x.Password).Column("Password").Not.Nullable().Length(50);
            Map(x => x.PasswordQuestion).Column("PasswordQuestion").Nullable().Length(255);
            Map(x => x.PasswordAnswer).Column("PasswordAnswer").Nullable().Length(50);
            Map(x => x.LastActivityDate).Column("LastActivityDate").Nullable();
            Map(x => x.LastLoginDate).Column("LastLoginDate").Nullable();
            Map(x => x.LastPasswordChangedDate).Column("LastPasswordChangedDate").Nullable();
            Map(x => x.CreationDate).Column("CreationDate").Not.Nullable();
            Map(x => x.Approved).Column("Approved").Not.Nullable();
            Map(x => x.Online).Column("Online").Not.Nullable();
            Map(x => x.LockedOut).Column("LockedOut").Not.Nullable();
            Map(x => x.LastLockedOutDate).Column("LastLockedOutDate").Nullable();
            Map(x => x.FailedPasswordAttemptCount).Column("FailedPasswordAttemptCount").Not.Nullable();
            Map(x => x.FailedPasswordAttemptWindowStart).Column("FailedPasswordAttemptWindowStart").Nullable();
            Map(x => x.FailedPasswordAnswerAttemptCount).Column("FailedPasswordAnswerAttemptCount").Not.Nullable();
            Map(x => x.FailedPasswordAnswerAttemptStartWindow).Column("FailedPasswordAnswerAttemptStartWindow").Nullable();

            HasManyToMany<role>(x => x.Roles)
                .Not.LazyLoad()
                .Table("UserInRole")
                .ParentKeyColumn("UserId")
                .ChildKeyColumn("RoleId")
                .NotFound.Ignore()
                .Cascade.All();
        }
    }
}

Specifications

There are presently 201 specifications for the MVC2 application template project. Of those 43 are specifically for the provider repository. All but two of the specifications passed testing.

Originally the NHibernate provider repository specification was setup to use an in-memory database. Next I changed that to using an empty SQLite database with the schema being built each time the spec project was compiled. Finally I settled on using a SQL Express MDF file with an existing schema. Why I switched is explained later.

The specification initialization includes creating several roles and saving them to the database. Next a user is created and a role is added to the user, then saved. So at the end of the initialization I should have 7 Roles, 13 Users and 15 UserToRole records in the database. The count for Roles is correct. The count for Users is correct. The count for UserToRole is zero.

Creating a UserToRole mapping class blows up any code that attempts to access either the User or Role table. Ok, fine. While I would like to have this specification pass testing, I have other specifications that retrieve User records and they have the correct number of Roles. So I can say the mapping is working.

The second specification that failed testing was the specification that retrieved a list of all users for a given role. The list being returned was empty. A closely related specification returns a count of users in a given role. The linq query for both is nearly identical as shown below.

    return Session
            .CreateSQLQuery(@"SELECT Count(*) AS cnt
                                FROM `User` U
                                INNER JOIN UserInRole X ON X.UserId = U.UserId
                                INNER JOIN Role R ON R.RoleId = X.RoleId
                                WHERE U.ApplicationName = ? AND R.RoleName = ?"
                                )
            .AddScalar("cnt", NHibernateUtil.Int32)
            .SetString(0, applicationName)
            .SetString(1, rolename)
            .UniqueResult<int>();

and

    return Session
            .CreateSQLQuery(@"SELECT U.*
                              FROM `User` U
                              INNER JOIN UserInRole X ON X.UserId = U.UserId
                              INNER JOIN Role R ON R.RoleId = X.RoleId
                              WHERE U.ApplicationName = ? AND R.RoleName = ?")
            .AddEntity(typeof(User))
            .SetString(0, applicationName)
            .SetString(1, rolename)
            .List<user>();

The specification that returns the count of users in a role passes testing, while the specification that returns that list of users fails testing.

The Problem

Checking the log file showed the SQL script that was generated to create the RoleToUser mapping table. It was not what I expected!

create table UserInRole (
       Role_id INTEGER not null,
       User_id UNIQUEIDENTIFIER not null,
       UserId UNIQUEIDENTIFIER not null,
       RoleId INTEGER not null,
       Index INTEGER not null,
       primary key (UserId, Index)
    )

I expected the create table query would only contain two fields, UserId and RoleId. There is a way to tell FluentNHibernate how to name the fields. I just can’t put my finger on it right now. Even if the key fields are properly named why is the field Index added to the table?

The Solution?

Generating the database schema for the in memory database and the SQLite database has proven to be a problem at this stage of development. There is, most likely, a means to force the desired schema. I have decided to go ahead and use a SQL Express database with the tables prebuilt, but empty of any data. I no longer have any schema issues with the UserToRole table, but my tests are still failing.

At this point I progress has been non-existent and I need to see some positive results. I decided I need to consider other ORM options instead of NHibernate.

LightSpeed

I have used LightSpeed in a previous project and it worked extremely well. The cost is $349 US for the developer edition, no source code. Consider a fair rate for an hour of your time. Then multiply that by the number of hours already spent investigating and fighting with NHibernate to arrive at a dollar amount. That $349 is looking like a bargain!

So I downloaded and installed version 3 of the express edition (free and has an eight table/model class limit). When I attempted to add my database tables to the designer, Visual Studio blows up. Go directly to jail, do not pass Go, do not collect $200. That was with Visual Studio 2008. Visual Studio 2010 blows up too.

Next, I search the LightSpeed forums to see if anyone else has the same issue. Sure enough I find what appears to be the problem. The LightSpeed designer barfs all over the place if you have a Data Connection (in the Server Explorer window) to a non-existent database. Really? The LinqToSQL and EntityFramework designers, as I am sure many others, have no problems with this. So, I double check the data connections. All of the them test ok. I try dragging tables to the designer once more and get the same results. I repeat testing all the data connections in the Server Explorer for a third time and dragging tables to the designer for a third time. Same results.

This is too much time spent spinning my wheels. Next!

LLBLGen Pro

Its $550 a shot! Not sure I want to put together a template that depends on a library that costs that much. $349 was pushing it with LightSpeed. Besides, you’d think for $550 per license the author could afford a few more vowels. What is LLBLGen anyway? My criticism is definitely unwarranted. I’m sure it’s a fine product.

My experience typically says the more a product costs, the more difficult it is to learn and use. Given the reasons above along with not wanting to spend the same amount of time learning LLBLGen Pro as I did on NHibernate, I decided to pass. Next!

Entity Framework

I built a previous project with version 1. I think I’d rather have a root canal done. No thanks. Next!

Sub Sonic

I love use Sub Sonic a lot. I have customized T4 templates that generate all kinds of code based on Sub Sonic. I love Sub Sonic. There’s just one little problem with Sub Sonic version 3. It doesn’t have strong for many-to-many relationships. The exact problem I am trying to solve is a many-to-many problem. Next!

LinqToSQL

It’s small, lightweight, and development will be fast. So I decide to create a provider data layer using LinqToSQL.

Summary

Having reached the end of the rope with NHibernate, I considered several other ORMs before deciding on LinqToSQL. I found it quite easy to modify the code I had written for the NHibernate provider data layer since much of it used Linq. When I compiled the code and tested the spec, all specifications passed! Woohoo! Success!

Well sort of. It was positive progress, but I wanted a data repository based on NHibernate. The next installment in the series will show just that! A NHibernate provider data repository!

Previous Articles in this Series

2 Comments (+add yours?)

chaitanya
Oct 12, 2010 @ 14:23:52

In this post –> Parts 1.3.4.6 have the wrong URL’s. Recheck the URL’s.

Thank you.
Eric
Oct 14, 2010 @ 23:54:58

Thanks chaintanya. All of the references to previous articles have been updated to the correct links.

Cancel

erictopia.com