Anti-If Campaign

If-less engineering
• http://alisnic.github.com/posts/ifless/
• http://www.antiifcampaign.com/
• http://www.tommycode.se/2010/03/anti-if-campaign.html

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

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;

*/

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

There's nothing magical about magic numbers

This magic number's intent is not clear:

<option value="3">

It's hard to know where in the program uses the value 3 and what is its meaning.

If we do it like the following, it's self-describing and we can find(Find Usages (Alt+F7)) what part of the program uses the value, things are a lot better:

<option value="@((int)ReportingModule.Products)" >

That's more magical!

Happy Coding! ツ

NHibernate Filter value not set

If you received this kind of error...

Filter [Filters] parameter [LanguageCode] value not set

...chances are you have this kind of code:

s.EnableFilter("Filters").SetParameter("LanguageCode", "zh");             

s.EnableFilter("Filters").SetParameter("FallbackLanguageCode", "en");

Must rewrite that to this:

s.EnableFilter("Filters")
    .SetParameter("LanguageCode", "zh")                        
    .SetParameter("FallbackLanguageCode", "en");

Localizing date

For July 16, 2012, and giving it a format of d in SSRS

• Formatted in German as: 16.07.2012
• Formatted in English as: 7/16/2012

Had the d format in English formatted July as 07 (i.e. double digits), we won't need special handling for English

Here's how to handle the special case, special cases are always evaluated first(pardon my captain-obviousness) on the conditions

=IIF(Parameters!NeutralLanguageCode.Value = "en", "MM/dd/yyyy", "d")

Here's the customized output for English: 07/16/2012



Likewise with date time (g format), we have to handle the special case, July 16 2012 6:00 PM

• Formatted in German as: 16.07.2012 18:00
• Formatted in English as: 7/16/2012 6:00 PM

=IIF(Parameters!NeutralLanguageCode.Value = "en", "MM/dd/yyyy hh:mm tt", "g")

Here's the customized output for English: 07/12/2012 06:00 PM

General formatting: http://msdn.microsoft.com/en-us/library/ms157406(v=sql.90).aspx

Granular formatting: http://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.71).aspx

DRY your code, Resharper can give your code some Linq love

This has a WET characteristic, which prevents Resharper from being able to analyze your loops:

Make it DRY…

… and Resharper will be smart enough to deduce the intent of your code:

Reshaper can then give your code some Linq love:

Export strongly-typed model to Excel. Making a DRY-adhering API

Let's say we have an API than can export data to Excel file via ASP.NET MVC: (Download the Excel exporting component from: http://epplus.codeplex.com/)

public class ExcelResult : ActionResult
{
 
    public ExcelPackage ExcelPackage { get; set; }
    public string FileName { get; set; }
 
 
    public override void ExecuteResult(ControllerContext context)
    {
        context.HttpContext.Response.AppendHeader("Content-type", "application/vnd.ms-excel");
        context.HttpContext.Response.AppendHeader("Content-disposition", string.Format("attachment; filename={0}.xls", FileName));
 
 
        byte[] a = ExcelPackage.GetAsByteArray();
        context.HttpContext.Response.OutputStream.Write(a, 0, a.Length);
    }
}

And then we want to re-use that component able to export list of employee, we can do this:

public class EmployeeExcelResult : ExcelResult
{
    IList<Employee> _employees;
 
 
    public EmployeeExcelResult(IList<Employee> employees)
    {
        _employees = employees;
    }
 
 
    public override void ExecuteResult(ControllerContext context)
    {
        ExcelPackage = new ExcelPackage();
 
        ExcelWorksheet sheet = ExcelPackage.Workbook.Worksheets.Add("Export");
 
 
        sheet.Cells[1, 1].Value = "Firstname";
        sheet.Cells[2, 1].LoadFromCollection(_employees.Select(y => new {Value = y.FirstName}));
 
        sheet.Cells[1, 2].Value = "Lastname";
        sheet.Cells[2, 2].LoadFromCollection(_employees.Select(y => new { Value = y.LastName }));
 
        sheet.Cells[1, 2].Value = "Age";
        sheet.Cells[2, 2].LoadFromCollection(_employees.Select(y => new { Value = y.Age}));
 
 
        base.ExecuteResult(context);
    }
 
}

