SQL Server's Partial Index

" ...So we should all use PostgreSQL, because it teaches us how to use the newer versions of SQL Server before they come out. :) " – http://www.postgresonline.com/journal/archives/195-Conditional-Uniqueness-with-Partial-Indexes.html




SQL Server 2008 already has partial index, which they termed as filtered index.

One possible use of partial index is to make conditional uniqueness on some rows.



Prior to version 2008, SQL Server don't have a mechanism to create uniqueness partially. Its uniqueness always applies to all the rows in the table. This limitation sticks out like a sore thumb, as there are certain cases when we don't want to apply uniqueness on certain rows, e.g. soft delete mechanism comes to mind.

create table ThePerson
(
    PersonId int identity primary key,
    PersonName nvarchar(100) not null,
    EmailAddress nvarchar(100) not null,
    IsDeleted bit not null default 0,
);
 
create unique index ux_ThePerson
on ThePerson(EmailAddress);
 
 
insert into ThePerson(PersonName, EmailAddress) values
('John Lennon','j.lennon@england.com'),
('Paul McCartney','p.mccartney@england.com');
 
 
update ThePerson set IsDeleted = 1 where PersonName = 'John Lennon';

 
-- this will have an error and won't be inserted to the table, as the unique constraint is applied to all rows, which includes the soft-deleted John Lennon row:
insert into ThePerson(PersonName, EmailAddress) values('Julian Lennon','j.lennon@england.com')

If we applied unique on e-mail address, when we soft delete the record John Lennon, the system should allow us to introduce another person with an e-mail address of j.lennon@england.com

However, the applied ux_ThePerson index will prevent us on adding another record with same e-mail address. We can add another person with same e-mail address if we hard delete John Lennon; or if we want to maintain soft delete mechanism, we just need to remove the unique index on EmailAddress, but the effect is not something to be desired of, not enforcing business rules(e.g. unique) on the database level could allow bad data to slip in to our records, e.g. some persons can have the same e-mail address. Not enforcing unique constraints on your entities, denies your system of being able to fail fast when invalid data happens to slip in



Enter partial index. This technology allows us to enforce unique constraints partially, i.e. not on all rows. SQL Server 2008 has this technology already, which is called filtered index



To allow Julian Lennon to be added to the database, enforce unique constraint on non-deleted rows only, to wit:

create table ThePerson
(
    PersonId int identity primary key,
    PersonName nvarchar(100) not null,
    EmailAddress nvarchar(100) not null,
    IsDeleted bit not null default 0,
);
 
 
create unique index ux_ThePerson
on ThePerson(EmailAddress)
where IsDeleted = 0;

 
 insert into ThePerson(PersonName, EmailAddress) values
('John Lennon','j.lennon@england.com'),
('Paul McCartney','p.mccartney@england.com')
 
 
-- This will have an error: Cannot insert duplicate key row in object 'dbo.ThePerson' with unique index 'ux_ThePerson'.
-- insert into ThePerson(PersonName, EmailAddress) values('Julian Lennon','j.lennon@england.com')
 
 
update ThePerson set IsDeleted = 1 where PersonName = 'John Lennon';
 
-- Upon soft-deleting John Lennon, other Person can assume the e-mail j.lennon@england.com
insert into ThePerson(PersonName, EmailAddress) values('Julian Lennon','j.lennon@england.com');

On the line 12 of the above DDL, the enforcement of unique constraint is done on non-soft-deleted rows only. Having applied the unique constraint partially, after soft-deleting John Lennon, we can now insert Julian Lennon that assumes the e-mail j.lennon@england.com



Happy Coding! ツ

Null References, A Billion Dollar Mistake

And ADO.NET promotes that billion dollar mistake ;-)



Given this code:

DateTime? periodBeginDate = null;
DateTime? periodEndDate = null;
 
var person = m_db.ExecuteScalar(CommandType.Text, "select * from person where CreatedDate between @periodBeginDate and @periodEndDate",
            new SqlParameter("@periodBeginDate", periodBeginDate),
            new SqlParameter("@periodEndDate", periodEndDate)
            );

That would result to this SQL:

exec sp_executesql N'
select * from person where CreatedDate between @periodBeginDate and @periodEndDate
',N'@periodBeginDate nvarchar(4000),@periodEndDate nvarchar(4000)',@periodBeginDate=default,@periodEndDate=default

And that gives this error:

Msg 8178, Level 16, State 1, Line 0
The parameterized query '(@periodBeginDate nvarchar(4000),@periodEndDate nvarchar(4000))
' expects the parameter '@periodBeginDate', which was not supplied.

Passing default is as good as not passing a parameter, to force passing NULL values as parameter, use DBNull.Value:

DateTime? periodBeginDate = null;
DateTime? periodEndDate = null;
 
var person = m_db.ExecuteScalar(CommandType.Text, "select * from person where CreateDate between @periodBeginDate and @periodEndDate",
            new SqlParameter("@periodBeginDate", periodBeginDate ?? (object) DBNull.Value),
            new SqlParameter("@periodEndDate", periodEndDate ?? (object) DBNull.Value)
            ); 

That would result to this SQL:

exec sp_executesql N'
select * from person where CreatedDate between @periodBeginDate and @periodEndDate
',N'@periodBeginDate nvarchar(4000),@periodEndDate nvarchar(4000)',@periodBeginDate=null,@periodEndDate=null

Answer from here: http://forums.asp.net/t/1611162.aspx/1



Happy Coding! ツ

Safe Navigation Operator

C# is lacking a safe navigation operator, so we are forced to write this kind of code...

Console.WriteLine("Winner's zipcode: {0}", lottery.LastDrawnWinner.Address.Street.ZipCode.Value);

...to this code pattern:

string zipCode = null;

// Too much guard clause...
if (lottery != null &&
        lottery.LastDrawnWinner != null &&
        lottery.LastDrawnWinner.Address != null &&
        lottery.LastDrawnWinner.Address.Street != null &&
        lottery.LastDrawnWinner.Address.Street.ZipCode != null) 
// ...too much.
{
    zipCode = lottery.LastDrawnWinner.Address.Street.ZipCode.Value;
}
 
Console.WriteLine("Winner's zip code: {0}", zipCode)

C# team hinted safe navigation operator will be available on future version of C#, which didn't materialized in C# 5 though, otherwise it looks like the following, neat!

Console.WriteLine("Winner's zip code: {0}", lottery.?LastDrawnWinner.?Address.?Street.?ZipCode.?Value);

For now what we can do is introduce a safe navigation design pattern by using extension method:

Console.WriteLine("Winner's zip code: {0}", lottery.NullSafe(l => l.LastDrawnWinner).NullSafe(w => w.Address).NullSafe(a => a.Street).NullSafe(s => s.ZipCode).NullSafe(z => z.Value));

Not as pretty as the real one (safe navigation operator), but it's tidier than doing nested ifs. You'll see the importance of having safe navigation operator / design pattern when you have some deep object graph or you have some repeated code, e.g.

g.Include(x =>
                    {                                   
                        if (x.PayrollExcelExportItemVariableBovms == null) return null;
 
                        var varPay =
                            x.PayrollExcelExportItemVariableBovms.SingleOrDefault(v => v.VariablePayPeriodStepId == param.PeriodStepId);
 
                        if (varPay != null)
                            return varPay.VariablePayBonus;
                        else
                            return null;
                    }
    ).Label(i18nVarPay_VariablePayBudget + "\n(" + param.PeriodStepLabel + ")");


g.Include(x =>
                    {                                   
                        if (x.PayrollExcelExportItemVariableBovms == null) return null;
 
                        var varPay =
                            x.PayrollExcelExportItemVariableBovms.SingleOrDefault(v => v.VariablePayPeriodStepId == param.PeriodStepId);
 
                        if (varPay != null)
                            return varPay.VariablePayBonus;
                        else
                            return null;
                    }
    ).Label(i18nVarPay_EquityPayBonus + "\n(" + param.PeriodStepLabel + ")");

I'm not gonna write my code that way if I can write it in a more robust and conciser way:

g.Include(x => x.PayrollExcelExportItemVariableBovms.?SingleOrDefault(v => v.VariablePayPeriodStepId == param.PeriodStepId).?VariablePayBudget)
        .Label(i18nVarPay_VariablePayBudget + "\n(" + param.PeriodStepLabel + ")");

g.Include(x => x.PayrollExcelExportItemVariableBovms.?SingleOrDefault(v => v.VariablePayPeriodStepId == param.PeriodStepId).?VariablePayBonus)
        .Label(i18nVarPay_VariablePayBonus + "\n(" + param.PeriodStepLabel + ")");

