NHibernate object-relational mapping discipline

What will happen to your language-embedded query(e.g. Linq) techniques if you are not yet weaned off on mapping table fields to objects by its physical implementation, i.e. you are using public virtual int ProductId { get; set; }
instead of using public virtual Product Product { get; set; }


That is, instead of this logical mapping, see line #18:

public class SalesHeader
{
 public virtual int SalesHeaderId { get; set; }
 public virtual string CustomerName { get; set; }

 public virtual string OrNum { get; set; }
 public virtual DateTime OrDate { get; set; }

 public virtual IList<SalesDetail> Sales { get; set; }      
}


public class SalesDetail 
{
 public virtual SalesHeader SalesHeader { get; set; }

 public virtual int SalesDetailId { get; set; }
 public virtual Product Product { get; set; }
 public virtual int Qty { get; set; }
 public virtual decimal UnitPrice { get; set; }
 public virtual decimal Amount { get; set; }            
}

You just do physical mapping, note line# 6:

public class SalesDetail 
{
 public virtual SalesHeader SalesHeader { get; set; }

 public virtual int SalesDetailId { get; set; }
 public virtual int ProductId { get; set; }
 public virtual int Qty { get; set; }
 public virtual decimal UnitPrice { get; set; }
 public virtual decimal Amount { get; set; }            
}

If you want to fashion your query to something like this (find all SalesHeader which has a Product that starts with M):

select *
from SalesHeader h
where exists(
 select * 
 from SalesDetail d 
 join Product p on p.ProductId = d.ProductId 
 where d.SalesHeaderId = h.SalesHeaderId
  and p.Product.Name like 'M%'
 )

This is how your Linq will look like if you map your database to objects physically:

var x =
 from h in s.Query<SalesHeader>()
 where h.Sales.Any(d =>
  s.Query<Product>().Where(p => p.ProductId == d.ProductId && p.ProductName.StartsWith("M")).Any())
 select h;

that looks convoluted, and the resulting query(columns removed for brevity) is even more:

exec sp_executesql 
N'select * 
from [SalesHeader] salesheade0_ 
where exists (
 select * from [SalesDetail] sales1_ 
 where salesheade0_.SalesHeaderId=sales1_.SalesHeaderId and 
  (exists (select *  from [Product] product2_ 
    where product2_.ProductId=sales1_.ProductId and (product2_.ProductName like (@p0+''%'')))))',N'@p0 nvarchar(4000)',@p0=N'M'

The resulting query, though achieves the same output with our desired query, is a far cry from simple query, it results to a bit complex query, it doesn't link SalesDetail to Product, it uses another EXISTS.


Another attempt, one-to-one with our desired query:

var x = from h in s.Query<SalesHeader>()
 where
 (
  (from d in s.Query<SalesDetail>()
  join p in s.Query<Product>() on d.ProductId equals p.ProductId
  where d.SalesHeader.SalesHeaderId == h.SalesHeaderId
   && p.ProductName.StartsWith("M")
  select d).Any()
 )
 select h;

And that generates this:

exec sp_executesql 
N'select *
from [SalesHeader] salesheade0_ 
where exists (
 select *
 from [SalesDetail] salesdetai1_, [Product] product2_ 
 where product2_.ProductId=salesdetai1_.ProductId and salesdetai1_.SalesHeaderId=salesheade0_.SalesHeaderId 
  and (product2_.ProductName like (@p0+''%'')))',N'@p0 nvarchar(4000)',@p0=N'M'  

Now it look the same with our desired query, though Linq-wise, it can be argued that it doesn't achieve so much in terms of readability and productivity.



To make Linq matters simple, use proper modelling with your ORM

public class SalesDetail 
{
 public virtual SalesHeader SalesHeader { get; set; }

 public virtual int SalesDetailId { get; set; }
 public virtual Product Product { get; set; }
 public virtual int Qty { get; set; }
 public virtual decimal UnitPrice { get; set; }
 public virtual decimal Amount { get; set; }            
}

The Linq will be now this simple, and it achieves the same desired query. The difference is minor, it use table comma table instead of join.

var x = from h in s.Query<SalesHeader>()
  where h.Sales.Any(d => d.Product.ProductName.StartsWith("M"))
  select h;

The generated query:

exec sp_executesql 
N'select *
from [SalesHeader] salesheade0_ 
where exists (
 select sales1_.SalesDetailId 
 from [SalesDetail] sales1_, [Product] product2_ 
 where salesheade0_.SalesHeaderId=sales1_.SalesHeaderId 
  and sales1_.ProductId=product2_.ProductId and (product2_.ProductName like (@p0+''%'')))',N'@p0 nvarchar(4000)',@p0=N'M'
  

Now the Linq is more maintainable and intuitive, and follows our desired optimized query :-)

Form_Load exception troubleshooting

Don't put code in Form_Load event if possible, silent errors will creep in when you are running inside Visual Studio. Error will manifest only when the EXE is run directly

private void Form1_Load(object sender, EventArgs e)
{
    int n = 0;
    MessageBox.Show((7 / n).ToString()); // won't throw an Exception when run inside VS
}

private void Form1_Shown(object sender, EventArgs e)
{
    int n = 0;
    MessageBox.Show((7 / n).ToString()); // will throw an exception, regardless of where it is run
}

Using checkbox list on ASP.NET MVC with NHibernate

The core logic of checkbox list:

@foreach (var g in Model.GenreSelections)
{                                
    <input type="checkbox" name="SelectedGenres" value="@g.GenreId" @(Model.SelectedGenres.Contains(g.GenreId) ? "checked" : "") /> @g.GenreName
}

Add the models

Movie model

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;


namespace AspNetMvcCheckboxList.Models
{
    public class Movie
    {
        public virtual int MovieId { get; set; }
        
        [   Required, Display(Name="Title")
        ]   public virtual string MovieName { get; set; }
        
        [   Required, Display(Name="Description")
        ]   public virtual string MovieDescription { get; set; }
        
        [   Required, Display(Name="Year Released"), Range(1900,9999)
        ]   public virtual int? YearReleased { get; set; }
        
        public virtual byte[] Version { get; set; }

        
        public virtual IList<Genre> Genres { get; set; }               
    }
}

Genre model

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace AspNetMvcCheckboxList.Models
{
    public class Genre
    {
        public virtual int GenreId { get; set; }
        public virtual string GenreName { get; set; }

        public virtual IList<Movie> Movies { get; set; }
    }
}

Then add a ViewsModels folder on your project, and add the following view model, note line #15 and #17:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using AspNetMvcCheckboxList.Models;

namespace AspNetMvcCheckboxList.ViewsModels
{
    public class MovieInputViewModel
    {
        public string MessageToUser { get; set; }

        public Movie TheMovie { get; set; }
        
        public List<Genre> GenreSelections{ get; set; }

        public List<int> SelectedGenres { get; set; }
    }
}

Add the Controller

Add the Movie controller, and add this Input action, note line #10:

public ViewResult Input(int id = 0)
{
    using (var s = Mapper.GetSessionFactory().OpenSession())
    {
        var movie = id != 0 ? s.Get<Movie>(id) : new Movie { Genres = new List<Genre>() };
        return View(new MovieInputViewModel
        {
            TheMovie = movie,
            GenreSelections = s.Query<Genre>().OrderBy(x => x.GenreName).ToList(),
            SelectedGenres = movie.Genres.Select(x => x.GenreId).ToList()
        });
    }
}

and add the Save action below. Note line #11, if the user don't check anything, the SelectedGenres won't be populated by ASP.NET MVC, and will be left as null. Note line #16, that pushes the selected genres by a user to Movie's Genre collection, use Load, don't use Get. When saving the third table, we are not interested on getting the genre record from database, only its ID(which is already known by the application, database round-trip is not necessary and will not happen), Load prevents eager fetching of object from database. Load doesn't hit the database, it just prepare the object's proxy by assigning it an ID; which when it's time for the program to access the object's properties other than the object ID, that's the time the object will hit the database(to fetch the rest of the properties of the object) by means of its ID(primary key).

[HttpPost]
public ActionResult Save(MovieInputViewModel input)
{
    using (var s = Mapper.GetSessionFactory().OpenSession())
    using (var tx = s.BeginTransaction())
    {
        try
        {
            bool isNew = input.TheMovie.MovieId == 0;

            input.SelectedGenres = input.SelectedGenres ?? new List<int>();
            input.GenreSelections = s.Query<Genre>().OrderBy(x => x.GenreName).ToList();

            input.TheMovie.Genres = new List<Genre>();
            foreach (int g in input.SelectedGenres)
                input.TheMovie.Genres.Add(s.Load<Genre>(g));


            s.SaveOrUpdate(input.TheMovie); // Primary key(MovieId) is automatically set with SaveOrUpdate, and the row version (Version) field too.


            tx.Commit();


            ModelState.Remove("TheMovie.MovieId");

            // No need to remove TheMovie.Version, ASP.NET MVC is not preserving the ModelState of variables with byte array type.
            // Hence, with byte array, the HiddenFor will always gets its value from the model, not from the ModelState
            // ModelState.Remove("TheMovie.Version"); 


            input.MessageToUser = string.Format("Saved. {0}", isNew ? "ID is " + input.TheMovie.MovieId : "");

        }
        catch (StaleObjectStateException)
        {
            ModelState.AddModelError("",
                "The record you attempted to edit was already modified by another user since you last loaded it. Open the latest changes on this record");
        }
                       
    }
    
    return View("Input", input);
}

Note on Save code last line, we just re-use the Input's view.


Add the supporting View

This is our Input's view, note line #56, that's the checkbox list mechanism; we also take into account the concurrent update of same movie, line #22:

@model AspNetMvcCheckboxList.ViewsModels.MovieInputViewModel

@{
    ViewBag.Title = "Movie";
}


<script src="@Url.Content("~/Scripts/jquery.validate.min.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.validate.unobtrusive.min.js")" type="text/javascript"></script>


@{ string controllerName = (string) ViewContext.RouteData.Values["Controller"]; }

@using (Html.BeginForm("Save", controllerName))
{
    @Html.ValidationSummary(true)
    <fieldset>
        <legend>Movie</legend>

        
        @Html.HiddenFor(model => model.TheMovie.MovieId)
        @Html.HiddenFor(model => model.TheMovie.Version)

        <div class="editor-label">
            @Html.LabelFor(model => model.TheMovie.MovieName)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.TheMovie.MovieName)
            @Html.ValidationMessageFor(model => model.TheMovie.MovieName)
        </div>


        <div class="editor-label">
            @Html.LabelFor(model => model.TheMovie.MovieDescription)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.TheMovie.MovieDescription)
            @Html.ValidationMessageFor(model => model.TheMovie.MovieDescription)
        </div>


        <div class="editor-label">
            @Html.LabelFor(model => model.TheMovie.YearReleased)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.TheMovie.YearReleased)
            @Html.ValidationMessageFor(model => model.TheMovie.YearReleased)
        </div>


        <fieldset>
            <legend>Genres</legend>
            
            @foreach (var g in Model.GenreSelections)
            {                                
                <input type="checkbox" name="SelectedGenres" value="@g.GenreId" @(Model.SelectedGenres.Contains(g.GenreId) ? "checked" : "") /> @g.GenreName                
            }
        </fieldset>

        <p>
            <input type="submit" value="Save" />

            
            <a href="@Url.Content(string.Format(@"~/{0}/Input", controllerName))">New</a>

            
        </p>
    </fieldset>
    
    <p>@Model.MessageToUser</p>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

Add the mapper

Uses automapping via Fluent NHibernate. The third table(MovieAssocGenre) is in line 38,39.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;

using NHibernate;
using NHibernate.Dialect;

using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using FluentNHibernate.Automapping;
using FluentNHibernate.Conventions;
using FluentNHibernate.Conventions.Instances;
using FluentNHibernate.Conventions.Helpers;

using AspNetMvcCheckboxList.Models;


namespace AspNetMvcCheckboxList
{
    public static class Mapper
    {
        static ISessionFactory _sf = null;
        public static ISessionFactory GetSessionFactory()
        {
            if (_sf != null) return _sf;

            var fc = Fluently.Configure()
                    .Database(MsSqlConfiguration.MsSql2008.ConnectionString(@"Data Source=localhost;Initial Catalog=TestNhCheckboxList;User id=sa;Password=P@$$w0rd"))
                    .Mappings
                    (m =>
                            m.AutoMappings.Add
                            (
                                AutoMap.AssemblyOf<MvcApplication>(new CustomConfiguration())
                                   .Conventions.Add(ForeignKey.EndsWith("Id"))
                                   .Conventions.Add<RowversionConvention>()  
                                   .Override<Movie>(x => x.HasManyToMany(y => y.Genres).Table("MovieAssocGenre") /* .ParentKeyColumn("MovieId").ChildKeyColumn("GenreId") // optional, the conventions overrides take care of these :-) */ )
                                   .Override<Genre>(x => x.HasManyToMany(y => y.Movies).Table("MovieAssocGenre") /* .ParentKeyColumn("GenreId").ChildKeyColumn("MovieId") // optional, the conventions overrides take care of these :-) */ )
                            )
                    // .ExportTo(@"C:\_Misc\NH")                
                    );


            // Console.WriteLine( "{0}", string.Join( ";\n", fc.BuildConfiguration().GenerateSchemaCreationScript(new MsSql2008Dialect() ) ) );
            // Console.ReadLine();

            _sf = fc.BuildSessionFactory();
            return _sf;
        }


        class CustomConfiguration : DefaultAutomappingConfiguration
        {
            IList<Type> _objectsToMap = new List<Type>()
            {
                // whitelisted objects to map
                typeof(Movie), typeof(Genre)
            };
            public override bool ShouldMap(Type type) { return _objectsToMap.Any(x => x == type); }
            public override bool IsId(FluentNHibernate.Member member) { return member.Name == member.DeclaringType.Name + "Id"; }            
        }


        class RowversionConvention : IVersionConvention
        {
            public void Apply(IVersionInstance instance) { instance.Generated.Always(); }
        }


    }
}

And this is the physical implementation. The R in ORM :-)

create table Movie
(
MovieId int identity(1,1) not null primary key,
MovieName varchar(100) not null unique,
MovieDescription varchar(100) not null,
YearReleased int not null,
Version rowversion not null
);



create table Genre
(
GenreId int identity(1,1) not null primary key,
GenreName varchar(100) not null unique
);


create table MovieAssocGenre
(
MovieAssocGenreId int identity(1,1) not null primary key,
MovieId int not null references Movie(MovieId),
GenreId int not null references Genre(GenreId),
constraint uk_MovieAssocGenre unique(MovieId, GenreId)
);



insert into Genre(GenreName) values('Action'),('Comedy'),('Documentary'),('Romance'),('Sci-Fi'),('Thriller');

Get the code here: http://code.google.com/p/aspnet-mvc-checkbox-list-demo/downloads/list

To contrast NHibernate approach to Entity Framework, visit http://www.ienablemuch.com/2011/07/using-checkbox-list-on-aspnet-mvc-with_16.html

Sample output:

Start embracing the five pillars of maintainable software: ORM, the MVC pattern, unit testing, mocking, IoC.

An advice to Microsoft toolchain users. Embrace what they churn out these past years. ASP.NET MVC, Entity Framework, Code-first EF(i.e. model-centric coding, not to be confused with designer), jQuery(not made by Microsoft, but now has a blessing from them). They deliver nice technologies way very late, it's a disservice to your skillset if you will not quickly learn these enabling technologies. Microsoft is already late, don't let yourself be late with Microsoft's latest technology.


MVC
MonoRail 2003 vs ASP.NET MVC 2007

ORM
NHibernate 2004 (Hibernate 2001) vs Entity Framework 2008


See? how long before Microsoft "legitimizes" certain technologies to the eyes of many Microsoft tools-using devs. And what's the year now? 2011. Hiding in the cave?


Use 21st-century techniques Don't lag behind, leave ASP.NET use ASP.NET MVC, leave ADO.NET use ORM and don't use sp-centric programming, not everyone are going gaga over sp, look at your Java counterpart devs, their DBA trusted them on touching the base tables, binding the ORM directly to tables, could it be that Java devs are DBA-grade application developers? and as such are more amenable and trusted on using ORM, hmm.. :-)


Start embracing the five pillars of maintainable software: ORM, the MVC pattern, unit testing, mocking, IoC.

WCF error. The underlying connection was closed: The connection was closed unexpectedly.

To trace the root of cause of that generic error more easily, put this in your WCF's web.config

<system.diagnostics>
  <sources>
    <source name="System.ServiceModel" switchValue="Information, ActivityTracing" propagateActivity="true">
      <listeners>
        <add name="traceListener" type="System.Diagnostics.XmlWriterTraceListener" initializeData= "c:\_Misc\traces.svclog" />
      </listeners>
    </source>
  </sources>
</system.diagnostics>

Then double-click the traces.svclog in C:\_Misc folder