Thursday, July 28, 2011

Sort the version number stored in string type

Read some question on stackoverflow on how to get the latest version number, the version number is of string type though. Here's one of my answer (aside from multiplying each digit group ):


with a as
(
 select * from ( values('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') ) as b(c)     
),
x as
(
 select c, 
  Ranking = RANK() 
   over(order by 
    convert(int,PARSENAME(c,3)), 
    convert(int,PARSENAME(c,2)), 
    convert(int,PARSENAME(c,1))) 
 from a
)
select * 
from x;

-- PARSENAME usage is inspired by http://www.sql-server-helper.com/tips/sort-ip-address.aspx

Output:
c      Ranking
2.1.4  1
2.1.5  2
2.1.12 3
2.2.1  4


To get the latest version, just sort the query in descending order then get the row with the ranking of 1:

with a as
(
 select * from ( values('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') ) as b(c)     
),
x as
(
 select c, 
  Ranking = RANK() 
   over(order by 
    convert(int,PARSENAME(c,3)) desc, 
    convert(int,PARSENAME(c,2)) desc, 
    convert(int,PARSENAME(c,1)) desc) 
 from a
)
select * 
from x 
where Ranking = 1

Output
c     Ranking
2.2.1 1

Thursday, July 21, 2011

IValidatableObject client-side validation

We cannot really bring IValidatableObject validation to client-side, unless someone could transpile C# code to javascript :p Another hurdle is IValidatableObject can access resources from server, and that cannot be transpiled to javascript :-) What the following article will show you is how to bring those IValidatableObject validation results back to client-side via ajax, so no page round-trip would occur. As IValidatableObject results are also placed in ModelState, we can nicely retrieve not just the IValidatableObject results, but also those errors that get past property-level validations and model-level(IValidatableObject) validations, e.g. database constraints exceptions, concurrent update exception, deleted record exception, server errors, disk out of space error :-) etc.

First, let's design the API. Line #49 and #83

@model NhConcurrencyOnAspNetMvc.Models.Song

@{
 ViewBag.Title = "Input";
}

<h2>Input</h2>


@DateTime.Now

<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>
<script src="/Scripts/IEnableMuch/ienablemuch.js" type="text/javascript"></script>

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

@using (Html.BeginForm("Save", controllerName) )
{

 @Html.HiddenFor(x => x.SongId)
 @Html.HiddenFor(x => x.Version)
 <fieldset>
  <legend>Song</legend>

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

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

  <p>
   <input type="submit" value="Save" />
  </p>
 </fieldset>
 
 
 
 @Html.JsAccessibleValidationSummary(true)
 
   
}


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



@ViewBag.Message



<script type="text/javascript">

 $(function () {

  $('input[type=submit]').click(function (e) {            
   e.preventDefault();

   if ($('form').valid()) {

    //alert('test');
    $.ajax({
     url: '/Song/SaveViaAjax',
     type: 'POST',
     data: $('form').serialize(),
     dataType: 'json',
     success: function (data) {


      var isOk = $('form').modelValidation(data);


      if (isOk) {
       $('input[name=SongId]').val(data.SongId);
       $('input[name=Version]').val(data.Version);

       alert('Saved.');
      }

     },
     error: function (xhr, err) {
      alert("readyState: " + xhr.readyState + "\nstatus: " + xhr.status);
      alert("responseText: " + xhr.responseText);
     }
    }); //ajax

   } //end if valid

  }); // submit


 });   // ready
</script>

On Controller line #36, return ModelState's valid status and its errors (model-level errors and property-level errors), we segregate those two errors so it's easier on jQuery's side to process those.

[HttpPost]
public JsonResult SaveViaAjax(Song song)
{
 SaveCommon(song);

 
 // you can use the following commented code
 /* 
 var v = from m in ModelState.AsEnumerable()
   from e in m.Value.Errors
   select new { m.Key, e.ErrorMessage };
 
 
 return Json(
  new 
  { 
   ModelState = 
    new 
    { 
     IsValid = ModelState.IsValid, 
     PropertyErrors = v.Where(x => !string.IsNullOrEmpty(x.Key)),
     ModelErrors = v.Where(x => string.IsNullOrEmpty(x.Key))
    },
   SongId = song.SongId,
   Version = Convert.ToBase64String(song.Version ?? new byte[]{} )
  });*/


 
 // alternatively, you can use an extension method, less error-prone,
 // pattern it after the code above

 return Json(
  new 
  { 
   ModelState = ModelState.ToJsonValidation(),
   SongId = song.SongId,
   Version = Convert.ToBase64String(song.Version ?? new byte[]{} )
  });


}





We refactor common code, line #9, so in case the user turned off the javascript, our app can degrade gracefully:

[HttpPost]
public ActionResult Save(Song song)
{
 SaveCommon(song);

 return View("Input", song);
}

private void SaveCommon(Song song)
{
 using (var s = Mapper.GetSessionFactory().OpenSession())
 using (var tx = s.BeginTransaction())
 {
  try
  {
   if (ModelState.IsValid)
   {
    s.SaveOrUpdate(song);

    tx.Commit();

    ModelState.Remove("SongId");
    // no need to issue ModelState.Remove on Version property; with byte array, ASP.NET MVC always get its value from the model, not from ModelState
    // ModelState.Remove("Version") 
   }

  }
  catch (StaleObjectStateException)
  {                    
   s.Evict(song);
   var dbValues = s.Get<Song>(song.SongId);
   var userValues = song;


   if (dbValues == null)
   {
    ModelState.AddModelError("", "This record you are attempting to save is already deleted by other user");
    return;
   }

   if (dbValues.SongName != userValues.SongName)
    ModelState.AddModelError("SongName", "Current value made by other user: " + dbValues.SongName);


   if (dbValues.AlbumName != userValues.AlbumName)
    ModelState.AddModelError("AlbumName", "Current value made by other user: " + dbValues.AlbumName);

   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");

  }
  catch (Exception ex)
  {
   ModelState.AddModelError("", ex.Message);
  }

 }
}


public ActionResult Delete(int id, byte[] Version)
{
 using (var s = Mapper.GetSessionFactory().OpenSession())
 using (var tx = s.BeginTransaction())
 {

  
  if (Request.HttpMethod == "POST")
  {
   var versionedDelete = new Song { SongId = id, Version = Version };
   try
   {
    s.Delete(versionedDelete);
    tx.Commit();

    return RedirectToAction("Index");
   }
   catch (StaleObjectStateException)
   {
    s.Evict(versionedDelete);

    var songToDelete = s.Get<Song>(id);
    if (songToDelete == null)
     return RedirectToAction("Index");

    ModelState.AddModelError("", "The record you are attempting to delete was modified by other user first. Do you still want to delete this record?");
    return View(songToDelete);
   }
  }
  else
  {
   var songToDelete = s.Get<Song>(id);
   return View(songToDelete);
  }
  
 }
}