And we will use that like this in the controller's action:

public ActionResult ExportEmployeesToExcel()
{
 
 IList<Employee> employees = new List<Employee>()
 {
   new Employee() { FirstName = "John", MiddleName = "Winston", LastName = "Lennon", Age = 12345, SongsPercent = 0.301234},
   new Employee() { FirstName = "Paul", MiddleName = "Phoenix", LastName = "McCartney", Age = 67891234, SongsPercent = 0.205678},
   new Employee() { FirstName = "George", MiddleName = "Tough", LastName = "Harisson", Age = 3456 },
   new Employee() { FirstName = "Ringo", MiddleName = "Drum", LastName = "Starr", Age = 3456 }
 };    
 
 return new EmployeeExcelResult(employees);
}

It should be noted that no ethically-trained software engineer would ever consent to write a DestroyBaghdad procedure. Basic professional ethics would instead require him to write a DestroyCity procedure, to which Baghdad could be given as a parameter -- Nathaniel Borenstein

The approach above(class EmployeeExcelResult), though it adheres to two of the basic OOP tenets(i.e. inheritance and polymorphism), has a problem if we need to use another kind of list to be exported, say list of product. Aside from that approach is not reusable, it's error prone too. If you are a keen observer, you'll notice that the Age property overwrites the Lastname property, resulting to two exported columns only.


We got to make the component very re-usable:

public class ExcelResult<T> : ExcelResult
{
    IEnumerable<T> _list;
 
 
    public ExcelResult(IEnumerable<T> list)
    {
        _list = list;
    }
 
 
    public override void ExecuteResult(ControllerContext context)
    {
        ExcelPackage = new ExcelPackage();
 
        ExcelWorksheet sheet = ExcelPackage.Workbook.Worksheets.Add("Export");
 
        int ordinal = 0;
        foreach (System.Reflection.PropertyInfo pi in typeof(T).GetProperties())
        {
            ++ordinal;
 
            sheet.Cells[1, ordinal].Value = pi.Name;
            sheet.Cells[2, ordinal].LoadFromCollection(_list.Select(x => new {Value = GetPropValue(x, pi.Name)}));
        }
        base.ExecuteResult(context);
    }
 
    // In this age of stackoverflow, who needs MSDN?
    // http://stackoverflow.com/questions/1196991/get-property-value-from-string-using-reflection-in-c-sharp
    static object GetPropValue(object src, string propName)
    {
        return src.GetType().GetProperty(propName).GetValue(src, null);
    }
 
}
 
.
.
.
 
public ActionResult ExportEmployeeList()
{
 IList<Employee> employees = new List<Employee>()
 {
   new Employee() { FirstName = "John", MiddleName = "Winston", LastName = "Lennon", Age = 12345, SongsPercent = 0.301234},
   new Employee() { FirstName = "Paul", MiddleName = "Phoenix", LastName = "McCartney", Age = 67891234, SongsPercent = 0.205678},
   new Employee() { FirstName = "George", MiddleName = "Tough", LastName = "Harisson", Age = 3456 },
   new Employee() { FirstName = "Ringo", MiddleName = "Drum", LastName = "Starr", Age = 3456 }
 };
 return new ExcelResult<Employee>(employees);
}

That's flexible now, but we can improve it more. We can make it more flexible by letting us able to specify which columns should be exported to Excel only, and able to specify the column's format. There are three approach we can use when designing an API such as that.

