Friday, December 28, 2012

Anti-If Campaign

Thursday, December 27, 2012

Self-hosting ServiceStack serving razor'd HTML

In this walkthrough, I'll show you how to setup a self-hosting(i.e. this doesn't use ASP.NET nor ASP.NET MVC) ServiceStack that serves HTML. The HTML can be made dynamic through razor

21 steps


1. Start Visual Studio in Administrator mode

2. Create a new Console Application project, name your project BillionaireServiceStackRazorSelfHosting

3. Change the project's Target framework to .NET Framework 4 (from the default .NET Framework 4 Client Profile)

4. Add ServiceStack.Razor to your project via Nuget

5. Replace your app.config with this content:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <sectionGroup name="system.web.webPages.razor" type="System.Web.WebPages.Razor.Configuration.RazorWebSectionGroup, System.Web.WebPages.Razor, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
      <section name="host" type="System.Web.WebPages.Razor.Configuration.HostSection, System.Web.WebPages.Razor, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" />
      <section name="pages" type="System.Web.WebPages.Razor.Configuration.RazorPagesSection, System.Web.WebPages.Razor, Version=1.0.0.0, Culture=neutral," requirePermission="false" />
    </sectionGroup>
  </configSections>
  <system.web>
    <httpHandlers>
      <add path="*" type="ServiceStack.WebHost.Endpoints.ServiceStackHttpHandlerFactory, ServiceStack" verb="*" />
    </httpHandlers>
    <compilation debug="true">
      <assemblies>
        <add assembly="System.Web.WebPages.Razor, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
      </assemblies>
      <buildProviders>
        <add extension=".cshtml" type="ServiceStack.Razor.CSharpRazorBuildProvider, ServiceStack.Razor" />
      </buildProviders>
    </compilation>
  </system.web>
  <!-- Required for IIS 7.0 -->
  <system.webServer>
    <handlers>
      <add path="*" name="ServiceStack.Factory" type="ServiceStack.WebHost.Endpoints.ServiceStackHttpHandlerFactory, ServiceStack" verb="*" preCondition="integratedMode" resourceType="Unspecified" allowPathInfo="true" />
    </handlers>
  </system.webServer>
  <appSettings>
    <add key="webPages:Enabled" value="false" />
    <add key="webpages:Version" value="2.0.0.0"/>
  </appSettings>
  <system.web.webPages.razor>
    <host factoryType="System.Web.Mvc.MvcWebRazorHostFactory, System.Web.Mvc, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
    <pages pageBaseType="ServiceStack.Razor.ViewPage">
      <namespaces>
        <add namespace="ServiceStack.Html" />
        <add namespace="ServiceStack.Razor" />
        <add namespace="ServiceStack.Text" />
        <add namespace="ServiceStack.OrmLite" />
        <add namespace="BillionaireServiceStackRazorSelfHosting" />
      </namespaces>
    </pages>
  </system.web.webPages.razor>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
  </startup>
</configuration>

6. Copy app.config to web.config, web.config enables intellisense for razor. The webpages:Version must be 2.0.0.0; if it is 1.0.0.0 razor intellisense will not work, saw the answer here: http://stackoverflow.com/questions/6133090/razor-intellisense-not-working-vs2010-sp1rel-mvc3-from-wpi-win7-x64/11780828#11780828


7. Create a new class named AppHost.cs in your project's root path:

//The entire C# code for the stand-alone RazorRockstars demo.
namespace BillionaireServiceStackRazorSelfHosting
{
    public class AppHost : ServiceStack.WebHost.Endpoints.AppHostHttpListenerBase
    {
     
        public AppHost() : base("Test Razor", typeof(AppHost).Assembly) { }

        public override void Configure(Funq.Container container)
        {
            ServiceStack.Logging.LogManager.LogFactory = new ServiceStack.Logging.Support.Logging.ConsoleLogFactory();

            Plugins.Add(new ServiceStack.Razor.RazorFormat());


            SetConfig(new ServiceStack.WebHost.Endpoints.EndpointHostConfig
            {
                CustomHttpHandlers = {
                    { System.Net.HttpStatusCode.NotFound, new ServiceStack.Razor.RazorHandler("/notfound") }
                }
            });
        }
    }    
}