Oops, that's not possible in C# 5 yet, NullSafe extension method would do the trick for now:

g.Include(x => x.PayrollExcelExportItemVariableBovms.NullSafe(x => x.SingleOrDefault(y => y.VariablePayPeriodStepId == param.PeriodStepId)).NullSafe(z => z.VariablePayBudget))
        .Label(i18nVarPay_VariablePayBudget + "\n(" + param.PeriodStepLabel + ")");

g.Include(x => x.PayrollExcelExportItemVariableBovms.NullSafe(x => x.SingleOrDefault(y => y.VariablePayPeriodStepId == param.PeriodStepId)).NullSafe(z => z.VariablePayBonus))
        .Label(i18nVarPay_VariablePayBonus + "\n(" + param.PeriodStepLabel + ")");

NullSafe extension method:

namespace NullSafeExtension
{
     
    public static class NullSafeHelper
    {
        public static U NullSafe<T, U>(this T t, Func<T, U> fn)
        {
            return t != null ? fn(t) : default(U);
        }
    }
}

Technique source: http://qualityofdata.com/2011/01/27/nullsafe-dereference-operator-in-c/



Happy Coding! ツ


UPDATE

Why using safe navigation is better than doing try-catch

When using a try catch, the code won't be able to run the next lines after of the line that has a null. Using a guard clause can prevent nulls from interfering on running all the lines in your code, but that will bloat or increase the noise in code.

I would rather use safe navigation than to use guarded clause. If we rather use catching null reference exception, it will prevent the code from running the next lines.

An example, a lottery winner won on a specific year, yet he has no address:

// Let's say a lottery winner has no registered address,
// hence this line will cause a null exception. So if we are catching exception...
Console.WriteLine("Winner's zipcode: {0}", lottery.LastDrawnWinner.Address.Street.ZipCode.Value); 

// ...this line won't execute:
Console.WriteLine("Year won: {0}", lottery.LastDrawnWinner.YearWon); 

Lest we forget, WCF is Windows Communication Foundation, not Web Communication Foundation. nod nod wink wink