• First we maintain a dictionary of column's metadata, where the metadata indicates the column's label and formatting. But dictionary is a non-starter as it promotes stringly-typed programming.
• Second is we can use attributes, but that would entail decorating the attributes directly to the concerned class, what if the other party(think B2B's provider) won't allow you to add attributes to their classes? The work-around on this second option is to use buddy classes, this would work only if the consuming party's classes are generated with partials(e.g. WCF) on them. But even buddy classes is an option, buddy classes violates DRY principle, and refactoring wouldn't be available also. Attributes-based API won't let us be creative, even a simple API such as passing the method's address to the function is not possible with attribute, hence necessitates passing the method as string, and calling the method through reflection. Stringly-typed API is brittle and non-discoverable.
• 
  That leaves us with the third option, let's design an API that avoids violating DRY principle, and avoids stringly-typed programming to boot. We can use Fluent API and C#'s Expression

We can make a better design for an API if we design it from the end, that is we design it how we wanted to use it. So here's how we wanted to use our API, this is similar to Fluent NHibernate: https://github.com/jagregory/fluent-nhibernate/wiki/Getting-started

public ActionResult ExportEmployeesToExcel()
{    
    IList<Employee> employees = new List<Employee>()
            {
                new Employee() { FirstName = "John", MiddleName = "Winston", LastName = "Lennon", Age = 12345, SongsPercent = 0.301234},
                new Employee() { FirstName = "Paul", MiddleName = "Phoenix", LastName = "McCartney", Age = 67891234, SongsPercent = 0.205678},
                new Employee() { FirstName = "George", MiddleName = "Tough", LastName = "Harisson", Age = 3456 },
                new Employee() { FirstName = "Ringo", MiddleName = "Drum", LastName = "Starr", Age = 3456 }
            };
 
    return new ExcelResultFromList<Employee>(employees)
    {                           
       Filename = "Employees",
       ExcelMapping = new EmployeeExcelMapping()
    };
}
 
.
.
.
 
public class EmployeeExcelMapping : ExcelMapping<Employee>
{
    public EmployeeExcelMapping()
    {
        Include(x => x.LastName).Label("Family Name");
        Include(x => x.FirstName);            
        Include(x => x.Age).NumberFormat("#,#00.00");
        Include(x => x.SongsPercent).Label("Song%").NumberFormat("0.000%");
        
        // we didn't include the middle name to exported excel 
    }
}

The above will appeal to you if you are a Fluent NHibernate fan. If you have used NHibernate 3.2, you might already learned there's another style of mapping by code which is built-in in NHibernate, you can inline-ly(if there's such a word) map your class with the latest NHibernate. Meaning, you don't have to create a separate class for your mapping, this is what "loquacious" (such an unfortunate name, the fluent nomenclature was already taken by others) mapping facilitates. The NHibernate's loquacious API has a more one-stop-shop feel into it. The API below is similar to this style: http://fabiomaulo.blogspot.com/2011/04/nhibernate-32-mapping-by-code.html

public ActionResult ExportEmployeesToExcel()
{
 
 IList<Employee> employees = new List<Employee>()
 {
   new Employee() { FirstName = "John", MiddleName = "Winston", LastName = "Lennon", Age = 12345, SongsPercent = 0.301234},
   new Employee() { FirstName = "Paul", MiddleName = "Phoenix", LastName = "McCartney", Age = 67891234, SongsPercent = 0.205678},
   new Employee() { FirstName = "George", MiddleName = "Tough", LastName = "Harisson", Age = 3456 },
   new Employee() { FirstName = "Ringo", MiddleName = "Drum", LastName = "Starr", Age = 3456 }
 };
 
 return ExcelResultFromList.Create<Employee>
                (list: employees, 
                filename: "Employees",
                mapAction: e =>
                                {
                                    e.Include(x => x.LastName).Label("Apelyido");
                                    e.Include(x => x.FirstName);
                                    e.Include(x => x.Age).NumberFormat("#,#00.00");
                                    e.Include(x => x.SongsPercent).Label("Song%").NumberFormat("0.000%");
                                });
} 

You can choose either fluent style or the loquacious style. Whichever mental model suits you



That's it, that's how we want our API to be, a good .NET citizen, very respecting of POCO. Very respecting of DRY principle: http://en.wikipedia.org/wiki/Don't_repeat_yourself




