Linq nuances with WCF Data Service

Don't assume all Linq providers are the same, Linq provider for Entity Framework is different from Linq provider for WCF Data Service

public static IQueryable<T> LimitAndOffset<T>(this IQueryable<T> q,
                    int pageSize, int pageOffset)
{
    // while this Linq works on both Entity Framework and WCF Data Services...
    // return q.Skip((pageOffset - 1) * pageSize).Take(pageSize);
    
    // ...this Linq doesn't work on WCF Data Service
    return q.Take(pageSize).Skip((pageOffset - 1) * pageSize);
}

I answered a colleague that Skip and Take's order doesn't matter (i.e. can call Take then Skip also) because it is deferredly executed, felt a little stupid for naturally assuming it applies to all Linq Providers :-) Linq for WCF Data Services doesn't allow Take then Skip. Don't assume anything when it comes to Linq.

Lesson learned, not all Linq providers are created equal. Lest I forgot, I already encountered one of many Linq's nuances a long time ago, example: http://www.ienablemuch.com/2010/12/performing-order-by-on-distinct-on-linq.html

I should have known better :-)

A null was returned after calling the 'GetService' method on a store provider instance of type 'System.Data.SQLite.SQLiteFactory'

If you encounter this error:

A null was returned after calling the 'GetService' method on a store provider instance of type 'System.Data.SQLite.SQLiteFactory'. The store provider might not be functioning correctly.

Add System.Data.SQLite.Linq.dll to your project's References

The type or namespace name 'ModelBuilder' does not exist

If you encountered this error:

The type or namespace name 'ModelBuilder' does not exist in the namespace 'System.Data.Entity.ModelConfiguration' (are you missing an assembly reference?)

Just change...

System.Data.Entity.ModelConfiguration.ModelBuilder modelBuilder

...to:

System.Data.Entity.DbModelBuilder modelBuilder

ASP.NET MVC Helper for jQuery Ajax ComboBox

View's code:

<div class="editor-label">
 @Html.LabelFor(model => model.CategoryId, "Category")
</div>
<div class="editor-field">
 <table>
 <tr>                    
  <td>@Html.AjaxComboBoxFor(model => model.CategoryId, "/Category/Lookup", "/Category/Caption")</td>
  <td>@Html.ValidationMessageFor(model => model.CategoryId)</td>
 </tr>
 </table>
</div>

Controller's code:

[HttpPost]
public JsonResult Lookup(string q_word, string primary_key, int per_page, int page_num)
{


 using (var svc = SessionFactoryBuilder.GetSessionFactory().OpenSession())
 {

  var FilteredCategory = svc.Query<Category>()
        .Where(x => q_word == "" || x.CategoryName.Contains(q_word));


  var PagedFilter = FilteredCategory.OrderBy(x => x.CategoryName)
        .LimitAndOffset(per_page, page_num)
        .ToList();

  return Json(
   new
   {
    cnt = FilteredCategory.Count()

    ,primary_key = PagedFilter.Select(x => x.CategoryId)

    ,candidate = PagedFilter.Select(x => x.CategoryName)

    ,cnt_page = PagedFilter.Count()

   }
   );

 }//using
}//List


[HttpPost]
public string Caption(string q_word)
{
 using (var svc = SessionFactoryBuilder.GetSessionFactory().OpenSession())
 {
  if (string.IsNullOrEmpty(q_word))
   return "";
  else
   return 
    svc.Query<Category>()
    .Where(x => x.CategoryId == int.Parse(q_word))
    .Select(x => x.CategoryName)
    .SingleOrDefault();
 }

}

Please do note that I modify the jQuery Ajax ComboBox's code a bit, so as to make the combobox's emitted name be compatible to ASP.NET MVC's Model

Get the code from SVN !

Or download it from google code

Boilerplate code for Fluent NHibernate

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate;
using FluentNHibernate.Cfg.Db;
using FluentNHibernate.Cfg;
using FluentNHibernate.Conventions.Helpers;

namespace FnhNavigationTesting
{
    public static class SessionFactoryBuilder
    {
        static ISessionFactory _sf = null;
        public static ISessionFactory GetSessionFactory()
        {
            // Building SessionFactory is costly, should be done only once, making the backing variable static shall prevent creation of multiple factory
            try
            {
                if (_sf != null) return _sf;

                _sf = 
                    Fluently.Configure()
                    .Database(
                        MsSqlConfiguration.MsSql2008
                        .ConnectionString(
                            @"Data Source=localhost;Initial Catalog=OkSystem;User id=sa;Password=opensesame"))
                    .Mappings( x => x.FluentMappings.AddFromAssemblyOf<Program>())
                    .BuildSessionFactory();

                return _sf;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.InnerException.Message + "\n" + ex.InnerException.StackTrace);
            }
        }
    }//SessionFactoryBuilder
}