The model and its server-level validations. These validations can be instantly seen on client-side(no page refresh) without doing a page roundtrip, use the jQuery library before the screenshots guide.


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

namespace NhConcurrencyOnAspNetMvc.Models
{
 public class Song : IValidatableObject
 {
  public virtual int SongId { get; set; }
  [Required] public virtual string SongName { get; set; }
  [Required] public virtual string AlbumName { get; set; }

  public virtual byte[] Version { get; set; }

  public virtual IEnumerable<ValidationResult> Validate(ValidationContext validationContext)
  {

   // model-level validation
   if (SongName == "blah" && AlbumName == "meh")
   {
    yield return new ValidationResult("Combined song name and album name cannot be blah and meh");
    yield return new ValidationResult("*", new[] { "SongName", "AlbumName" });                
   }
   else
   {

    // model-level validation
    if (SongName[0] != AlbumName[0])
    {
     yield return new ValidationResult("Song name and album name must start with same letter");
     yield return new ValidationResult("*", new[] { "AlbumName", "SongName" });                    
    }

    // property, inline style
    if (!SongName.ToUpper().Contains("LOVE"))
     yield return new ValidationResult("Song name must have a word love", new[] { "SongName" });

    // property, but using asterisk style
    if (!AlbumName.ToUpper().Contains("GREAT"))
    {
     yield return new ValidationResult("Album name must have a word great");
     yield return new ValidationResult("*", new[] { "AlbumName" });                    
    }
   }
   
  }
 }
}



There's a bug on Html.ValidationSummary when excludePropertyErrors is true, there's no <div class="validation-summary-valid"> tag emitted, i.e. it doesn't output anything, it's exactly the same as if not placing Html.ValidationSummary in code at all. As it is, there's no way for javascript to output validations on page. To rectify that problem, we still output validation-summary-valid on page even the excludePropertyErrors is true, with one caveat, we set data-valmsg-summary to false (line #83); this won't have any effect on existing client-side validation, jquery validation library just look for data-valmsg-summary=true attribute.


Below is the corresponding HtmlHelper, API-wise, it behaves exactly the same as its eight Html.ValidationSummary cousin. In fact, it's safe to keep Html.JsAccessibleValidationSummary in your code even you will not use IValidatableObject client-side validation. Loving extension methods :-) Click the Expand Source.


public static object ToJsonValidation(this ModelStateDictionary modelState)
{
 var v = from m in modelState.AsEnumerable()
   from e in m.Value.Errors
   select new { m.Key, e.ErrorMessage };

 return new
 {
  IsValid = modelState.IsValid,
  PropertyErrors = v.Where(x => !string.IsNullOrEmpty(x.Key)),
  ModelErrors = v.Where(x => string.IsNullOrEmpty(x.Key))
 };
}



public static MvcHtmlString JsAccessibleValidationSummary(this HtmlHelper htmlHelper)
{
 return htmlHelper.ValidationSummary();
}


public static MvcHtmlString JsAccessibleValidationSummary(this HtmlHelper htmlHelper, bool excludePropertyErrors)
{
 if (!htmlHelper.ViewData.ModelState.IsValid || !excludePropertyErrors)
  return htmlHelper.ValidationSummary(excludePropertyErrors);
 else
 {
  return htmlHelper.BuildValidationSummary(null, null);
 }
}

public static MvcHtmlString JsAccessibleValidationSummary(this HtmlHelper htmlHelper, string message)
{
 return htmlHelper.ValidationSummary(message);
}

public static MvcHtmlString JsAccessibleValidationSummary(this HtmlHelper htmlHelper, bool excludePropertyErrors, string message)
{
 if (!htmlHelper.ViewData.ModelState.IsValid || !excludePropertyErrors)
  return htmlHelper.ValidationSummary(excludePropertyErrors, message);
 else
  return htmlHelper.BuildValidationSummary(message, (IDictionary<string, object>)null);

}


public static MvcHtmlString JsAccessibleValidationSummary(this HtmlHelper htmlHelper, string message, object htmlAttributes)
{
 return htmlHelper.ValidationSummary(message, htmlAttributes);
}


public static MvcHtmlString JsAccessibleValidationSummary(this HtmlHelper htmlHelper, bool excludePropertyErrors, string message, object htmlAttributes)
{
 if (!htmlHelper.ViewData.ModelState.IsValid || !excludePropertyErrors)
  return htmlHelper.ValidationSummary(excludePropertyErrors, message, htmlAttributes);
 else
  return htmlHelper.BuildValidationSummary(message, HtmlHelper.AnonymousObjectToHtmlAttributes(htmlAttributes));
}

public static MvcHtmlString JsAccessibleValidationSummary(this HtmlHelper htmlHelper, string message, IDictionary<string, object> htmlAttributes)
{
 return htmlHelper.ValidationSummary(message, htmlAttributes);
}

public static MvcHtmlString JsAccessibleValidationSummary(this HtmlHelper htmlHelper, bool excludePropertyErrors, string message, IDictionary<string, object> htmlAttributes)
{
 if (!htmlHelper.ViewData.ModelState.IsValid || !excludePropertyErrors)
  return htmlHelper.ValidationSummary(excludePropertyErrors, message, htmlAttributes);
 else
  return htmlHelper.BuildValidationSummary(message, htmlAttributes);
}


private static MvcHtmlString BuildValidationSummary(this HtmlHelper htmlHelper, string message, IDictionary<string, object> htmlAttributes)
{
 // excludePropertyErrors is always true here

 string messageSpan;
 if (!String.IsNullOrEmpty(message))
 {
  TagBuilder spanTag = new TagBuilder("span");
  spanTag.SetInnerText(message);
  messageSpan = spanTag.ToString(TagRenderMode.Normal) + Environment.NewLine;
 }
 else
 {
  messageSpan = null;
 }

 TagBuilder divBuilder = new TagBuilder("div");

 divBuilder.AddCssClass(HtmlHelper.ValidationSummaryValidCssClassName);
 divBuilder.InnerHtml = messageSpan + "<ul></ul>";


 // We use false, so jQuery won't output property validation on summary
 divBuilder.MergeAttribute("data-valmsg-summary", "false");

 divBuilder.MergeAttributes(htmlAttributes);


 return divBuilder.ToMvcHtmlString(TagRenderMode.Normal);
}