Expand the full code below to see how to implement the API above:

using System.Linq;


public class ExcelMapping<T>
{
    readonly System.Collections.Generic.IList<ExportMetaData<T>> _exportList = new System.Collections.Generic.List<ExportMetaData<T>>();
    public System.Collections.Generic.IList<ExportMetaData<T>> ExportList { get { return _exportList; } }



    public ExportPart<T> Include<TProp>(System.Linq.Expressions.Expression<System.Func<T, TProp>> p)
    {
        string name = new ExpressionGetter.PropertyPathVisitor().GetPropertyPath(p);

        var emd = new ExportMetaData<T>();
        ExportList.Add(emd);

        var input = new ExportPart<T>(emd, name);
        return input;

    }
}

public class ExportMetaData<T>
{
    public string Name { get; set; }
    public string Label { get; set; }
    public string NumberFormat { get; set; }

    // just ready this in case EPPlus can compute column width in the future
    // public bool AutoFit { get; set; }

    public System.Func<T, object> ValueReplacer { get; set; }
}


public class ExportPart<T>
{
    ExportMetaData<T> _emd;

    public ExportPart(ExportMetaData<T> exportMetaData, string name)
    {
        _emd = exportMetaData;
        _emd.Name = name;
    }

    public ExportPart<T> NumberFormat(string format)
    {
        _emd.NumberFormat = format;
        return this;
    }

    public ExportPart<T> Label(string label)
    {
        _emd.Label = label;
        return this;
    }

    public ExportPart<T> ValueReplacer(System.Func<T, object> valueReplacer)
    {
        _emd.ValueReplacer = valueReplacer;
        return this;
    }
}


// Should I call this ExcelResultFromListFactory?
public static class ExcelResultFromList
{
    public static ExcelResultFromList<T> Create<T>(System.Collections.Generic.IEnumerable<T> list, string filename, System.Action<ExcelMapping<T>> mapAction)
    {
        var e = new ExcelMapping<T>();
        var r = new ExcelResultFromList<T>(list) { FileName = filename, ExcelMapping = e };
        mapAction(e);
        return r;
    }
}


public class ExcelResultFromList<T> : ExcelResult
{

    public ExcelMapping<T> ExcelMapping { get; set; }

    public System.Collections.Generic.IEnumerable<T> _list;


    public ExcelResultFromList(System.Collections.Generic.IEnumerable<T> list)
    {
        _list = list;
    }


    public override void ExecuteResult(System.Web.Mvc.ControllerContext context)
    {
        ExcelPackage = new OfficeOpenXml.ExcelPackage();

        OfficeOpenXml.ExcelWorksheet sheet = ExcelPackage.Workbook.Worksheets.Add("Export");



        int col = 0;
        foreach (ExportMetaData<T> emd in ExcelMapping.ExportList)
        {
            int ordinal = ++col;
            sheet.Cells[1, ordinal].Value = emd.Label ?? emd.Name;
            OfficeOpenXml.ExcelColumn ec = sheet.Column(ordinal);

            if (!string.IsNullOrEmpty(emd.NumberFormat))
                ec.Style.Numberformat.Format = emd.NumberFormat;


            sheet.Cells[2, ordinal].LoadFromCollection(_list.Select(y =>
                new
                {
                    Value = emd.ValueReplacer == null ? GetPropValue(y, emd.Name) : emd.ValueReplacer(y)
                }));
        }


        base.ExecuteResult(context);
    }


    // In this age of stackoverflow, who needs MSDN?
    // http://stackoverflow.com/questions/1196991/get-property-value-from-string-using-reflection-in-c-sharp
    static object GetPropValue(object src, string propName)
    {
        return src.GetType().GetProperty(propName).GetValue(src, null);
    }
}


// We can t make an apple pie from scratch, some ingredients have to come from somewhere:
// PropertyPathVisitor sourced from: http://www.thomaslevesque.com/2010/10/03/entity-framework-using-include-with-lambda-expressions/
namespace ExpressionGetter
{