8. Replace your Program.cs with this:

using System;


namespace BillionaireServiceStackRazorSelfHosting
{
    class Program
    {
        static void Main(string[] args)
        {
            ServiceStack.Logging.LogManager.LogFactory = new ServiceStack.Logging.Support.Logging.ConsoleLogFactory();

            var appHost = new AppHost();
            appHost.Init();
            appHost.Start("http://*:2012/");

            var proc = new System.Diagnostics.Process ();
            proc.StartInfo.UseShellExecute = true;
            proc.StartInfo.FileName = "http://localhost:2012/";
            proc.Start ();


            Console.WriteLine("\n\nListening on http://*:2012/..");
            Console.WriteLine("Type Ctrl+C to quit..");
            System.Threading.Thread.Sleep(System.Threading.Timeout.Infinite);
        }
    }
}

9. If you'll compile your code now, you'll receive an error that you must add System.Web to your project:


10. Add System.Web to your project's Reference:


11. Your code shall compile now:


12. To test if things are working accordingly, run your code, you shall see the following:




13. Check http://localhost:2012 on your browser. You shall see the following:




14. Then create a new page named Great.cshtml on your site's root path


15. Put this code in Great.cshtml:

Teach your kid to start counting from zero:

@for (int i = 0; i < 10; i++)
{
    <p>@i</p>
}

16. If you'll run your code now, you'll receive an error:



17. You must set Great.cshtml's Copy to Output Directory property to Copy if newer


18. Your code will run now:



19. To use strongly-typed model for your razor page, use inherits directive. Create a model first, it must be public and its Copy to Output Directory property must be set to Copy if newer too

namespace BillionaireServiceStackRazorSelfHosting
{
    public class CountRange
    {
        public int StartNumber { get; set; }
        public int EndNumber { get; set; }
    }
}

20. Then change Great.cshtml to this:

@inherits ViewPage<CountRange>


Start: @Model.StartNumber <br />
End: @Model.EndNumber


@for (int i = Model.StartNumber; i <= Model.EndNumber; i++)
{
    <p>@i</p>
}

21. Run your code, and browse this url: http://localhost:2012/Great?StartNumber=8&EndNumber=12. This is the output:



That's it, web serving is never been this lightweight!