Suffice to say, WCF was not created with web app in mind, try to interoperate WCF with a web front-end, I think (sorry I didn't do my homework) it's doable, but I would hazard a guess that a web-facing API fashioned out of WCF service will feel very byzantine, e.g. what is the DataMember attribute's business to a jQuery-driven or AngularJS-driven web app?



Yesterday, we have encountered a WCF limitation where we cannot transport a dictionary object from WCF to ASP.NET MVC, then from MVC we need to transport the dictionary object as JSON object. We are using ASP.NET MVC controller as a service for delivering JSON to a web front-end that needed the data, as ASP.NET MVC has a more natural affinity to web and JSON than a WCF to web and JSON is.



In a nutshell, every web application is really just one complex marshaling problem, we marshal the data from DB through WCF, then through ASP.NET MVC, then through JSON, then to the DOM, and then we do it all again in reverse order. If only the first gateway(e.g. WCF) can marshal (via JSON) our data directly to a web front-end, we won't even need ASP.NET MVC to carry out the last step, we can just directly return JSON data right there on the first gateway. Today we will explore that option, albeit without WCF nor ASP.NET MVC.





Here's the summary of steps

1. Create a web server. We can use anything here, for simplicity sake we will just use ASP.NET Web application

2. Create a ServiceStack. We replace both WCF and ASP.NET MVC JSON server with ServiceStack

3. From the web front-end(jQuery or AngularJS) use the JSON from ServiceStack

4. Profit!





Step 1. Create a new ASP.NET Web Application

Step 2. Get the "Starter ASP.NET Website Template - ServiceStack at /" from NuGet



Edit WebServiceExample.cs to this:

public Person Any(Hello request)
{
    return new Person { Lastname = "Torvalds", Firstname = "Linus", Age = 42 };
}

Note that you don't even need to explicitly serialize the object to Json on the service, here's a sample output via this url: http://localhost:62015/hello?format=json



Note that the above code and all the code that follows have this output:

{"Lastname":"Torvalds","Firstname":"Linus","Age":42}

You can even specify object as the returned type, and everything still works as expected:

public object Any(Hello request)
{
    return new Person { Lastname = "Torvalds", Firstname = "Linus", Age = 42 };
}

So what's the implication of being able to return just an object type? we can use anonymous type!

public object Any(Hello request)
{
    return new {Lastname = "Torvalds", Firstname = "Linus", Age = 43};
}

How about the humble dictionary that WCF is refusing to marshal? It Just Works on ServiceStack

public object Any(Hello request)
{            
    var d = new Dictionary<string, object>();
    d.Add("Lastname", "Torvalds");
    d.Add("Firstname", "Linus");
    d.Add("Age", 44);
    return d;
}

How about the dynamic object we originally intended to use on WCF which WCF is not capable of marshalling? Keep them coming, It Just Works on ServiceStack!

public object Any(Hello request)
{
        dynamic person = new System.Dynamic.ExpandoObject();
 
        person.Lastname = "Torvalds";
        person.Firstname = "Linus";
        person.Age = 45;
 
        return person;
}

And the kind of marshalling dynamic data we originally (dictionary is just a plan B, which doesn't work on WCF) intend to do on WCF which WCF is not capable of? Marshalling dynamic is perfectly fine on ServiceStack. FTW!

public object Any(Hello request)
{
        dynamic person = new System.Dynamic.ExpandoObject();
 
        person.Lastname = "Torvalds";
        person.Firstname = "Linus";
        person.Age = 46;
 
        var dictPerson = (IDictionary<string, object>)person;
 
        var r = new Random();
        for (char c = 'A'; c <= 'E'; c++)
        {
               dictPerson[c.ToString()] = r.Next(7);
        }
 
        return person;
}

Sample output:

{"Lastname":"Torvalds","Firstname":"Linus","Age":46,"A":5,"B":6,"C":2,"D":3,"E":5}

Step 3. From the web front-end(jQuery or AngularJS) use the JSON from ServiceStack. Left as an exercise to the reader

Step 4. Profit! Left as an exercise to the reader


ServiceStack is the best SOA framework your web front-end can communicate to


That's it folks, everything just works the way we intend things to!


Happy Coding! ツ

Clay, the power of JavaScript within C#

The code:

using System;
using System.Linq;
using System.Collections.Generic;
 
using System.Dynamic;
 
using ClaySharp;
 
 
 
namespace TheDynamic
{
    class Program
    {
        static void Main(string[] args)
        {
            // SampleExpando();
            SampleClay();
        }
 
        static void SampleExpando()
        {
            dynamic d = new ExpandoObject();
           
            d["LoremIpsum"] = "World"; // ExpandoObject limitation, dictionary-property duality is not possible
            Console.WriteLine("Hello {0}", d.LoremIpsum);
           Console.ReadLine();
        }
 
 
        // Clay: The power of JavaScript within C#
        // http://www.hanselman.com/blog/NuGetPackageOfTheWeek6DynamicMalleableEnjoyableExpandoObjectsWithClay.aspx
        static void SampleClay()
        {
           
            dynamic New = new ClayFactory();
           
            
            var great = New.Blah();
         
            // just to prove Clay is more dynamic than ExpandoObject, you can add things at runtime, and access them as property:
            // string s = Console.ReadLine(); // Enter this: LoremIpsum
            // great[s]
 
 
            // You can set things via dictionary approach
            great["LoremIpsum"] = "World";         
            // And access them through both property or dictionary approach, just like in JavaScript
            Console.WriteLine("Hello: {0} {1}", great.LoremIpsum, great["LoremIpsum"]);
 
 
            // And vice versa, you can set things using property approach
            great.Awe = "Some";
            // And access them through both dictionary or property approach, just like in JavaScript
            Console.WriteLine("Feelings: {0} {1}", great["Awe"], great.Awe);
 
           
            var props = new Dictionary<string, object>();
 
 
            Func<object> nullFunc = () => null;
 
            var clayBehaviorProvider = great as IClayBehaviorProvider;
            clayBehaviorProvider.Behavior.GetMembers(nullFunc, great, props);
 
 
            Console.WriteLine("\n\nLaugh the problems: \n");
 
            foreach (KeyValuePair<string, object> kv in props.Skip(1))
            {
                Console.WriteLine("{0} {1}", kv.Key, kv.Value);
            }
 
            Console.ReadLine();
        }
    }
 
    // Good read, Clay in action, in ASP.NET MVC! http://orchard.codeplex.com/discussions/406947
}
 

The output:

Hello: World World
Feelings: Some Some
 
 
Laugh the problems:
 
LoremIpsum World
Awe Some