    class PropertyPathVisitor : System.Linq.Expressions.ExpressionVisitor
    {
        private System.Collections.Generic.Stack<string> _stack;

        public string GetPropertyPath(System.Linq.Expressions.Expression expression)
        {
            _stack = new System.Collections.Generic.Stack<string>();
            Visit(expression);
            return _stack
                .Aggregate(
                    new System.Text.StringBuilder(),
                    (sb, name) =>
                        (sb.Length > 0 ? sb.Append(".") : sb).Append(name))
                .ToString();
        }



        protected override System.Linq.Expressions.Expression VisitMember(System.Linq.Expressions.MemberExpression expression)
        {
            if (_stack != null)
                _stack.Push(expression.Member.Name);
            return base.VisitMember(expression);
        }


    }

}


public class ExcelResult : System.Web.Mvc.ActionResult
{

    public OfficeOpenXml.ExcelPackage ExcelPackage { get; set; }
    public string FileName { get; set; }


    public override void ExecuteResult(System.Web.Mvc.ControllerContext context)
    {
        context.HttpContext.Response.AppendHeader("Content-type", "application/vnd.ms-excel");
        context.HttpContext.Response.AppendHeader("Content-disposition", string.Format("attachment; filename={0}.xls", FileName));


        byte[] a = ExcelPackage.GetAsByteArray();
        context.HttpContext.Response.OutputStream.Write(a, 0, a.Length);
    }
}

Happy Coding! ツ

Query SARGability

A colleague asked how to select from two tables dynamically, something along this line(question was not exactly phrase as the following):

DECLARE @tableToSelect CHAR(1) = 'y';
select * from @tableToSelect where a < 100;   

If that is even possible, it would be a problem if the similar columns are differently named on the two tables, say PersonID, MencschID (German for PersonID). This can not select all the persons from the American tables:

select * from @tableToSelect where MenschID < 100;

I suggested him this:

WITH x AS
(
    SELECT a,b FROM dbo.t
    WHERE @tableToSelect = 't'
    UNION ALL    
    SELECT c,d FROM dbo.y    
    WHERE @tableToSelect = 'y'
)
SELECT * FROM x WHERE a < 100;

A discerning programmer that he is, he quizzed me if that would result to inefficient query. I like that he posed that question and wanted his code to be performant.


If he had asked me that question when I was just a starting developer, I would concur with him. I would naturally assume that it's not efficient to union the tables and filtering it further. I would assume that it would be better if the rows are filtered right from the root source, as illustrated below, and thus enabling both queries of the UNIONed queries to use the available index:

SELECT a,b
FROM t
WHERE @tableToSelect = 't' and a < 100
 
UNION all
 
SELECT c,d
FROM y
WHERE @tableToSelect = 'y' AND c < 100;

Fortunately for us, internet is a wonderful place; an eon ago, I stumble upon an article that says RDBMS doesn't short-circuit conditions: http://weblogs.sqlteam.com/mladenp/archive/2008/02/25/How-SQL-Server-short-circuits-WHERE-condition-evaluation.aspx


So this would result to an error (Conversion failed when converting date and/or time from character string):

SELECT   *
FROM     t1
WHERE    ISDATE(val) = 1
         AND CONVERT(DATETIME, val) < GETDATE();

If we try to fool the RDBMS by eagerly filtering the valid dates first, this would still not work, this will still result to conversion error. A proof that WITH or any form of re-arranging the query doesn't eagerly execute the query.

WITH x AS
(
      SELECT   *
      FROM     t1
      WHERE    ISDATE(val) = 1
)
SELECT *
FROM x    
WHERE CONVERT(DATETIME, val) < GETDATE();

That means, the conditions' execution order is not executed based on how you arrange your query. The outer query's conditions could be mixed with the inner query's condition by your RDBMS. Armed with that knowledge in mind, the following two queries are executed similarly:

SELECT *
FROM t
WHERE @tableToSelect = 't' and a < 100
 