// copied from ASP.NET MVC. so many internals in ASP.NET MVC :-)
private static MvcHtmlString ToMvcHtmlString(this TagBuilder tagBuilder, TagRenderMode renderMode)
{
 System.Diagnostics.Debug.Assert(tagBuilder != null);
 return new MvcHtmlString(tagBuilder.ToString(renderMode));
}


Here's the modelValidation code, it's very lean:
(function ($) {

 $.fn.modelValidation = function (data) {

  var form = $(this);

  if (form.size() > 1) {
   alert('There are more than one form in this page. Contact the dev to indicate the specific form that needed be validated');
   return;
  }



  if (!data.ModelState.IsValid) {
   showInvalid(data, form);
  }
  else
   clearInvalid(form);

  return data.ModelState.IsValid;

 }



 function clearInvalid(form) {
  var valSum = $('div[data-valmsg-summary]', form)
  valSum.removeClass().addClass('validation-summary-valid');
  var errorList = $('> ul', valSum);
  errorList.html('');
 }

 function showInvalid(data, form) {

  valSum = $('div[data-valmsg-summary]', form);


  if (valSum.attr('data-valmsg-summary') == undefined)
   valSum = null;



  var errorList = null;
  if (valSum) {
   valSum.removeClass().addClass('validation-summary-errors');
   errorList = $('> ul', valSum);
   errorList.html('');
  }


  $.each(data.ModelState.PropertyErrors, function () {

   if (valSum && valSum.attr('data-valmsg-summary') == 'true') {
    errorList.append($('<li />').text(this.ErrorMessage));
   }


   var propVal = $('span[data-valmsg-for=' + this.Key + ']', form);
   propVal.removeClass().addClass('field-validation-error').text(this.ErrorMessage);

   var prop = $('input[name=' + this.Key + ']', form);
   prop.addClass('input-validation-error');
  });



  if (valSum) {
   $.each(data.ModelState.ModelErrors, function () {
    errorList.append($('<li />').text(this.ErrorMessage));
   });
  }

 }//showInvalid


})(jQuery);


Complete code here: http://code.google.com/p/nh-concurrency-handling-on-aspnet-mvc-demo/downloads/list


Code simulation output for fetching ModelState errors(of which IValidatableObject also places its errors) messages from server using ajax, time remain constant. You can also try to simulate sans ajax, the output is same, with different time of course :-)

Session 1 ( 12:44:20 PM ). Save button not yet clicked

Session 1 ( 12:44:22 PM ). Save button not yet clicked

Session 3. Delete not yet clicked

Session 1 ( 12:44:20 PM ). Save button clicked. Shows the server business validations without refreshing the whole page. No page refresh, note the time

Session 1 ( 12:44:20 PM ). Valid input done on Song Name. Song name and album name must start with same letter. Validation from server is invoked. No page refresh, note the time

Session 1 ( 12:44:20 PM ). Valid input done on Song Name, yet the combined validation of Song Name and Album Name from business validation is still not valid. No page refresh, note the time

Session 1 ( 12:44:20 PM ). Combined validation of Song Name and Album Name passed. Album name is still not valid. No page refresh, note the time

Session 1 ( 12:44:20 PM ). Every business validations passed. Saved successfully

Session 2( 12:44:22 PM ). After session 1 finished. Session 2 user tried to save his/her edit concurrently with Session 1. The database error is shown back, there's no page refresh, note the time


Session 3. It detected changes made on other sessions

Session 3. Delete successful

Session 1 ( 12:44:20 PM ). Tried to save again. It shows the concurrent delete by other session. The server from server is  shown back. No page refresh, note the time


UPDATE 2012-09-04


C# can be transpiled to Javascript: http://www.saltarelle-compiler.com/documentation/supported-c-language-features

Wednesday, July 20, 2011

Class don't appear on Add View dialog

If your class don't appear in Create a strongly-typed view, compile your code first, then try to Add a View again, the class will appear in Model class dropdown list then

Monday, July 18, 2011

Entity Framework: AsNoTracking() and context.DbSet.Find(pk), a dangerous combo