Another advantage of serving Razor this way(as opposed to ASP.NET MVC's Areas) is you can structure your web pages in the most logical way you wanted them be, you could place Razor pages on any directory and on any level, ServiceStack.Razor won't have a problem serving them.


ASP.NET MVC even with its Areas, feels very constraining. In fact, afaik, the main intent of ASP.NET MVC's Areas is not for stashing related controllers together; Area's main intent is to group the related controller+views(and perhaps plus some controller-specific models) so an application's module could be structured and conceived as one cohesive MVC unit. Perhaps Area's Controllers folder should be named Controller, i.e. in singular form ツ


The ServiceStack solution you've made on Visual Studio can be opened on MonoDevelop, it works out-of-the-box. I tried to open and run the same Visual Studio .sln on MonoDevelop on my main development OS (Mac OS X), It Just Works™


For more info on ServiceStack Razor head to http://razor.servicestack.net/


Happy Computing! ツ





A minor snag, though razor's intellisense is working by following the steps above, I can't figure out how to make the inherits directive be detected properly by Visual Studio. Consequence is, Visual Studio won't be able to give you intellisense for the Model; in fact, Visual Studio will put red squiggly line below the Model property. Things are still working though even Visual Studio put red squiggly line below them




Posted a question on stackoverflow, hope someone could shed some light on ServiceStack razor's intellisense on model: http://stackoverflow.com/questions/14043364/servicestack-razor-inherits-directive-has-no-intellisense


GitHub: https://github.com/MichaelBuen/DemoServiceStackRazor

Monday, December 24, 2012

Making your application multilingual with NHibernate. Laugh in the face of change

An existing application that become a success and later needed be bolted with multilingual support is where NHibernate adaptability on brownfield projects shines. How well does the application that uses NHibernate can adapt to this change? It handles it seamlessly, in fact there's virtually no changes in your application code needed be made. The localization on both retrieving and saving are being done automatically


Changes needed be made to your domain classes to support multiple languages? Zero, zilch, nada


public class Order
{
    public virtual int OrderId { get; set; }
    public virtual Product Product { get; set; }
    public virtual DateTime OrderDateTaken { get; set; }
    public virtual int Qty { get; set; }
    public virtual string EmailAccount { get; set; }
}

public class Product
{
    public virtual int ProductId { get; set; }
    
    public virtual string ProductName { get; set; }
    public virtual string ProductDescription { get; set; }      

    public virtual int YearIntroduced { get; set; }

    public virtual IList<Order> Orders { get; set; }
}     


Changes needed be made to your persistence code to support multiple languages? Zero, zilch, nada

var pz = 
        new Product
        {
            ProductName = "PC",
            ProductDescription = "Personal Computer",
            YearIntroduced = 1981
        };
    s.Merge(pz);
    s.Flush();


Changes needed be made to your object retrieval code to support multiple languages? Zero, zilch, nada
var ps = 
        from p in s.Query<Product>()
        orderby p.ProductDescription descending
        select p;
        
    
    
    foreach (var p in ps)
    {
        Console.WriteLine(
             "Name: {0}\nDescription: {1}\nYear Introduced: {2}\n\n", 
             p.ProductName, p.ProductDescription, p.YearIntroduced);
    }


When we get to the bottom of things, multilingual support is something that should not leak to your domain classes. This is where a capable ORM shines, it insulates your domain classes against infrastructure changes needed be made, everything just works automagically. The infrastructure to support multiple languages might be relational, but the domain classes don't have to reflect the underlying infrastructure. Object-wise, your domain class is still the same cohesive and atomic object your app knows. This is what Object-Relational Mapping is all about. Your object truly mimics the real-world object/entity, not a mere one-to-one mapping of object to relational. The beauty of abstraction


To cut to the chase, these are the infrastructure changes needed be made to enable multilingual support for your app.

  1. Move language-specific fields to another table
  2. Create a function that fetches the language-specific information
  3. Adjust the mapping
  4. Set the language upon database connection


1. Move language-specific fields to another table:
create table Product_Translation
(   
    ProductId int not null references Product(ProductId),
    LanguageCode varchar(5) not null,
    ProductName nvarchar(max) not null,
    ProductDescription nvarchar(max) not null,
    constraint pk_Product_Translation primary key(ProductId, LanguageCode)
);



Then move the language-specific content of your Product table to Product_Translation table.
insert into Product_Translation(ProductId,LanguageCode,ProductName,ProductDescription)
select ProductId,'en',ProductName,ProductDescription from Product;


Then drop the language-specific fields from the main table, your table should not have any language-specific fields left in it, e.g.
create table Product
(
    ProductId int identity(1,1) not null primary key,
    YearIntroduced int not null
);



2. Create a function that fetches the language-specific information:


create function GetProductTranslation(@LanguageCode varchar(5))
returns table
as
    return
        with a as
        (
            select 
                
                TheRank = 
                    rank() over(
                        partition by ProductId 
                
                        order by    
                        case LanguageCode 
                        when @LanguageCode then 1
                        when 'en' then 2 -- fallback language
                        else 3
                        end
                    ),

                ProductId, ProductName, ProductDescription

            from Product_Translation t
        )
        select ProductId, ProductName, ProductDescription 
        from a 
        where TheRank = 1;       
go




To verify if things are working accordingly:
select p.ProductId, t.ProductName, t.ProductDescription, p.YearIntroduced
from Product p
join dbo.GetProductTranslation('en') t
on t.ProductId = p.ProductId


3. Adjust the mapping

Do note that the parameter substitution on SqlInsert and SqlUpdate is order-dependent. If you declare properties in your class in this order: ProductName, ProductDescription; the ProductName will be passed to the first parameter, and the ProductDescription to the second parameter, the last parameter is where NHibernate passes your entities joining key.

public class ProductMapping : NHibernate.Mapping.ByCode.Conformist.ClassMapping<Product>
{
    string updateCommand =
@"
merge Product_Translation as target
using (values({0},?, ?, ?)) as source(LanguageCode, ProductName, ProductDescription, ProductId)
on source.ProductId = target.ProductId and source.LanguageCode = target.LanguageCode

when matched then 
    update set ProductName = source.ProductName, ProductDescription = source.ProductDescription
when not matched then 
    insert (LanguageCode, ProductName, ProductDescription, ProductId) 
    values(LanguageCode, ProductName, ProductDescription, ProductId);";


    public ProductMapping()
    {

        Table("Product");

        Id(x => x.ProductId, map =>
        {
            map.Column("ProductId");
            map.Generator(NHibernate.Mapping.ByCode.Generators.Identity);
        });

        Property(x => x.YearIntroduced);


        string sessionLanguage = "(select cast(CONTEXT_INFO() as varchar(5)))";

        Join("dbo.GetProductTranslation(:lf.LanguageCode)", j =>
        {
            j.Inverse(false);
            j.Fetch(NHibernate.Mapping.ByCode.FetchKind.Join);
            j.Key(k => k.Column("ProductId"));

            j.Property(p => p.ProductName);
            j.Property(p => p.ProductDescription);

            j.SqlInsert(string.Format("insert into Product_Translation(LanguageCode, ProductName, ProductDescription, ProductId) values({0}, ?, ?, ?)", sessionLanguage));
            j.SqlUpdate(string.Format(updateCommand, sessionLanguage));
        }); // join

        Bag<Order>(
            list => list.Orders,
            rel => rel.Key(y => y.Column("ProductId")),
            relType => relType.OneToMany());
    }

} // product mapping

Point of interest on the mapping is the dbo.GetProductTranslation function, it receives its values from the filter. Which is defined while building the session factory:

var filterDef = 
       new NHibernate.Engine.FilterDefinition(
            "lf", null, 
            new Dictionary<string, NHibernate.Type.IType> 
            { 
                { "LanguageCode", NHibernateUtil.String }
            }, useManyToOne: false);
        cfg.AddFilterDefinition(filterDef);



You'll notice too that the SqlInsert and SqlUpdate doesn't use filters, this is a limitation of NHibernate filters, filters are applied on functions and conditions only. For that limitation, we'll just set Sql Server's CONTEXT_INFO to desired language upon database connection. For SqlUpdate command, we use Sql Server's merge statement, if the ProductId+LanguageCode is existing on Product_Translation it will update the existing row; if not, then it will insert it.



4. Set the language upon database connection
using (var s = Mapper.GetSessionFactory().OpenSession())
using (var tx = s.BeginTransaction().SetLanguage(s,"zh-CHS"))    
{   
     // Your beautiful code goes here        
}


public static class TransactionHelper
{
    public static NHibernate.ITransaction SetLanguage(this NHibernate.ITransaction tx, NHibernate.ISession session, string languageCode)
    {            
        var cmd = session.Connection.CreateCommand();
        tx.Enlist(cmd);
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.CommandText = "SetLanguage";
        var prm = cmd.CreateParameter();
        prm.ParameterName = "@LanguageCode";
        prm.Value = languageCode;
        cmd.Parameters.Add(prm);
        cmd.ExecuteNonQuery();

        session.EnableFilter("lf").SetParameter("LanguageCode", languageCode);

        return tx;
    }
}

create procedure SetLanguage(@LanguageCode varchar(5)) as
begin
    declare @binvar varbinary(128);
    set @binvar = cast(@LanguageCode as varbinary(128));
    set context_info @binvar;
end;

That's it! Your application will do business as usual, without it being aware that the infrastructure underneath was somehow changed radically.

Nothing shall change on how the application get and persist objects.


Take that curmudgeoned ADO.NET developers!


Happy Coding! ツ


One of C#'s selling points: One-stop-shop. Expand to see the full code:

C# code:
using System;
using System.Collections.Generic;


namespace BillionDollarBusinessApp
{    
    using BusinessEntities;

    // If we are using repository pattern, these doesn't need to turn up here:
    using TheMapper;
    using NHibernate.Linq;    

    class Program
    {
        static void Main(string[] args) 
        {

            using (var s = Mapper.GetSessionFactory().OpenSession())
            using (var tx = s.BeginTransaction().SetLanguage(s, "zh"))
            {

                Action showProducts = delegate
                {
                    foreach (var product in s.Query<Product>())
                    {
                        Console.WriteLine("Product Name: {0}\nDescription: {1}\nYear Introduced: {2}\n", product.ProductName, product.ProductDescription, product.YearIntroduced);
                    }
                };

                Console.WriteLine("***Prior to localizing Apple name***\n");
                showProducts();

                var prodApple = s.Get<Product>(2);
                prodApple.ProductName = "苹果";
                s.Merge(prodApple);

                Console.WriteLine("***After localizing Apple name***\n");
                showProducts();

                tx.Commit();
            }

            Console.ReadKey();
        }
    }

}

namespace TheMapper
{
    using NHibernate.Cfg;
    using NHibernate.Linq;

    using BusinessEntities;

    public static class TransactionHelper
    {
        public static NHibernate.ITransaction SetLanguage(this NHibernate.ITransaction tx, NHibernate.ISession session, string languageCode)
        {
            var cmd = session.Connection.CreateCommand();
            tx.Enlist(cmd);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "SetLanguage";
            var prm = cmd.CreateParameter();
            prm.ParameterName = "@LanguageCode";
            prm.Value = languageCode;
            cmd.Parameters.Add(prm);
            cmd.ExecuteNonQuery();

            session.EnableFilter("lf").SetParameter("LanguageCode", languageCode);

            return tx;
        }
    }

    public static class Mapper
    {
        private static NHibernate.ISessionFactory _isf = null;
        public static NHibernate.ISessionFactory GetSessionFactory()
        {
            var cfg = new NHibernate.Cfg.Configuration();
            var mapper = new NHibernate.Mapping.ByCode.ModelMapper();
            mapper.AddMappings(new[] { typeof(ProductMapping), typeof(OrderMapping) });

            cfg.DataBaseIntegration(c =>
                {
                    c.Driver<NHibernate.Driver.Sql2008ClientDriver>();
                    c.Dialect<NHibernate.Dialect.MsSql2008Dialect>();
                    c.ConnectionString = "Server=localhost; Database=TheLocalized; Trusted_Connection=true; MultipleActiveResultSets=true";
                    // c.LogSqlInConsole = true;
                });

            NHibernate.Cfg.MappingSchema.HbmMapping domainMapping = mapper.CompileMappingForAllExplicitlyAddedEntities();
            cfg.AddMapping(domainMapping);

            var filterDef = new NHibernate.Engine.FilterDefinition(
                "lf", null,
                new Dictionary<string, NHibernate.Type.IType> 
                { 
                    { "LanguageCode", NHibernate.NHibernateUtil.String }
                }, useManyToOne: false);
            cfg.AddFilterDefinition(filterDef);

            NHibernate.ISessionFactory sessionFactory = cfg.BuildSessionFactory();



            _isf = sessionFactory;

            return _isf;
        }

    }


    public class ProductMapping : NHibernate.Mapping.ByCode.Conformist.ClassMapping<Product>
    {
        string updateCommand =
@"
merge Product_Translation as target
using (values({0},?, ?, ?)) as source(LanguageCode, ProductName, ProductDescription, ProductId)
on source.ProductId = target.ProductId and source.LanguageCode = target.LanguageCode

when matched then 
    update set ProductName = source.ProductName, ProductDescription = source.ProductDescription
when not matched then 
    insert (LanguageCode, ProductName, ProductDescription, ProductId)   
    values(LanguageCode, ProductName, ProductDescription, ProductId);";


        public ProductMapping()
        {
            Table("Product");

            Id(x => x.ProductId, map =>
            {
                map.Column("ProductId");
                map.Generator(NHibernate.Mapping.ByCode.Generators.Identity);
            });

            Property(x => x.YearIntroduced);

            string sessionLanguage = "convert(nvarchar, substring(context_info(), 5, convert(int, substring(context_info(), 1, 4)) )  )";

            Join("dbo.GetProductTranslation(:lf.LanguageCode)", j =>
            {
                j.Inverse(false);
                j.Fetch(NHibernate.Mapping.ByCode.FetchKind.Join);
                j.Key(k => k.Column("ProductId"));

                j.Property(p => p.ProductName);
                j.Property(p => p.ProductDescription);

                j.SqlInsert(string.Format("insert into Product_Translation(LanguageCode, ProductName, ProductDescription, ProductId) values({0}, ?, ?, ?)", sessionLanguage));
                j.SqlUpdate(string.Format(updateCommand, sessionLanguage));
            }); // join

            Bag<Order>(
                list => list.Orders,
                rel => rel.Key(y => y.Column("ProductId")),
                relType => relType.OneToMany());
        }

    } // product mapping


    public class OrderMapping : NHibernate.Mapping.ByCode.Conformist.ClassMapping<Order>
    {
        public OrderMapping()
        {
            Table("[Order]");
            Id(x => x.OrderId, map =>
            {
                map.Column("OrderId");
                map.Generator(NHibernate.Mapping.ByCode.Generators.Identity);
            });
            Property(x => x.Qty);
            Property(x => x.OrderDateTaken);
            Property(x => x.EmailAccount);

            ManyToOne(refr => refr.Product, m => m.Column("ProductId"));

        }
    } // order mapping


}

namespace BusinessEntities
{

    public class Product
    {
        public virtual int ProductId { get; set; }

        // SqlInsert's parameter should correspond to this order
        public virtual string ProductName { get; set; }
        public virtual string ProductDescription { get; set; }

        public virtual int YearIntroduced { get; set; }

        public virtual IList<Order> Orders { get; set; }
    }

    public class Order
    {
        public virtual int OrderId { get; set; }
        public virtual Product Product { get; set; }
        public virtual DateTime OrderDateTaken { get; set; }
        public virtual int Qty { get; set; }
        public virtual string EmailAccount { get; set; }
    }
}


Database:
/*
drop table [Order];
drop table Product_Translation;
drop table Product;

drop function GetProductTranslation;
drop procedure SetLanguage;
*/


create table Product
(
    ProductId int identity(1,1) not null primary key,
    YearIntroduced int not null
);  


create table Product_Translation
(   
    ProductId int not null references Product(ProductId),
    LanguageCode varchar(5) not null,
    ProductName nvarchar(max) not null,
    ProductDescription nvarchar(max) not null,
    constraint pk_Product_Translation primary key(ProductId, LanguageCode)
);



create table [Order]
(
OrderId int identity(1,1) not null primary key,
EmailAccount nvarchar(100) not null,
ProductId int not null references Product(ProductId),
Qty int not null,
OrderDateTaken datetime not null,
);



insert into Product(YearIntroduced) values(1981);
declare @Computer int = SCOPE_IDENTITY();
insert into Product_Translation(ProductId, LanguageCode, ProductName, ProductDescription) 
values(@Computer, 'en','PC', 'Personal Computer');
insert into Product_Translation(ProductId, LanguageCode, ProductName, ProductDescription) 
values(@Computer, 'zh', N'电脑', N'电脑');


insert into Product(YearIntroduced) values(1984);
declare @Apple int = SCOPE_IDENTITY();
insert into Product_Translation(ProductId, LanguageCode, ProductName, ProductDescription) 
values(@Apple, 'en', 'Apple', 'Truly Personal Computer');


insert into Product(YearIntroduced) values(1886);
declare @CocaCola int = SCOPE_IDENTITY();
insert into Product_Translation(ProductId, LanguageCode, ProductName, ProductDescription) 
values(@CocaCola, 'en', 'Coca Cola', 'Refreshing');
insert into Product_Translation(ProductId, LanguageCode, ProductName, ProductDescription) 
values(@CocaCola, 'zh', N'可口可乐', N'可口可乐');




insert into Product(YearIntroduced) values(1903);
declare @TsingTao int = SCOPE_IDENTITY();
insert into Product_Translation(ProductId, LanguageCode, ProductName, ProductDescription) 
values(@TsingTao, 'zh', N'青岛', N'青岛');


insert into [Order](EmailAccount, ProductId, OrderDateTaken, Qty) values('a@a.com', @Apple, CURRENT_TIMESTAMP, 3);

go

create function GetProductTranslation(@LanguageCode varchar(max))
returns table
as
    return
        with a as
        (
            select 
                
                TheRank = 
                    rank() over(
                        partition by ProductId 
                
                        order by    
                        case LanguageCode 
                        when @LanguageCode then 1
                        when 'en' then 2 -- fallback language
                        else 3
                        end
                    ),

                ProductId, ProductName, ProductDescription

            from Product_Translation t
        )
        select ProductId, ProductName, ProductDescription 
        from a where TheRank = 1;       
go

create procedure [dbo].[SetLanguage](@LanguageCode nvarchar(5)) as
begin
    declare @binvar varbinary(128);
    set @binvar = cast(datalength(@LanguageCode) as varbinary) + cast(@LanguageCode as varbinary);
    set context_info @binvar;
end;
    

/*


select *
from Product p
join dbo.GetProductTranslation('en') t
on t.ProductId = p.ProductId;



select *
from Product p
join dbo.GetProductTranslation('zh') t
on t.ProductId = p.ProductId;


select * from Product_Translation;

*/

Sunday, December 23, 2012

NHibernate's non-stringly-typed LEFT JOIN

As of the time of this writing (and since 2009), NHibernate Linq doesn't support DefaultIfEmpty(), which is a LEFT JOIN functionality. And as such, we won't be able to report entities with collections having count of zero, as that kind of report uses left join.



Given this domain class:

public class Product
    {
        public virtual int ProductId { get; set; }
        public virtual string ProductName { get; set; }
        public virtual string ProductDescription { get; set; }
        public virtual int YearIntroduced { get; set; }

        public virtual IList<Order> Orders { get; set; }
    }


The way to report product even with no orders:

Order orderAlias = null;
var results =
    s.QueryOver<Product>()
    .Left.JoinAlias(od => od.Orders, () => orderAlias)
    .SelectList(selection => 
        selection.SelectGroup(b => b.ProductId)
            .SelectGroup(b => b.ProductName)
        .SelectCount(b => orderAlias.OrderId))                    
    .List<object[]>()                    
    .Select(zz => new
    {
        ProductId = (int)zz[0],
        ProductName = (string)zz[1],
        OrderCount = (int)zz[2]
    });

foreach (var item in results)
{
    Console.WriteLine("{0} {1}", item.ProductName, item.OrderCount);
}


That doesn't materialize the product orders to List<Order>. That's efficient, it uses COUNT, LEFT JOIN, GROUP BY.

However, the ugly consequence of using IQueryOver (aside from IQueryOver approach is also ugly) is we won't be able to use the more mockable and unit-test-friendly IQueryable. We can use IQueryable even on in-memory objects which makes IQueryable amenable for unit-testing. IQueryOver API looks very convoluted as compared to Linq's API simplicity; to contrast, here's how it looks like if NHibernate's Linq supported DefaultIfEmpty:


var www = 
    from p in s.Query<Product>
    from o in p.Orders.DefaultIfEmpty()
    group o by new { p.ProductId, p.ProductName } into grp
    select new { grp.Key.ProductId, grp.Key.ProductName, OrderCount = grp.Sum(x => x.Product != null ? 1 : 0 ) };




Linq's flexibility

If you find incessant aliasing a bit too distracting...

var py =
   from p in s.Query<Product>()

   from o in p.Orders
   group o by new { p.ProductId } into grp
   select new { grp.Key.ProductId, Count = grp.Count() };


...you can do it with lambda approach:

var py =
 from p in s.Query<Product>()
  .SelectMany(_ => _.Orders)
  .GroupBy(_ => new { _.Product.ProductId })
 select new { p.Key.ProductId, Count = p.Count() };