UNION all
 
SELECT *
FROM y
WHERE @tableToSelect = 'y' AND c < 100;
 

WITH x AS
(
    SELECT a,b FROM dbo.t
    WHERE @tableToSelect = 't'
    UNION ALL    
    SELECT c,d FROM dbo.y    
    WHERE @tableToSelect = 'y'
)
SELECT * FROM x WHERE a < 100;

That is, the second query(CTE) is expanded to the same query as the first query; thus, the second query's a < 100 expression utilizes all the available indexes on both tables, making the query performant. Given that they are semantically the same, it's better to use the second query as it's easier to maintain, the query's condition is just placed on one location only, there's no code duplication involved. Some shun the table-deriving or CTE approach, as they think enclosing the query on CTE or table-deriving it would deprive the expression a < 100 the SARGability it needed; this is not true, CTE-using queries can still use the available indexes on both tables.



Both queries have exactly the same identical query execution plan:

Here's the supporting DDL:

SET NOCOUNT ON;

CREATE TABLE t(a INT, b INT);
CREATE TABLE y(c INT, d INT);

DECLARE @i INT;

SET @i = 0;
WHILE @i < 100000 BEGIN
    INSERT INTO t(a,b) VALUES(@i,0);    
SET @i = @i + 1;
END;

INSERT INTO y(c,d) SELECT a,b FROM t;
CREATE INDEX ux_t ON t(a);
CREATE INDEX ux_y ON y(c);

SET NOCOUNT OFF;

If you are a keen observer, you'll ask, what will happen to the query with the date detection in it? How to prevent the query from executing the conversion if the field is not a date? We can really do short-circuit in SQL Server explicitly, but that would entails using CASE WHEN, and that would make your query not being SARGable. Here's the short-circuited query:

SELECT *
FROM t1
WHERE CASE WHEN ISDATE(val) = 1 AND CONVERT(DATETIME, val) < GETDATE() THEN 1 END = 1

Another approach, is to use an optimization fence on ISDATE. This way, SQL Server don't need to merge the ISDATE condition with CONVERT, avoiding the conversion failed error.

with x as
(
      SELECT   TOP 4000000000 *
      FROM     t1
      WHERE    ISDATE(val) = 1
)
SELECT *
FROM x    
WHERE CONVERT(DATETIME, val) < GETDATE();

So here's the performance of a non-SARGable query:

DECLARE @tableToSelect CHAR(1) = 'y';

-- select * from @tableToSelect where a < 100;


SELECT a,b
FROM t
WHERE case when @tableToSelect = 't' AND a < 100 then 1 end = 1
 
UNION all
 
SELECT c,d
FROM y
WHERE case when @tableToSelect = 'y' AND c < 100 then 1 end = 1;
 

WITH x AS
(
    SELECT a,b FROM dbo.t
    WHERE @tableToSelect = 't'
    UNION ALL    
    SELECT c,d FROM dbo.y    
    WHERE @tableToSelect = 'y'
)
SELECT * FROM x WHERE a < 100;

As we can see the non-SARGable query is slow compared to the SARGable one. non-SARGable query has a cost of 81% against 19% of SARGable one



That's it, we should strive to make our query use our tables' indexes



Happy Coding! ツ

Armor-wielding shortcut with ZenCoding

When was the last time you have a nerdgasm? Was it since you last saw Iron Man's trailer where you've seen his suitcase armor expanded to an Iron Man suit and automatically costumed(armed?) him? Was it since you first learned Linq/lambda and all the expressiveness, succinctness and slickness you can accomplish with it?



Wow, that was so 3 years ago! In internet time it's an eternity! It's time to rectify that long drought. There are times we are in a Zen-like state (a.k.a. in the zone), wherein the code to implement something are all on your head already, your project is virtually done, ready to be coded/typed; and the only thing that is a bottleneck to implement the app is the interface between you and the computer, is the keyboard, is the typing.


With ZenCoding, typing humongous HTMLs off the top of your head will be a thing of the past. Given the following tags..

<div id="page">
    <div class="logo"></div>
    <ul id="navigation">
        <li class="navitem"></li>
        <li class="navitem"></li>
        <li class="navitem"></li>
        <li class="navitem"></li>
        <li class="navitem"></li>
        <li class="navitem"></li>
        <li class="navitem"></li>
    </ul>
</div>

..you can type that automatically with ease with ZenCoding, just type the following (CSS-like syntax), then press the tab key(on most editors it's tab key, on Visual Studio press Alt+Z):

div#page>div.logo+ul#navigation>li.navitem*7

Neat isn't it? Tony Stark will be fuming with envy when he'll know that his suitcase armor is just a one-trick pony compared to your ZenCoding armor. How about the following contruct?

div#menu>table>tr>td*7>a.nav

That expands to this:

<div id="menu">
    <table>
        <tr>
            <td><a href="" class="nav"></a></td>
            <td><a href="" class="nav"></a></td>
            <td><a href="" class="nav"></a></td>
            <td><a href="" class="nav"></a></td>
            <td><a href="" class="nav"></a></td>
            <td><a href="" class="nav"></a></td>
            <td><a href="" class="nav"></a></td>
        </tr>
    </table>
</div>

And when you want to make multiple tr tags with multiple td tags with that construct, you already know the drill ;-)


With ZenCoding, every ZenCoding shortcut are an armor ready to spring from the master's fingertips and empowers the master and protects the people


Get the ZenCoding at: http://visualstudiogallery.msdn.microsoft.com/924090a6-1177-4e81-96a3-e929d7193130

Happy Coding! ツ

Sample Fluent API barebone code

using System;
using System.Collections.Generic;

using System.Linq;
using System.Text;
using System.Linq.Expressions;




namespace Sample
{
    using DomainToInputMappings;
    
    internal class Program
    {
        private static void Main(string[] args)
        {
            var x = new FlightInput();
            Console.ReadKey();
        }
    }
}


// NHibernate can use these domain classes directly:
namespace DomainClasses
{
    public class Flight
    {
        public virtual Country Country { get; set; }
        public virtual City City { get; set; }
        public virtual int StayDuration { get; set; }
    }
    
    public class Country
    {
        public virtual string CountryCode { get; set; }
        public virtual string CountryName { get; set; }
        public virtual int Population { get; set; }
    }
    
    public class City
    {
        public virtual Country Country { get; set; }
        
        public virtual int CityID { get; set; }
        public virtual string CityName { get; set; }
    }
    
}

namespace DomainToInputMappings
{
    using DomainClasses;
    using InputMapper;
    using FinderControls;
    
    
    // On some systems, we don't directly map the domain classes to input,
    // should flatten the domain classes to DTO (e.g. x.Country.CountryCode to x.CountryCode) first,
    // then map the input to the DTO instead,
    // so it's easier and lighter(CountryName and Population won't be transferred) to transfer
    // and use the object across the wire. e.g. Silverlight, jQuery ajax, etc  
    class FlightInput : InputMap<Flight>
    {
        public FlightInput()
        {
            Input(x => x.Country.CountryCode)
                .DisplayWidth(200)
                    .Color(ConsoleColor.Blue)
                    .UseFinder<CountryFinder>().SelectedID(x => x.SelectedCountryCode);
            
            
            
            // The advantage of fluent API, aside from there's autocomplete,
            // we don't need to do stringly-typed approach when referring to the property of an object,
            // we can refer to the property in a strongly-typed manner (e.g. x.Country.CountryCode).
            // With attributes-based API, we have to do this instead:
            
            // Compilers can't catch error if you misspelled Country.ConutryCode if you make stringly-typed API
            // [DisplayWidth(200)]
            // [Color(KnownColor.Blue)]
            // [UseFinder(typeof(CityFinder), SelectedID = "SelectedCityID", CascadingField = "Country.CountryCode")]
            
            
            Input(x => x.City.CityID)
                .DisplayWidth(200)
                    .Color(ConsoleColor.Blue)
                    .UseFinder<CityFinder>().SelectedID(x => x.SelectedCityID).CascadingField(x => x.Country.CountryCode);
            
            Input(x => x.StayDuration)
                .DisplayWidth(100)
                    .Color(ConsoleColor.Green)
                    .UseSpinner(1, 10);
        }
    }
    
}


namespace FinderControls
{
    public class CountryFinder
    {
        public int SelectedCountryCode { get; set; }
        public bool MultiSelect { get; set; }
    }
    
    public class CityFinder
    {
        public int SelectedCityID { get; set; }
        public bool MultiSelect { get; set; }
    }   
}




namespace InputMapper
{
    using ExpressionGetter;
    public abstract class InputMap<T>
    {
        public InputPart<T> Input<TProp>(Expression<Func<T, TProp>> p)
        {
            Console.WriteLine(new PropertyPathVisitor().GetPropertyPath(p));
            
            
            var inputPart = new InputPart<T>();
            return inputPart;
        }
    }
    
    
    public class InputPart<T>
    {
        public InputPart<T> DisplayWidth(int width)
        {
            return this;
        }
        
        public InputPart<T> Color(ConsoleColor color)
        {
            return this;
        }
        
        public FinderPart<T, TFinder> UseFinder<TFinder>()
        {
            return new FinderPart<T, TFinder>();
        }
        
        
        public InputPart<T> UseSpinner(int from, int to)
        {
            return this;
        }
    }
    
    public class FinderPart<T, TFinder>
    {
        public FinderPart<T, TFinder> SelectedID<TFinderProp>(Expression<Func<TFinder, TFinderProp>> x)
        {
            return this;
        }
        
        public FinderPart<T, TFinder> CascadingField<TProp>(Expression<Func<T, TProp>> x)
        {
            return this;
        }
    }
}

namespace ExpressionGetter
{
    // We can’t make an apple pie from scratch, some ingredients have to come from somewhere:
    
    // PropertyPathVisitor sourced from: http://www.thomaslevesque.com/2010/10/03/entity-framework-using-include-with-lambda-expressions/
    class PropertyPathVisitor : ExpressionVisitor
    {
        private Stack<string> _stack;
        
        public string GetPropertyPath(Expression expression)
        {
            _stack = new Stack<string>();
            Visit(expression);
            return _stack
                .Aggregate(
                    new StringBuilder(),
                    (sb, name) =>
                    (sb.Length > 0 ? sb.Append(".") : sb).Append(name))
                    .ToString();
        }
        
        
        
        protected override Expression VisitMember(MemberExpression expression)
        {
            if (_stack != null)
                _stack.Push(expression.Member.Name);
            return base.VisitMember(expression);
        }
        
        
    }
    
}

Rationale for fluent-based API: http://www.ienablemuch.com/2012/11/fluent-based-apis.html

Dream Setup

We all have our own dream setup for development machine

My dream setup is more on software stack though rather than on hardware:

• AngularJS - MVC on client-side instead of on server-side (e.g. ASP.NET MVC)
• ServiceStack - .NET REST API
• ToTheEfnhX - Everyone are self-patronizing of their self-written repository component :p
• DitTO - Everyone are self-patronizing of their self-written bi-directional domain-classes-to-DTO-classes component too :p
• Ninject - Dependency injection component, for wiring repository component against the ORM
• NHibernate - ORM - maps the domain classes to the database. Glad to know that the company uses NHibernate
• Fluent NHibernate - fluent approach for setting up the mapping between NHibernate and database
• Redis - caching provider for NHibernate
• Postgres - database

• Moq - for mocking object for unit testing
• Microsoft Test if using Visual Studio - not a dream setup, but this is good enough for unit testing
• NUnit - unit testing for C# if using MonoDevolop instead of Visual Studio
• WatiN - For doing web application testing
• Jasmine - unit testing for Javascript

• Visual Studio or MonoDevelop