Using context.YourDbSetHere.AsNoTracking() (line #11), all your succeeding context.YourDbSetHere.Find(pk) (line #35) will always hit the database

Actually, it's the combo of context.YourSourceDbSetHere.AsNoTracking() and context.DestinationDbSetHere.Add(context.YourSourceDbSetHere.Find(pk)) that is dangerous.


There's no clean way for a stub/proxy object (an object with only had its ID property set, the rest of the properties are left with their default value(i.e. null, empty string, zero, etc)) to represent an entity on many-to-many inserts.




[HttpPost]
public ActionResult Save(MovieInputViewModel input)
{
 using (var db = new TheMovieContext())
 {
  try
  {
   bool isNew = input.TheMovie.MovieId == 0;

   input.SelectedGenres = input.SelectedGenres ?? new List<int>();                    
   input.GenreSelections = db.Genres.AsNoTracking().OrderBy(x => x.GenreName).ToList();


   var movie = !isNew ? db.Movies.Find(input.TheMovie.MovieId) : input.TheMovie;                    

   if (isNew)
    db.Movies.Add(movie);
   else
   {
    db.Entry(movie).Property("Version").OriginalValue = input.TheMovie.Version;
    db.Entry(movie).State = System.Data.EntityState.Unchanged;


    // dirty this all the time even this is not changed by the user, 
    // so we have a simplified mechanism for concurrent update 
    // when the associated Genre(s) is modified. 
    // June 3, 2012: Above comment is stale already. For detecting concurrent update, use TimeStamp attribute instead of dirtying-technique+Concurrency attribute combo technique.
    // Read the concurrent update technique using Timestamp at this post: http://www.ienablemuch.com/2011/07/using-checkbox-list-on-aspnet-mvc-with_16.html
    db.Entry(movie).Property("MovieName").IsModified = true;
   }


   movie.Genres = movie.Genres ?? new List<Genre>();
   movie.Genres.Clear();
   foreach (int g in input.SelectedGenres)
   {
    movie.Genres.Add(db.Genres.Find(g));    
   }

   UpdateModel(movie, "TheMovie", includeProperties: null, excludeProperties: new string[] { "Version" });


   db.SaveChanges();

   db.Entry(movie).Reload();
   input.TheMovie.Version = movie.Version;

   
   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 = input.MessageToUser + " " + string.Format("Saved. {0}", isNew ? "ID is " + input.TheMovie.MovieId : "");

  }
  catch (DbUpdateConcurrencyException)
  {
   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);
}



To solve that problem, monitor your context's ChangeTracker, line #35 to 47. Even you are using AsNoTracking, line #35 to 47 will not make a database roundtrip to fetch the Genre's row(s), inserting into many-to-many table will be streamlined.

[HttpPost]
public ActionResult Save(MovieInputViewModel input)
{
 using (var db = new TheMovieContext())
 {
  try
  {
   bool isNew = input.TheMovie.MovieId == 0;

   input.SelectedGenres = input.SelectedGenres ?? new List<int>();                    
   input.GenreSelections = db.Genres.AsNoTracking().OrderBy(x => x.GenreName).ToList();


   var movie = !isNew ? db.Movies.Find(input.TheMovie.MovieId) : input.TheMovie;                    

   if (isNew)
    db.Movies.Add(movie);
   else
   {
    db.Entry(movie).Property("Version").OriginalValue = input.TheMovie.Version;
    db.Entry(movie).State = System.Data.EntityState.Unchanged;


    // dirty this all the time even this is not changed by the user, 
    // so we have a simplified mechanism for concurrent update 
    // when the associated Genre(s) is modified
    db.Entry(movie).Property("MovieName").IsModified = true;
   }


   movie.Genres = movie.Genres ?? new List<Genre>();
   movie.Genres.Clear();
   foreach (int g in input.SelectedGenres)
   {
    var cachedGenre = db.ChangeTracker.Entries<Genre>().SingleOrDefault(x => x.Entity.GenreId == g);

    Genre gx = null;
    if (cachedGenre != null)
     gx = cachedGenre.Entity;
    else
    {
     gx = new Genre { GenreId = g };
     db.Entry(gx).State = EntityState.Unchanged;
     input.MessageToUser = input.MessageToUser + "Not yet in cache " + g.ToString() + ";";
    }

    movie.Genres.Add(gx);
   }

   UpdateModel(movie, "TheMovie", includeProperties: null, excludeProperties: new string[] { "Version" });



   db.SaveChanges();

   db.Entry(movie).Reload();
   input.TheMovie.Version = movie.Version;


   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 = input.MessageToUser + " " + string.Format("Saved. {0}", isNew ? "ID is " + input.TheMovie.MovieId : "");

  }
  catch (DbUpdateConcurrencyException)
  {
   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);
}


This article could have been titled:
Entity Framework: Representing an entity with a stub/proxy object is a one big exercise of leaky abstraction.

Microsoft could borrow some technology from NHibernate for their Entity Framework. They should implement context.Load<EntityHere>(pkHere). C'mon Microsoft, spare us the trouble of leaky abstractions of your Entity Framework :-)


Related to Using checkbox list on ASP.NET MVC with Entity Framework 4.1


Sample outputs

On first save:



On second save:



Make your query malleable with Linq

Perhaps you've already seen/made this query pattern in your coding career:

SELECT *
FROM SchoolOfThought
WHERE
 (
  @inputTeacher = ''
  OR 
  Teacher = @inputTeacher
 )
 AND
 (
  @inputSubject = ''
  OR
  Subject = @inputSubject
 )
 AND
 (
  @inputQuality IS NULL
  OR
  Quality = @inputQuality
 ) 


That query is synonymous with this approach:

string inputTeacher = Console.ReadLine();
string inputSubject = Console.ReadLine();
string inputQuality = Console.ReadLine();

string filter = "";

if (inputTeacher != "")
 filter = string.Format("Teacher = '{0}'", inputTeacher);
 
if (inputSubject != "")
 filter = filter + (filter.Length != 0 ? " AND " : "" ) + string.Format("Subject = '{0}'", inputSubject);
 
if (inputQuality != "")
 filter = filter + (filter.Length != 0 ? " AND " : "" ) + string.Format("Subject = {0}", int.Parse(inputQuality));
 
string query = "SELECT * FROM SchoolOfThought " + (filter.Length != 0 ? "WHERE " + filter : "");


If the user has input on subject only, say Math, the resulting query is shorter:

SELECT  *
FROM  SchoolOfThought
WHERE Subject = 'Math';


If the user didn't input anything, The resulting query is much shorter:

SELECT  *
FROM  SchoolOfThought;



Even though concatenation approach is performant and lighter on network traffic(but don't do micro-optimizations), you will eschew the concatenation approach in favor of the first code. The reasons are twofold; first, you can't guarantee that you can safeguard your query from SQL-injection; second, code-review-wise, you don't want your code be flagged as having a code smell, the second approach is longer and look every bit as brittle.




Now, everything changes when Linq came to the scene, we no longer have to write our query patterned after the first code. We could now write the code below with a warm and fuzzy feeling that there will be no SQL-injection that could accidentally creep in the code. Lambda-using-Linq:

string inputTeacher = Console.ReadLine();
string inputSubject = Console.ReadLine();
string inputQuality = Console.ReadLine();

var query = s.Query<SchoolOfThought>();

if (inputTeacher.Length > 0)
 query = query.Where(x => x.Teacher == inputTeacher);

if (inputSubject.Length > 0)
 query = query.Where(x => x.Subject == inputSubject);

if (inputQuality.Length > 0)
 query = query.Where(x => x.Quality == int.Parse(inputQuality));

foreach (var item in query)
{
 Console.WriteLine("{0} {1} {2}", item.Teacher, item.Subject, item.Quality);
}


If you only input one variable, say Math on Subject, the resulting query will be this:

exec sp_executesql N'select schoolofth0_.SchoolOfThoughtId as SchoolOf1_3_, schoolofth0_.Teacher as Teacher3_, schoolofth0_.Subject as Subject3_, schoolofth0_.Quality as Quality3_ 
from [SchoolOfThought] schoolofth0_ 
where schoolofth0_.Subject=@p0',N'@p0 nvarchar(4000)',@p0=N'Math'



Linq-chaining has the advantage of preventing Sql-injection while maintaining the advantage of strongly-typed(as opposed to stringly-typed programming, e.g. concatenation) programming, strongly-typed means less error, you have autocomplete at your fingertips when you are using an IDE, and you have a code that is refactoring-friendly anytime. And last but not the least, with Linq you'll have more confidence the program is correct even before it is run.


If the inputSubject and inputQuality are required inputs; compiled-expression-wise, these five code has no differences:

Approach 1
string inputSubject = "Math";
string inputQuality = "80";

var query = 
 from x in s.Query<SchoolOfThought>()
 where x.Subject == inputSubject && x.Quality == int.Parse(inputQuality)
 select x;

Approach 2. Don't worry, parenthesis isn't required, compiler is not confused where the Linq boundaries end, think of from having a built-in open parenthesis, and select having a built-in close parenthesis.
string inputSubject = "Math";
string inputQuality = "80";

var query =
 from x in   
               
      from x in s.Query<SchoolOfThought>()
      where x.Subject == inputSubject
      select x  

 where x.Quality == int.Parse(inputQuality)
 select x;

Approach 3
string inputSubject = "Math";
string inputQuality = "80";

var query =
 (from x in s.Query<SchoolOfThought>()
 where x.Subject == inputSubject
 select x)
 .Where(x => x.Quality == int.Parse(inputQuality));


Approach 4
string inputSubject = "Math";
string inputQuality = "80";

var query = s.Query<SchoolOfThought>()
 .Where(x => x.Subject == inputSubject && x.Quality == int.Parse(inputQuality));

Approach 5
string inputSubject = "Math";
string inputQuality = "80";

var query = s.Query<SchoolOfThought>()
 .Where(x => x.Subject == inputSubject)
 .Where(x => x.Quality == int.Parse(inputQuality));


All the five code approaches above is compiled to this code:
exec sp_executesql 
N'select schoolofth0_.SchoolOfThoughtId as SchoolOf1_3_, schoolofth0_.Teacher as Teacher3_, schoolofth0_.Subject as Subject3_, schoolofth0_.Quality as Quality3_ 
from [SchoolOfThought] schoolofth0_ 
where schoolofth0_.Subject=@p0 and schoolofth0_.Quality=@p1',N'@p0 nvarchar(4000),@p1 int',@p0=N'Math',@p1=80 


Lastly, if you want your Linq-chaining to look as query-like(instead of Lambda-ish approach) as possible:


string inputTeacher = Console.ReadLine();
string inputSubject = Console.ReadLine();
string inputQuality = Console.ReadLine();


var query = s.Query<SchoolOfThought>();

if (inputTeacher.Length > 0)
 query = 
  from x in query 
  where x.Teacher == inputTeacher 
  select x;

if (inputSubject.Length > 0)
 query = 
  from x in query 
  where x.Subject == inputSubject 
  select x;

if (inputQuality.Length > 0)
 query = 
  from x in query 
  where x.Quality == int.Parse(inputQuality)
  select x;

I deliberately wrap the Linq clauses on their own line; so as to sway you to use the Lambda-ish approach instead, which arguably is way much shorter some of the times :-) Some of the times only, not all the times ;-)

Sunday, July 17, 2011

Compelling reason to abandon ADO.NET and use Linq-capable ORM instead

Many scratch their head with this innocent question:

How to pass "Philippines,China,Canada" as parameter @list to IN clause? This isn't working: SELECT * FROM Country WHERE CountryName IN (@list)

Using Linq, that problem can be easily solved. There's no method on scalar type that can test against a list, a good Linq provider (used Linq-to-NHibernate here) don't have a problem translating this(list against scalar) though:

var countryList = 
 from c in s.Query<Country>()
 where new[]{ "Philippines", "China" }.Contains(c.CountryName)
 select c;

The generated query:

exec sp_executesql N'select country0_.CountryId as CountryId1_, country0_.CountryName as CountryN2_1_, country0_.Population as Population1_ 
from [Country] country0_ 
where country0_.CountryName in (@p0 , @p1)',N'@p0 nvarchar(4000),@p1 nvarchar(4000)',@p0=N'Philippines',@p1=N'China'

Linq can accept variable list too:

Console.Write("Input list: ");
string[] list = Console.ReadLine().Split(','); // example input is Philippines,Canada,China

var countryList =
 from c in s.Query<Country>()
 where list.Contains(c.CountryName)
 select c;


The generated query:

exec sp_executesql N'select country0_.CountryId as CountryId1_, country0_.CountryName as CountryN2_1_, country0_.Population as Population1_ 
from [Country] country0_ 
where country0_.CountryName in (@p0 , @p1 , @p2)',N'@p0 nvarchar(4000),@p1 nvarchar(4000),@p2 nvarchar(4000)',@p0=N'Philippines',@p1=N'Canada',@p2=N'China'

Typical Fluent NHibernate Automapping boilerplate code

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

using NHibernate;

using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using FluentNHibernate.Automapping;

using FluentNHibernate.Conventions;
using FluentNHibernate.Conventions.Instances;

using NHibernateFetchProblems.Models;

namespace NHibernateFetchProblems.DbMapping
{
    public static class NhMapping
    {
        private static ISessionFactory _isf = null;
        public static ISessionFactory GetSessionFactory()
        {
            if (_isf != null) return _isf;

            var cfg = new StoreConfiguration();

            var sessionFactory = Fluently.Configure()
              .Database(MsSqlConfiguration.MsSql2008.ShowSql().ConnectionString(                
                  "Server=localhost; Database=NhFetch; Trusted_Connection=true; MultipleActiveResultSets=true"
                  ))
              .Mappings(m =>
                m.AutoMappings
                  .Add(AutoMap.AssemblyOf<Person>(cfg)
                  .Conventions.Add<ReferenceConvention>()
                  .Conventions.Add<HasManyConvention>()  
                  .Override<Question>(x => x.HasMany(y => y.QuestionComments).KeyColumn("Question_QuestionId").Cascade.AllDeleteOrphan().Inverse())
                  .Override<Question>(x => x.HasMany(y => y.Answers).KeyColumn("Question_QuestionId").Cascade.AllDeleteOrphan().Inverse())
                  .Override<Answer>(x => x.HasMany(y => y.AnswerComments).KeyColumn("Answer_AnswerId").Cascade.AllDeleteOrphan().Inverse())
                  )
                )
              .BuildSessionFactory();


            _isf = sessionFactory;

            return _isf;
        }
    }


    public class StoreConfiguration : DefaultAutomappingConfiguration
    {
        readonly IList<Type> _objectsToMap = new List<Type>()
        {
            // whitelisted objects to map
            typeof(Person), typeof(Question), typeof(QuestionComment), typeof(Answer), typeof(AnswerComment)
        };
        public override bool IsId(FluentNHibernate.Member member)
        {
            // return base.IsId(member);
            return member.Name == member.DeclaringType.Name + "Id";
        }
        public override bool ShouldMap(Type type) { return _objectsToMap.Any(x => x == type); }


    }
    
    public class ReferenceConvention : IReferenceConvention
    {
        public void Apply(IManyToOneInstance instance)
        {
            instance.Column(
                instance.Name + "_" + instance.Property.PropertyType.Name + "Id");
        }
    }    
    
    class HasManyConvention : IHasManyConvention
    {
    
        public void Apply(IOneToManyCollectionInstance instance)
        {
            instance.Inverse();
            instance.Cascade.AllDeleteOrphan();
        }
    }    

}

Saturday, July 16, 2011

Using checkbox list on ASP.NET MVC with Entity Framework 4.1 (telling the users about the third table is a leaky abstraction)

UPDATE August 2, 2011
The code is now efficient, movie.Genres.Add can just receive an stub object


[HttpPost]
public ActionResult Save(MovieInputViewModel input)
{
    using (var db = new TheMovieContext())
    {
        try
        {
            bool isNew = input.TheMovie.MovieId == 0;

            input.SelectedGenres = input.SelectedGenres ?? new List<int>();                    
            input.GenreSelections = db.Genres.AsNoTracking().OrderBy(x => x.GenreName).ToList();


            var movie = !isNew ? db.Movies.Find(input.TheMovie.MovieId) : input.TheMovie;                    

            if (isNew)
                db.Movies.Add(movie);
            else
            {
                db.Entry(movie).Property(x => x.Version).OriginalValue = input.TheMovie.Version;
                                        
                db.Entry(movie).State = EntityState.Modified;
            }


            movie.Genres = movie.Genres ?? new List<Genre>();
            movie.Genres.Clear();
            foreach (int g in input.SelectedGenres)
                movie.Genres.Add(db.LoadStub<Genre>(g));
            
            UpdateModel(movie, "TheMovie", includeProperties: null, excludeProperties: new string[] { "Version" });                   

            db.SaveChanges();

            
            input.TheMovie.Version = movie.Version;


            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 = input.MessageToUser + " " + string.Format("Saved. {0}", isNew ? "ID is " + input.TheMovie.MovieId : "");

        }
        catch (DbUpdateConcurrencyException)
        {
            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);
}

We changed the Movie rowversion's attribute to Timestamp attribute(from ConcurrencyCheck attribute) so we will not need any work-around anymore(always setting one property to force-dirty the record)

public class Movie
{
    [Key]
    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; }
    
    [Timestamp]
    public virtual byte[] Version { get; set; }

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

Here's the LoadStub extension method. If we could know how to get the primary key name of an entity, we will remove the primayKeyName property in code. For now, the convention-over configuration(using Entity name + ID as the default primary key) could prevent explicitly passing the primary key name.

Get the LoadStub code at http://www.ienablemuch.com/2011/08/entity-frameworks-nhibernate_02.html




Old post

Using checkboxes is a many-to-many design. In this post, I'll show you how to make the process of inserting to a third table a fairly automatic process with Entity Framework.

Most of the supporting logic is similar with http://www.ienablemuch.com/2011/07/using-checkbox-list-on-aspnet-mvc-with.html(NHibernate version)

The only difference is in the way the model is being saved.

[HttpPost]
public ActionResult Save(MovieInputViewModel input)
{
    using (var db = new TheMovieContext())
    {
        try
        {
            bool isNew = input.TheMovie.MovieId == 0;

            input.SelectedGenres = input.SelectedGenres ?? new List<int>();                    
            input.GenreSelections = db.Genres.AsNoTracking().OrderBy(x => x.GenreName).ToList();


            var movie = !isNew ? db.Movies.Find(input.TheMovie.MovieId) : input.TheMovie;

            if (isNew)
                db.Movies.Add(movie);
            else
            {
                db.Entry(movie).Property("Version").OriginalValue = input.TheMovie.Version;
                db.Entry(movie).State = System.Data.EntityState.Unchanged;


                // dirty this all the time even this is not changed by the user, 
                // so we have a simplified mechanism for concurrent update 
                // when the associated Genre(s) is modified
                db.Entry(movie).Property("MovieName").IsModified = true;
            }


            movie.Genres = movie.Genres ?? new List<Genre>();
            movie.Genres.Clear();
            foreach (int g in input.SelectedGenres)
            {
                // What is Entity Framework 4.1 analogous to NHibernate's session.Load<Genre>(g) ? My google-fu is failing me
                // db.Genres.Find(g) is not efficient
                movie.Genres.Add(db.Genres.Find(g));
                // Solution for db.Genres.Find inefficiency:
                // http://www.ienablemuch.com/2011/07/entity-framework-asnotracking-and.html
            }

            // http://www.joe-stevens.com/2010/02/17/asp-net-mvc-using-controller-updatemodel-when-using-a-viewmodel/
            // null will get all properties under TheMovie object, the Version is excluded                 
            UpdateModel(movie, "TheMovie", includeProperties: null, excludeProperties: new string[] { "Version" });



            db.SaveChanges();

            db.Entry(movie).Reload();
            input.TheMovie.Version = movie.Version;


            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 (DbUpdateConcurrencyException)
        {
            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);
}

The models. Note, the following models has a circular reference, this sort of thing is not possible on a platform like RDBMS. That's why platforms that can describe many-to-many scenario in an elegant manner and can be conveyed intuitively to users, is very appealing to developers who want to model the problem domain in terms of user's lingo, and fortunately one of those platforms is your favorite language(mine happens to be C#), C# don't have problems with circular references . Users don't know the fuss what's a third table is all about. Don't tell them about a third table, that will be a leaky abstraction. It's for you(developer) to infer if a third table is warranted; an example, a Genre happen to have many Movies, and a Movie can have many Genres too.

public class Genre
{
 [Key]
 public virtual int GenreId { get; set; }
 public virtual string GenreName { get; set; }

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


public class Movie
{
 [Key]
 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; }
 
 [ConcurrencyCheck]
 public virtual byte[] Version { get; set; }

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


ORM mapping. We have three tables, namely Movie, Genre, and MovieAssocGenre.
public class TheMovieContext : DbContext
{
 public DbSet<Movie> Movies { get; set; }
 public DbSet<Genre> Genres { get; set; }


 protected override void OnModelCreating(DbModelBuilder modelBuilder)
 {           
  modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

  modelBuilder.Entity<Movie>().HasMany(x => x.Genres).WithMany(x => x.Movies).Map(x =>
   {
    x.ToTable("MovieAssocGenre");
    x.MapLeftKey("MovieId");
    x.MapRightKey("GenreId");
   });

  modelBuilder.Entity<Genre>().HasMany(x => x.Movies).WithMany(x => x.Genres).Map(x =>
  {
   x.ToTable("MovieAssocGenre");
   x.MapLeftKey("GenreId");
   x.MapRightKey("MovieId");
  });
  
  
  base.OnModelCreating(modelBuilder);
 }


}


DDL:
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 complete code from http://code.google.com/p/ef-aspnet-mvc-checkbox-list-demo/downloads/list

Solution found on db.Genres.Find always hitting the database: http://www.ienablemuch.com/2011/07/entity-framework-asnotracking-and.html

Sample output

NHibernate equivalent of Entity Framework's MapLeftKey and MapRightKey

Fluent NHibernate equivalent of Entity Framework's MapLeftKey and MapRightKey


.Override<Movie>(x => x.HasManyToMany(y => y.Genres).Table("MovieAssocGenre")        
    .ParentKeyColumn("z_MovieId").ChildKeyColumn("z_GenreId"))
.Override<Genre>(x => x.HasManyToMany(y => y.Movies).Table("MovieAssocGenre")
    .ParentKeyColumn("z_GenreId").ChildKeyColumn("z_MovieId"))


And EntityFramework equivalent of NHibernate's ParentKeyColumn and ChildKeyColumn

// You can either do this:
modelBuilder.Entity<Movie>().HasMany(x => x.Genres).WithMany(x => x.Movies).Map(x =>
 {
  x.ToTable("MovieAssocGenre");
  x.MapLeftKey("z_MovieId");
  x.MapRightKey("z_GenreId");
 });

// Or this:
modelBuilder.Entity<Genre>().HasMany(x => x.Movies).WithMany(x => x.Genres).Map(x =>
 {
  x.ToTable("MovieAssocGenre");
  x.MapLeftKey("z_GenreId");
  x.MapRightKey("z_MovieId");
 });


// In Entity Framework, there's no need to do both statements, just choose one. Any of those statement fully describes the many-to-many on both ends

// Whereas in NHibernate, you need to do both

The DDL:

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

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,
z_MovieId int not null references Movie(MovieId),
z_GenreId int not null references Genre(GenreId),
constraint uk_MovieAssocGenre unique(z_MovieId, z_GenreId)
);

NHibernate concurrency checking on ASP.NET MVC

NHibernate concurrency handling on ASP.NET MVC is pretty boring, no need to introduce any work-around code :-)

Contrast with Entity Framework which need work-around(connected mode simulation) when it is working in disconnected state, NHibernate don't need any of that. NHibernate concurrent update is automatically handled behind the scene, no need to introduce any code to make NHibernate be informed which column(rowversion) is not needed on UPDATE's SET clause, what you just need is to inform NHibernate which property is your rowversion column, and no need to simulate connected mode.


Here's your typical user-friendly(and programmer-friendly if I may add) concurrent update handling with NHibernate:

[HttpPost]
public ActionResult Save(Song song)
{
    using (var s = Mapper.GetSessionFactory().OpenSession())
    using (var tx = s.BeginTransaction())
    {
        try
        {
            s.SaveOrUpdate(song); // SaveOrUpdate automatically fetches the primary key and row version

            tx.Commit();


            ModelState.Remove("SongId");
            // no need to issue ModelState.Remove on Version property; with byte array, ASP.NET MVC always get its value from the model, not from ModelState
            // ModelState.Remove("Version") 

        }
        catch(StaleObjectStateException ex)
        {                       
            s.Evict(song);
            var dbValues = s.Get<Song>(song.SongId);
            var userValues = song;
           


            if (dbValues == null)
            {
                ModelState.AddModelError("", "This record you are attempting to save is already deleted by other user");
                return;
            }

           
            if (dbValues.SongName != userValues.SongName)
                ModelState.AddModelError("SongName", "Current value made by other user: " + dbValues.SongName);

            
            if (dbValues.AlbumName != userValues.AlbumName)
                ModelState.AddModelError("AlbumName", "Current value made by other user: " + dbValues.AlbumName);

            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");

        }
        catch (Exception ex)
        {
            ModelState.AddModelError("", ex.Message);
        }


        return View("Input", song);
    }
}

Get the DDL at http://www.ienablemuch.com/2011/07/entity-framework-concurrency-checking.html

Complete demo code: http://code.google.com/p/nh-concurrency-handling-on-aspnet-mvc-demo/downloads/list

Sample output:

Entity Framework concurrency checking on ASP.NET MVC (UpdateModel is the only sane approach when you want to use concurrency checking on EF)

UPDATE August 2, 2011

Perhaps I'm too harsh on Entity Framework, we can simplify concurrency checking if we used Timestamp attribute. ConcurrencyCheck doesn't work well on detached(e.g. web) scenario, work-arounds is needed. On the updated code, we change ConcurrencyCheck attribute to Timestamp attribute

Though for complex object persisting (e.g. many-to-many. http://www.ienablemuch.com/2011/07/using-checkbox-list-on-aspnet-mvc-with_16.html), even we used Timestamp attribute, we still need OriginalValue work-around. Directly persisting a detached entity which has children entities would result to duplicate children entities, as EF has no knowledge that the old children entities should be discarded; so for this type of data persistence we need to to re-simulate attached scenario, that means we directly work on entities that come from EF, and that means we need to inform EF the entity's original rowversion value if we want EF to handle concurrent updates well. To prevent duplicate children entities on an entity, we need to clear the attached entity's children; on our checkbox list demo, that is movie.Genres.Clear(). Don't worry, clearing the children entities is efficient, it doesn't actually delete the children rows on database, try to check the Sql Server profiler :-)

public class Song
{
    [Key]
    public int SongId { get; set; }
    public string SongName { get; set; }
    public string AlbumName { get; set; }

    [Timestamp]
    public virtual byte[] Version { get; set; }
}


Here's the updated Save method:

[HttpPost]
public ActionResult Save(Song song)
{
    SaveCommon(song);

    return View("Input", song); // song is the model
}



private void SaveCommon(Song song)
{
    using (var db = new TheMusicContext())
    {
        try
        {
            if (song.SongId == 0)
                db.Songs.Add(song);                                                
            else
                db.Entry(song).State = System.Data.EntityState.Modified;

            db.SaveChanges();

            ModelState.Remove("SongId"); // to force ASP.NET MVC to get the SongId value from the model, not from ModelState
        }
        catch (DbUpdateConcurrencyException ex)
        {
            var entry = ex.Entries.Single();


            var dbValues = entry.GetDatabaseValues();

            // already deleted
            if ( dbValues == null)
            {
                ModelState.AddModelError("", "This record you are attempting to save is already deleted by other user");
                return;
            }

            
            Song songDbValues = (Song) dbValues.ToObject();
            Song userValues = (Song) entry.Entity;



            if (songDbValues.SongName != userValues.SongName)
                ModelState.AddModelError("SongName", "Current value made by other user: " + songDbValues.SongName);

            if (songDbValues.AlbumName != userValues.AlbumName)
                ModelState.AddModelError("AlbumName", "Current value made by other user: " + songDbValues.AlbumName);

            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");
        }
        catch (Exception ex)
        {
            ModelState.AddModelError("", ex.Message + "\n" + ex.StackTrace + ex.InnerException.Message);
        }
        
      
    }

               
}//SaveCommon

Here's the updated Delete method:

public ActionResult Delete(int id, byte[] Version)
{
    using (var db = new TheMusicContext())
    {

        var stub = new Song { SongId = id, Version = Version };
        if (Request.HttpMethod == "POST")
        {
            try
            {                        
                db.Entry(stub).State = System.Data.EntityState.Deleted;
                db.SaveChanges();
                return RedirectToAction("Index");
            }
            catch (DbUpdateConcurrencyException ex)
            {
                var entry = ex.Entries.Single();
                var dbValues = entry.GetDatabaseValues();


                // already deleted
                if (dbValues == null)
                    return RedirectToAction("Index");
                                 
                ModelState.AddModelError("", "The record you are attempting to delete was modified by other user first. Do you still want to delete this record?");
                Song songDbValues = (Song)dbValues.ToObject();       

                return View(songDbValues);
            }                    
        }
        else
        {
            var songToDelete = db.Songs.Find(id);
            return View(songToDelete);
        }
    }
}


Get the updated code here: http://code.google.com/p/ef-concurrency-on-aspnet-mvc-demo/downloads/list




**STALE post. read the recommended procedure above**

Entity Framework concurrency handling doesn't work out-of-the-box if you are using it in disconnected scenario(as typified by web apps). Contrast to this(connected): http://www.ienablemuch.com/2011/05/entity-frameworks-concurrency-handling.html

When you submitted(POST) a form, you must simulate your entity like it was just first loaded from the GET method(e.g. http://Song/Edit/1), essentially you need to simulate connected mode so EF can compare if the record's rowversion have changed since it was first loaded. On the code below, that simulation is in line #22 and 23


ASP.NET's MVC model binding doesn't seem to be of much use on Entity Framework. Entity Framework cannot work with the posted model directly. Contrast that with NHibernate http://www.ienablemuch.com/2011/07/nhibernate-concurrency-checking-on.html

[HttpPost]
public ActionResult Save(Song song)
{
    using (var db = new TheMusicContext())
    {
        try
        {
            bool isNew = song.SongId == 0;

            var songToDb = !isNew ? db.Songs.Find(song.SongId) : song;

            if (songToDb == null) throw new Exception("This record you are attempting to save is already deleted by other user");


            if (isNew)
                db.Songs.Add(songToDb); // Primary key is automatically populated
            else
            {
                // To facilitate concurrency checks, do these two lines.
                // When EF perform an update, it compares the DB value from OriginalValue.
                // Leaky abstractions rear its ugly head when you want to do concurrency checks on EF ;-)
                db.Entry(songToDb).Property("Version").OriginalValue = song.Version;
                db.Entry(songToDb).State = System.Data.EntityState.Unchanged;

                // There's too much implicitness here, UpdateModel get values from Request.Form/Request.QueryString, Model Binding (variable song) isn't of much use here.
                // But UpdateModel is the only sane approach when you want to use concurrency checking on EF,
                // as it has array parameter, to which you can selectively pass properties via Linq-to-objects
                // Automapper is another approach? http://automapper.codeplex.com/
                // Is Model Binding useless on EF?
                // One thing for sure, Model Binding is not useless on NHibernate :-)
                // http://www.ienablemuch.com/2011/07/nhibernate-concurrency-checking-on.html
                UpdateModel(songToDb, 
                      typeof(Song).GetProperties()
                      .Where(x => x.Name != "Version")
                      .Select(x => x.Name).ToArray());
            }


            db.SaveChanges();

            db.Entry(songToDb).Reload(); // Fetch the Version column from the database
            song.Version = songToDb.Version;


            ModelState.Remove("SongId"); // to force ASP.NET to get the SongId value from the model, not from ModelState

            // no need to issue ModelState.Remove on Version property; with byte array, ASP.NET MVC always get its value from the model, not from ModelState
            // ModelState.Remove("Version") 
        }
        catch (DbUpdateConcurrencyException ex)
        {
            var entry = ex.Entries.Single();
            Song dbValues = (Song)entry.GetDatabaseValues().ToObject();
            Song userValues = (Song)entry.Entity;



            if (dbValues.SongName != userValues.SongName)
                ModelState.AddModelError("SongName", "Current value made by other user: " + dbValues.SongName);

            if (dbValues.AlbumName != userValues.AlbumName)
                ModelState.AddModelError("AlbumName", "Current value made by other user: " + dbValues.AlbumName);

            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");
        }
        catch (Exception ex)
        {
            ModelState.AddModelError("", ex.Message);
        }
        

        return View("Input", song); // song is the model

    }       
}


Model:
public class Song
{
    [Key]
    public int SongId { get; set; }
    public string SongName { get; set; }
    public string AlbumName { get; set; }

    [ConcurrencyCheck]
    public virtual byte[] Version { get; set; }
}

The View:
@model EfConcurrencyOnAspNetMvc.Models.Song

@{
    ViewBag.Title = "Input";
}

<h2>Input</h2>

<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)

    @Html.HiddenFor(x => x.SongId)
    @Html.HiddenFor(x => x.Version)
    <fieldset>
        <legend>Song</legend>

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

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

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

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

The DDL:
create table Song
(
SongId int identity(1,1) not null primary key,
SongName varchar(50) not null,
AlbumName varchar(50) not null,
Version rowversion not null
);

Complete demo code: http://code.google.com/p/ef-concurrency-on-aspnet-mvc-demo/downloads/list


Sample Output:

Monday, July 11, 2011

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
}

Sunday, July 10, 2011

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: