Fluent NHibernate: Simple Many-to-Many Vs. Normal Many-to-Many Mapping Timing

Summary

In an earlier post I showed how to map a many-to-many relationship without using the HasManyToMany mapper.  So I finally put together a simple test project (technically it’s two projects in one solution) to test the two methods side-by-side.  To my surprise, it is very difficult to tell if one is faster than the other.  One problem with speed testing involves caching.  It’s difficult to know for sure how much of the results are cached in memory.  I even used the GC.Collect() to clear any unused memory before performing each test.


Results



As you can see the results are not seriously conclusive.  I reversed the ordering of the normal and easy methods and this is what I got:



Yeah, not conclusive.  Bummer.

Technically, I should insert a thousand classes and then link them to each student.  Then delete all students using both methods and see how it comes out.  I suspect I’ll run into the same timing issues.  The normal mapping method using ILists is quick because I have it set to LazyLoad.  I guess that says something about using Fluent NHibernate for your ORM (the firstnames.txt file contains 1,219 lines that are inserted as student names for the insert test).

If anybody wants to take a shot at this, feel free to download the sample code and give it a try.



Download the sample code here:

FluentNHibernateSimpleVsNormal.zip

 

Fluent NHibernate Composite Key Many-to-Many

Summary

I’m on a roll today, so I decided to create a new connection table between my Class table and Student table in my previous blog posts.  This table is identical to the StudentClass table, except I removed the primary key and used only two fields and made them into a composite key.  If you’re looking for an example of the composite key mapping, this is not the article to show it.  Even though I made the two connecting keys into one primary key, there is no composite mapping because the table itself is not mapped.


The ERD

The new table is called Student2Class and here is the final diagram:


Again, I’m using two databases because that was how my original tables were setup in the multiple database mapping article.  You can put all the tables in one database and perform the same CRUD operations that I have here.


Code Changes

I altered the name of the table in the mappings inside both the ClassMap and StudentMap objects.  That was my only changes from the previous project.  All the CRUD operations work as before.


You can download the sample code here:

FluentNHibernateManyToManyComposite.zip

 

Fluent NHibernate: Removing a Relationship Connection From a Many-to-Many

In my last blog post I forgot to show how to disconnect a student from a class but leave both the student and the class records intact.  So I quickly ninja’d something together to demonstrate how simple it is to perform this vital function.  Here’s my code:

using (ISession db = MSSQLSessionFactory.OpenSession())
{
    // delete connection between student and class
    var student = (from s in db.Query<Student>()
                    where s.Name == “New Student
                    select s).FirstOrDefault();

    using (db.BeginTransaction())
    {
        foreach (var temp in student.Class)
        {
            if (temp.Name.Trim() == “CSC 300“)
            {
                student.Class.Remove(temp);
                break;
            }
        }
                    
        db.Transaction.Commit();
    }

}

Firsrt, I queried for the student I wished to disconnect.  Then I searched through the list of Classes that the student was assigned to, searching for the one class to disconnect.  Once I found the Class (be aware of the Trim() since the Name is a character field) I removed it from the list and committed the transaction.  This was all that was necessary to make the StudentClass record delete connecting “New Student” and “CSC 300”.

As Hannibal Smith would say “I love it when a plan comes together!”



 

Fluent NHibernate HasManyToMany the Normal Way

Summary

As I mentioned in my last blog post, there is a simple way to treat a many-to-many data relationship involving basic mapping and handling the connection using the LINQ query itself.  Now I’m going to show how the many-to-many mapping occurs involving more mapping commands and one less table (the connecting table callded StudentClass).


Code Changes

First, I commented out the StudentClass object.  We’re not going to need this table.  Instead the table will be referenced in the HasManyToMany mapping method inside both the Student object and the Class object.  Next I modified my Student object as so:

public class Student
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual IList<Class> Class { get; set; }

    public Student()
    {
        Class = new List<Class>();
    }
}

public class StudentMap : ClassMap<Student>
{
    public StudentMap()
    {
        Id(u => u.Id).GeneratedBy.Identity();
        Map(u => u.Name).Nullable();
        HasManyToMany(u => u.Class)
            .Table(“studentdata..studentclass“)
            .ParentKeyColumn(“StudentId“)
            .ChildKeyColumn(“ClassId“);

        Table(“studentdata..Student“);
    }
}

Don’t forget to add a using for System.Collections.Generic (not shown in this code snippet), otherwise you’ll get a syntax error when trying to use an IList or List command.  You’ll need a constructor for the IList for the insert CRUD operation.  If you don’t include the constructor to create a new list, then you’ll have no problem querying existing data, but your Class.Add(myclass) will not work.

The mapping for the Student class includes a new HasManyToMany mapping.  The tricky part was getting the right fields for ParentKeyColumn and ChildKeyColumn.  These refer to the two columns in the StudentClass table (which must also be specified using the “Table” mapping).  The ParentKeycolumn always points to the current table containing the mapping.  The Child points to the table that will contain the records that go into the IList.

OK, so I also modified the Class object to do the exact same thing in reverse:

public class Class
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual int TeacherId { get; set; }
    public virtual IList<Student> Student { get; set; }

    public Class()
    {
        Student= new List<Student>();
    }
}

public class ClassMap : ClassMap<Class>
{
    public ClassMap()
    {
        Id(u => u.Id).GeneratedBy.Identity();
        Map(u => u.Name).Nullable();
        HasManyToMany(u => u.Student)
            .Table(“studentdata..studentclass“)
            .ParentKeyColumn(“ClassId“)
            .ChildKeyColumn(“StudentId“);

        Map(u => u.TeacherId).Not.Nullable();

        Table(“facultydata..Class“);
    }

}

Again, I added an IList, this time creating a list of Student class objects.  Then I did the exact same mapping with the ParentKeyColumn and ChildKeyColumn in reverse.

The LINQ that I used for testing the new mapping looks like this:


using (ISession db = MSSQLSessionFactory.OpenSession())
{
    var query = (from s in db.Query<Student>()
                 select
                 ).OrderBy(x => x.Name).ToList();

    Console.WriteLine(“List of classes each student has“);
    foreach (Student studentitem in query)
    {
        foreach (var classitem in studentitem.Class)
        {
            Console.WriteLine(studentitem.Name.Trim() + ” ” + classitem.Name.Trim());
        }
    }

    Console.WriteLine();

    var query2 = (from c in db.Query<Class>()
                  select c).OrderBy(x => x.Name).ToList();

    Console.WriteLine(“List of students in each class”);
    foreach (Class classitem in query2)
    {
        foreach (var studentitem in classitem.Student)
        {
            Console.WriteLine(classitem.Name.Trim() + ” ” + studentitem.Name.Trim());
        }
    }

    Console.ReadKey();
}


I did two queries, one listing the classes for each student to make sure the output matched my previous example exactly.  Then I listed the students in each class to see if the reverse made sense.  


CRUD Operations

This took some trial and error for me to get the insert to work right.  Initially, I thought that I needed an “inverse()” mapping, but later realized that it was unnecessary.  Here’s an example of inserting a student, a class and then connecting the two together:

// insert new student
Student student = new Student()
{
    Name = “New Student
};

db.Save(student);

// insert a new class to the student
Class tempclass = new Class()
{
    Name = “CSC 300“,
    TeacherId = 1
};

db.Save(tempclass);

student.Class.Add(tempclass);
db.Save(student);


db.Flush();


This entire operation will save a new student record, then a class record.  The class is then added to the student list and saved again, but a Flush() is necessary, otherwise the StudentClass connecting record will not be created.  Also, if you put a break-point at the db.Save(student) line of code and look at the student object, you’ll see that the id is set to zero.  After the save, NHibernate back-fills the unique id from the database into the id field.

Next I performed a delete to see if it cascaded correctly:

// Test delete student and connector, but not class
using (db.BeginTransaction())
{
    db.Delete(student);
    db.Transaction.Commit();

}

As it turns out, the operation above will delete the student and any StudentClass records for that student, but it will correctly leave the class intact.  One thing to note is that the Class object is not reloaded with the new student record.  So if you try and list all students in each class as before, you’ll get the same list of classes and students as before the insert.  In other words, the new class and student are not loaded back automatically.  To avoid this problem, close the context after performing an insert (or more appropriately, use one context for the insert operation alone), then open a new context to re-read the data from the database.

Now, let’s do the Class delete:

using (ISession db = MSSQLSessionFactory.OpenSession())
{
    var tempclass = (from c in db.Query<Class>() 
                     where c.Name == “CSC 300” 
                     select c).FirstOrDefault();

    // Test delete class and connector, but not student
    using (db.BeginTransaction())
    {
        db.Delete(tempclass);
        db.Transaction.Commit();
    }
}
This delete will remove the Class and the connecting StudentClass record but leave the Student intact.  I’m not going to go through the updates in this blog post, since they are normally trivial.

One more thing I’d like to mention is that there is a LazyLoad() mapping that can be added to both HasManyToMany mappings (both the student and class objects).  This can improve performance in situations where there are a lot of students and a/or a lot of classes.


Conclusion

I still have not performed a speed comparison between this method and the simple many-to-many method I used in my last blog post.  I also have not done the CRUD operations for the simple method in my last blog post.  I’ll save that for another post (I know, you want it NOWWW!!!).  I apologize for leaving you in suspense.  The HasManyToMany mapping turned out to be easier than I had expected.  To be honest, I struggled with performing a HasManyToMany mapping on another table setup that I’ll also blog about in the future.  The instance I’m talking about involves a composite key situation with the connector table.  You may have noticed that my StudentClass table contains a primary key that is separate from the two foreign connector keys.  This made my simple method easy because I can define the key as Id field and treat the other two fields as separate map fields.  When I attempted to use a two field StudentClass table with both foreign keys setup as a composite primary key, I discovered that there is a very complex mapping operation involving an override to the equal that is necessary because NHibernate doesn’t handle this situation directly.  I’m planning to alter the StudentClass table and make it work so I can demo this situation for anybody that is stuck on composite keys (I saw a lot of stack overflow issues involving this problem, with varying methods of solving it).

You can download the code for this project here:






 

Fluent NHibernate Many-to-Many Without Using HasManyMany

Summary

In my last Fluent NHibernate post I showed how to do a One-to-Many mapping using the HasMany mapping method and ILists.  Getting the mappings to work correctly was a bit painful, and the outcome was questionable.  So I thought I’d try my hand at mapping a many-to-many situation without using the HasManyToMany mapping method or ILists.


The ERD

First, I modified my Teacher, Student, Class databases I used in previous examples.  This is what I setup:

I’m sticking with two databases for now.  This will show as a table mapping in each mapping class.  Also, I left the teacherid foreign key inside the class table.  You can remove this field if you want to setup your own example.


The Data

I used the following data for this sample:

The top table is the student table, the middle table is the class table and the bottom table is the connecting studentclass table.


The C# Code

On to the code.  I’m trying to demonstrate a very simple example.  This may not suit your purposes, but it does demonstrate a very easy way to perform a many-to-many situation.  In this example, there are many students in a class and students can have many classes.  Here are my three tables:

Class table (see Class.cs file):

public class Class
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual int TeacherId { get; set; }
}

public class ClassMap : ClassMap<Class>
{
    public ClassMap()
    {
        Id(u => u.Id).GeneratedBy.Identity();
        Map(u => u.Name).Nullable();
        Map(u => u.TeacherId).Not.Nullable();

        Table(“facultydata..Class“);
    }
}

Student table (see Student.cs file):

public class Student
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
}

public class StudentMap : ClassMap<Student>
{
    public StudentMap()
    {
        Id(u => u.Id).GeneratedBy.Identity();
        Map(u => u.Name).Nullable();

        Table(“studentdata..Student“);
    }
}

And finally, the StudentClass table (see StudentClass.cs file):

public class StudentClass
{
    public virtual int StudentClassId { get; set; }
    public virtual int StudentId { get; set; }
    public virtual int ClassId { get; set; }
}

public class StudentClassMap : ClassMap<StudentClass>
{
    public StudentClassMap()
    {
        Id(u => u.StudentClassId).GeneratedBy.Identity();
        Map(u => u.StudentId).Not.Nullable();
        Map(u => u.ClassId).Not.Nullable();

        Table(“studentdata..StudentClass“);
    }
}

All the table objects are very simple and straightforward.  I did not change anything in the SessionFactory object from previous examples.  You can download the code at the end to get a complete running example.


The Query

I formed a query to spit out the student names next to the class that the student is assigned to:

using (ISession db = MSSQLSessionFactory.OpenSession())
{

    var query = (from c in db.Query<Class>()
                 join sc in db.Query<StudentClass>() 
                   on c.Id equals sc.ClassId
                 join s in db.Query<Student>() 
                   on sc.StudentId equals s.Id
                 select new { s, c }
                 ).OrderBy(x => x.s.Name).ToList();

    foreach (var item in query)
    {
        Console.WriteLine(item.s.Name.Trim() + ” ” + item.c.Name.Trim());
    }

    Console.ReadKey();
}

As you can see, the query is nothing more than a join between the three tables and then I selected two of the tables for output.  I then ordered by the student name. 


The Output

Here is the expected output:


Conclusion

Sometimes building a database and created the ORM mappings can be simple.  The database itself can be setup with the correct referential integrity.  Also, I did not perform any CRUD operations on this example (yet).  If you decide to do an insert, remember the Class record and the Student record must exist before you can create a connecting record in the StudentClass table (yeah, I know, that’s database 101 knowledge).

I’m still planning to show the code using the HasManyToMany mapping of this setup.  The mappings will be more complex and I would also like to do CRUD operations for both to compare the speed and difficulty in setting up the ORM code (and queries) for each.

For now, you can download the code here:


 

Just Some Stuff About Me…

I thought I’d change it up a bit and just do a quick blog post about who I am and where I came from.  Some of you are probably wondering why I used “IT” in my blog title since I don’t blog about hardware stuff.  Technically, I was hoping this blog would cover my knowledge in both hardware and software, but it has turned into a blog about software only.  Sometimes you just have to choose a topic and stay focused.  I started my career as a hardware guy back in the day when computers were repaired at the chip level (at least I didn’t start when computers were built with tubes!).  When I was a teenager I got into electronics as a hobby and I really got hooked onto digital circuits.  I still have my TTL logic book and my original 8088 manual that I purchased from Byte magazine for $5.  There was a deal going on back in 1980 when Intel had a new chip called the 8088 and they were offering a free chip if you bought the tech manual for $5.  So I did.  I still have the chip (never used it) and the manual.  

As you can see, the pages of my logic book are getting yellow:


And the iAPX 88 book is well worn:

Anyway, I joined the Navy at age 19 as an electronics technician.  I spent almost a year at Great Lakes training center learning (or re-learning) electronics from the ground up.  I was very good at electronics and I graduated top of my class in A school, so I got first pick of orders.  Fortunately, there was a ship in Pearl Harbor that needed a satellite communications technician with automated communication skills.  So they sent me to San Diego for C school to learn automated systems and satellite communications.  The automated system consisted of a class to repair the UYK-20 mini computer.  Or as we nick-named it the Yuck 20.  This minicomputer was built specifically to be small enough to fit through a round hatch, so the ship yard didn’t need to cut a hole in the ship’s hull to install these machines.  Which was a good thing, because even my ship had over a dozen of these machines on board and this was in the early 80’s.  Here’s what a UYK-20 computer looks like from the outside:
It’s a bulky machine but it was bullet-proof and built like a tank.  Rarely did I ever repair one of these machines after I was deployed, and I was stationed on the USS Worden for 3 years.  

Another minicomputer that was introduced while I was stationed on the Worden was the SNAP-II computer.  This minicomputer was used to keep track of inventory on-board the ship and run all the nasty paperwork that was needed in order to procure a repair part.  This machine used 8″ floppy drives and 9-track mag tapes.  I preferred the mag tapes.

After I received my honorable discharge from the Navy, I decided to go back to college.  Technically, I wanted to go to college before I went into the Navy, but circumstances prevented me from being able to afford such a luxury.  Fresh out of the Navy and able to get a good paying job, I was able to afford college.  During this time I worked at a small phone company that maintained large business systems.  I installed and maintained a lot of KSU’s, or Key systems as well as some large switches made by Mitel (Also known as PBX’s).  Our company installed a lot of SX-200 and SX-2000 switches.  These machines switched circuits by digitizing each channel, then multiplexing them into a serial stream.  A special chip in the machine would swap two channels inside the stream in real-time which is what performed the switching.
By the time I was involved in telephone systems, the paradigm had already changed to a board-level replacement only.  Rarely did I have to break out a soldering iron.

After I graduated from the University of Michigan, I did some network related work.  Primarily thin-net and 10Base-T (which was new).  Technically, I was already involved in the installation of distribution systems when I worked for the telephone company, but I became more involved after the PC became powerful enough to be used as a server and Novell was the primary server OS.  
I went to work for Pirelli for one year and I learned about the line of Allen Bradley PLC products.  This was a fun project.  I designed and wired up a system consisting of three PLC’s (industrial computers) and connected them to a AB data highway to a PC in the computer room which was used to receive the raw data from the factory and store it in a database.  I also had to learn PLC ladder logic, which is the language that these devices used in order to read relay contacts and transmit data over the data highway.  Here’s what an 8-slot PLC looks like:


After I left Pirelli, I went to work for Building Technology Associates where I was tasked with merging two database systems together and fixing all the non-relational issues that were created by years of throwing together software without any knowledge of how to do it right.  That was a fun task.  At BTA I was also in charge of the hardware.  For this company I was able to hire a network administrator to off-load the work that needed to be done.  Since we maintained our own computer center, we had three partial racks of servers to maintain.  This is where I parted with the hardware world.  

I worked for BTA for 16 years and I slowly dis-involved myself with servers and hardware, other than to possibly configure for my next project.  Now I work for Dealer-On and we have a clear separation of hardware and software personnel.  This gives me the time to fully focus on the company software and leave the hardware to those who only work on the software.

Do I miss working on hardware.  A little.  I prefer the good-ole-days when I could troubleshoot to the chip level using a schematic diagram.  Those days are all but gone.  But I could still drag out my tools and resurrect my hobby!


 

Fluent NHibernate HasMany Mapping

Summary


This post will demonstrate how a one-to-many mapping can be done in Fluent NHibernate.  As in previous articles, I’m going to use my facultydata database with two tables to demonstrate the HasMany and References mappings.


The Database

First, we’ll need to make sure that both tables have primary keys.  Then add a foreign key constraint from the teacher table primary key to the class teacherid foreign key field.  You can use this MS SQL script to do it:

ALTER TABLE [dbo].[class]  WITH CHECK ADD  CONSTRAINT [FK_Class_Teacher] FOREIGN KEY([teacherid])
REFERENCES [dbo].[teacher] ([id])
ALTER TABLE [dbo].[class] CHECK CONSTRAINT [FK_Class_Teacher]
Now create or modify your Class table mapping in C# to look like this:

public class Class
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual Teacher Teacher { get; set; }
}

public class ClassMap : ClassMap<Class>
{
    public ClassMap()
    {
        Id(u => u.Id).GeneratedBy.Identity();
        Map(u => u.Name).Nullable();
        References(x => x.Teacher)
            .Column(“TeacherId“)
            .Not.Nullable(); 
    }
}
The first thing you should notice is the missing foreign key defined in the class itself.  This has been replaced by a pointer to the Teacher class.  This will be used as a track back in the ORM itself.  The mapping has been altered to include a “References” section.  
You’ll also need to modify your Teacher table class and mappings like this:
public class Teacher
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual IList<Class> Classes { get; set; }

    public Teacher()
    {
        Classes = new List<Class>();
    }
}

public class TeacherMap : ClassMap<Teacher>
{
    public TeacherMap()
    {
        Id(u => u.Id).GeneratedBy.Identity();
        Map(u => u.Name).Nullable();
        HasMany(u => u.Classes)
            .KeyColumn(“TeacherId“)
            .Inverse()
            .Cascade.All();
    }
}

The Teacher object has been modified to add an IList of Class objects.  The point of this exercise is to connect these two objects (Teacher and Class) together so that the classes that belong to each Teacher is automatically loaded when the teacher records are queried.  I also added a constructor to the Teacher object to make sure that the IList is not null if the user creates a new Teacher record.

The real magic happens in the mapping.  The HasMany is tricky to get to work properly.  First is the KeyColumn setting, which must point to the foreign key in the Class table.  Second the Inverse() must be used because the primary keys of the child records (Class) are not null.  Last, the .Cascade.All() needs to be used for the cascade delete operation.  I did not put a cascade delete in MS SQL server.  I imagine that it would just be easier to put one in and the database can take care of itself when deleting a teacher record.  For this demonstration, I wanted to see if the ORM would do cascades on its own.

One other thing of note:  I setup the primary keys to be identity keys in MS SQL server (meaning MS SQL will create a unique primary key on inserts).  To take advantage of this feature, I also setup the primary keys in the ORM to have the .GeneratedBy.Identity().  One hazard of this is that the primary key is not filled in until the record is created (because the key doesn’t exist until MS SQL gives it one).  Fortunately, NHibernate will re-read the key back into the object after save.


The CRUD Operations

Here are the CRUD operations that I used to test this mapping:

using (ISession session = MSSQLSessionFactory.OpenSession())
{
    // Test query of data by selecting only the teacher records
    var query = (from t in session.Query<Teacher>()
                    select t).ToList();

    foreach (var teacheritem in query)
    {
        foreach (var classitem in teacheritem.Classes)
        {
            Console.WriteLine(“Class:” + classitem.Name.Trim() + ” Teacher:” + teacheritem.Name.Trim());
        }
    }

    // Test inserts
    Teacher newteacher = new Teacher() { Name = “Delete ME” };
    session.Save(newteacher);

    Class newclass = new Class() { Name = “TECH01“, Teacher = newteacher };
    newteacher.Classes.Add(newclass);
    session.Save(newclass);

    newclass = new Class() { Name = “TECH02“, Teacher = newteacher };
    newteacher.Classes.Add(newclass);
    session.Save(newclass);

    // Test update parent and children
    using (session.BeginTransaction())
    {
        newteacher.Classes[0].Name = “NONTECH01“;
        newteacher.Classes[1].Name = “NONTECH02“;
        newteacher.Name = “Delete 2“;
        session.Update(newteacher);
        session.Transaction.Commit();
    }

    // Test cascade deletes
    using (session.BeginTransaction())
    {
        session.Delete(newteacher);
        session.Transaction.Commit();
    }

    Console.ReadKey();
}
The query itself is trivial.  The inserts I had some trouble with.  There are a lot of articles about how to insert parent child records.  My simple fix is to save after each object is created (guaranteeing that the primary key is read back into the object).  If you step through the code you’ll see the primary key of the newteacher and the primary keys of each newclass being set right after the save method is called.
After the inserts, I performed an update.  I wanted to test the ability of the ORM to save all changes to the parent and child records with one save.  So I setup a transaction, changed the name of the teacher record and all it’s children, then committed it.  Then I checked the database, and it all updated as expected.
Last, I performed a cascade delete by deleting only the teacher record.  If you attempt to manually delete the teacher record from MS SQL server, you’ll get a foreign key error.  I checked the database after this operation and all records were deleted as expected.
Download the whole test project here: FluentNHibernateMappings.zip




 

Fluent NHibernate Unit Testing Using SQLite

Summary

So I’ve been producing a lot of blog posts on NHibernate lately.  I’m attempting to evaluate NHiberate against other ORMs like Entity Framework.  This blog post will focus no basic unit testing of NHibernate.  I’m using Fluent with my NHibernate, but much of this knowledge still applies to straight NHibernate.


SQLite


Before I started putting together my first unit test, I consulted my vast reference library of information, also known as Google.  As I did my initial research into how everyone else was unit testing NHibernate, I stumbled across a lot of articles talking about a tiny database driver known as SQLite.  There’s a .NET version that you can use with Visual Studio by searching for it in the NuGet package manager.  The first thing I had to work out is how to connect to SQLite and return an ISession object identical to what I was getting with MSSQL.  This is what I came up with:

using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using NHibernate;
using NHibernate.Tool.hbm2ddl;

namespace UnitTestNHibernate
{
    public static class SQLLiteSessionFactory
    {
        private static ISessionFactory _sessionFactory;
        private static ISessionFactory SessionFactory
        {
            get
            {
                if (_sessionFactory == null)
                {
                    _sessionFactory = Fluently.Configure()
                    .Database(SQLiteConfiguration
                            .Standard
                            .InMemory()
                            .UsingFile(“facultydata.db“)
                    )
                    .Mappings(m => m.FluentMappings.AddFromAssemblyOf<NHibernateUnitTestSQLLite.Class>())
                    .Mappings(m => m.FluentMappings.AddFromAssemblyOf<NHibernateUnitTestSQLLite.Teacher>())
                    .ExposeConfiguration(cfg => new SchemaExport(cfg).Create(true, true))
                    .BuildSessionFactory();
                }
                return _sessionFactory;
            }
        }
        public static ISession OpenSession()
        {
            return SessionFactory.OpenSession();
        }
    }
}

I’m using the facultydata database from my previous examples (I’m somewhat lazy and I don’t want to create new databases and tables to demonstrate these things if I don’t have to).  Anyway, I will not be testing multiple databases in this blog post (primarily because I haven’t been able to make it work or fake it in SQLite).

The .InMemory() setting causes the database to be created in memory only.  No file.  If you don’t include this flag, then a database named “facultydata.db” will be created in the bin directory of your project.

The .ExposeConfiguration setting will cause all the tables used by this application to be created in the SQLite database when a session is opened.  Therefore, when you create your unit tests, you only have to insert some data and call your method under test.


The Unit Tests

I wrote one unit test just to access tables in Sqlite and see how it will perform.  Here is the test I wrote:

[TestMethod]
public void test_sqlite_database_using_fluent_nhibernate_directly()
{
    using (ISession session = SQLLiteSessionFactory.OpenSession())
    {
        session.Save(new Teacher() { Name = “Adam” });
        session.Save(new Teacher() { Name = “Joe” });

        session.Save(new Class() { Name = “MATH 101“, TeacherId = 1 });


        var query = (from t in session.Query<Teacher>()
                        join c in session.Query<Class>() on t.Id equals c.TeacherId
                        select t).ToList();

        Assert.AreEqual(1, query.Count);
    }
}

As you can see I just tossed two records in the Teacher table and one record in the Class table.  Then I joined them together.  Of course, one of the classes does not have a teach assigned (I don’t have any constraints on my SQLite database in this example), therefore one of the teacher records will not join and only one record will be produced.  But that’s OK, this is just a proof of concept test anyway.

Next, I need to be able to test a method in the main project by breaking dependency of the object and using the test session.  So I wrote an object to read all classes and I created two constructors to handle normal access to MSSQL server and test access using SQLite.  Here’s what the object looks like:

using System;
using System.Collections.Generic;
using System.Linq;
using NHibernate;
using NHibernate.Linq;

namespace NHibernateUnitTestSQLLite
{
    public class ReadAllClasses
    {
        ISession _session;

        public ReadAllClasses()
        {
            _session = MSSQLSessionFactory.OpenSession();
        }

        public ReadAllClasses(ISession _testSession)
        {
            _session = _testSession;
        }

        protected void Dispose(bool disposing)
        {
            if (_session is IDisposable)
            {
                ((IDisposable)_session).Dispose();
            }
        }

        public List<Class> Execute()
        {
            // quick and dirty LINQ query
            var query = (from c in _session.Query<Class>() 
                         select c).ToList();

            return query;
        }
    }
}

As you can see, the first constructor uses the MSSQLSessionFactory object to get an ISession and the second constructor allows a unit test to pass an ISession that can be created before calling this method.  This object was created in the main project (not the unit test project).

This is how I wrote the unit test to call this method:

[TestMethod]
public void test_method_in_fluent_nhibernate_project()
{
    ISession session = SQLLiteSessionFactory.OpenSession();

    session.Save(new Class() { Name = “MATH 101“, TeacherId = 1 });

    session.Save(new Class() { Name = “CSC 100“, TeacherId = 1 });

    ReadAllClasses readAllClasses = new ReadAllClasses(session);

    List<Class> result = readAllClasses.Execute();

    Assert.AreEqual(2, result.Count);

}

In my unit test, I created a session and passed it as a parameter to ReadAllClasses().  This sets the class up to use the SQLite database instead of the default MSSQL database.  

If you want to create this project from the ground up, you’ll need to add a NuGet package for NHibernate, then FluentNHibernate, then SQLite.  You can delete the Entity Framework dlls that are installed by NuGet. Even so, the project will still be 112 Megabytes in size, with very little code.  My suggestion to anybody who wants to deploy an application into a production system, is to move all the needed dlls into a third party directory, then re-add the references and remove all the NuGet “fluff”.

You can download my sample code here:

NHibernateUnitTestSQLLite.zip



 

Fluent NHibernate Stored Procedure

Summary

This week I’m going to give a very simple example of how to execute a stored procedure.  The stored procedure will only do a select query, but I’ll pass a variable to demonstrate how it’s done and I’ll leave it up to the reader to alter the code to execute other types of stored procedures.  I’m going to build on my last post, so I’ll use all the same code except for the main method of the console application.  If you haven’t read my previous post on NHibernate go here: Fluent NHibernate using Multiple Databases and setup your database to match the example.


The Stored Procedure

Copy this SQL command into your query window of MS SQL server, and execute it.  It should generate a stored procedure in your database.  Make sure this stored procedure is in the facultydata database.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[pSelectClassPerTeacher]
@TeacherId int
AS
BEGIN
SET NOCOUNT ON;

SELECT * FROM class WHERE teacherid = @TeacherId 
END

The Code

The only change I made to the Main method of program.cs is this:

using (ISession session = NHibernateHelper.OpenSession())
{
    var query = session.CreateSQLQuery(“exec pSelectClassPerTeacher :TeacherId“)
        .AddEntity(typeof(Class))
        .SetParameter(“TeacherId“, 2)
        .List<Class>();

    foreach (var item in query)
    {
        Console.WriteLine(“ClassName=” + item.Name.Trim() + ” TeacherId=” + item.TeacherId.ToString());
    }

    Console.ReadKey();

}

That’s it.  Now run it.  It should produce a list of class names with teacher number 2.  One of the reasons why my example was so simple is that I selected all records from the class table.  This produced the same output as the “Class” object that defines that table.  You can do any fancy query that produces other types of output, followed up by defining a special return type class that has all the expected fields.  Also, each parameter must be accounted for inside the string and it must have a matching “.SetParameter()” method call.

I know, this is a short post, but this turns out to be an easy subject.