Use CollectionAssert.AreEqual to test list

[Test]
public void Fibonacci_is_ok()
{
 CollectionAssert.AreEqual(
    new[] { 0,1,1,2,3,5,8 }, 
    Utils.FibonacciGenerator().Take(7).ToArray() );
}

Deletion strategy for Sql Server 2008

create table product
(
product_id int not null identity(1,1),
the_product varchar(50) not null,
is_deleted int not null default 0
);


create unique index ux_product on product(the_product)
where is_deleted = 0;

insert into product(the_product) values('keyboard');
insert into product(the_product) values('mouse');
update product set is_deleted = 1 where the_product = 'mouse';

-- insert into product(the_product) values('keyboard'); -- won't be allowed

/* For all intents and purposes, the old mouse is 
deleted from the system, should still allow inserting new mouse. 
Prior to filtered index capability(available only on Sql Server 2008), 
having a unique column on table with marked deleted deleting strategy 
is an impossible implementation */
insert into product(the_product) values('mouse'); -- will be allowed

Paging for Linq

using System;
using System.Linq;


namespace WebHelpers.Extensions
{
    public static class Helpers
    {
        public static IQueryable<T> LimitAndOffset<T>(this IQueryable<T> q,      
                         int pageSize, int pageOffset)
        {
            return q.Skip((pageOffset - 1) * pageSize).Take(pageSize);
        }
    }
}

Usage, so instead of...

return View(_productsRepository.Products.OrderBy(x => x.Description)
            .Skip((page - 1) * PageSize).Take(PageSize)
            .ToList());

...which is very brittle, use this instead:

return View(_productsRepository.Products.OrderBy(x => x.Description)
            .LimitAndOffset(pageSize: PageSize, pageOffset: page)
            .ToList());

NUnit Testing:

[Test]
public void Is_paging_logic_correct()
{
 var p = new int[] { 1, 2, 3, 4, 5};

 var e = p.AsQueryable().LimitAndOffset(3, 2).ToArray();

 Assert.AreEqual(2, e.Count());
 Assert.AreEqual(4, e[0]);
 Assert.AreEqual(5, e[1]);
 
}

Use string.Join and Linq methods combo to facilitate easier unit testing

using System;
using System.Linq;

using Utilities;

using NUnit.Framework;




namespace TestTheUtilities
{
 [TestFixture]
 public class MyClass
 {  
  [Test]
  public void Fibonacci_is_ok()
  {
   Assert.AreEqual( "0,1,1,2,3,5,8",
               
         string.Join( ",", 
             Utils.FibonacciGenerator()
                  .Take(7).Select(x => x.ToString()).ToArray() )  

               );

   }

 } 
}

If it interest anyone, here's the fibonacci generator, just click to expand

using System;
using System.Collections.Generic;



namespace Utilities
{

 public static class Utils
 {
  public static IEnumerable<int> FibonacciGenerator ()
  {
   int prev = 0;
   int current = 1;
   
   for(;;)
   {
    yield return prev;
    
    int z = current;
    current = current + prev;
    prev = z;
   }
  }
 }
}

[EDIT]
The code above is not the easier way to compare collection, use CollectionAssert

Postgresql LAG windowing function

Given this:

create table t
(
ts timestamp not null,
code int not null
);

insert into t values
('2011-01-13 10:00:00', 5),
('2011-01-13 10:10:00', 5),
('2011-01-13 10:20:00', 5),
('2011-01-13 10:30:00', 5),
('2011-01-13 10:40:00', 0),
('2011-01-13 10:50:00', 1),
('2011-01-13 11:00:00', 1),
('2011-01-13 11:10:00', 1),
('2011-01-13 11:20:00', 0),
('2011-01-13 11:30:00', 5),
('2011-01-13 11:40:00', 5),
('2011-01-13 11:50:00', 3),
('2011-01-13 12:00:00', 3),
('2011-01-13 12:10:00', 3);

An stackoverflow user asked:

How can I select the first date of each set of identical numbers, so I end up with this:
2011-01-13 10:00:00, 5
2011-01-13 10:40:00, 0
2011-01-13 10:50:00, 1
2011-01-13 11:20:00, 0
2011-01-13 11:30:00, 5
2011-01-13 11:50:00, 3

I answered:

with sequencer as 
(
SELECT ROW_NUMBER() OVER(ORDER BY ts) seq, ts, Code
FROM T
)
select a.ts, a.Code
from sequencer a 
left join sequencer b on b.seq + 1 = a.seq 
where b.code IS DISTINCT FROM a.code;

But I'm not giving the problem a first-class treatment it deserves, I noticed somebody gave an answer that uses lag function, albeit he/she uses where prevcode <> code or prevcode is null

with r as
(
select ts, code, lag(code,1,null) over(order by ts) as prevcode
from t
)
select ts, code 
from r
where prevcode is distinct from code

Hmm.. :-) that code is very elegant, sleek and performant

http://www.postgresql.org/docs/8.4/static/functions-window.html

NHibernate saves your whole object graph even on cross-process scenario. NHibernate+WCF

One of the nicest thing with NHibernate is that it let you concentrate on your problem domain instead of doing low-level plumbing of saving your table(s)


And this is your ever-efficient and succint data component in action:

public int SaveQuestion(Question q)
{
 using (var s = SessionFactory.OpenSession())
 using (var tx = s.BeginTransaction())
 {                
  var qx = (Question)s.Merge(q);
  tx.Commit();
  return qx.QuestionId;
 }
}

And that will work even your data relationships are two or more level deep and still maintain its five-lines-ness regardless of how deep the rabbit hole goes table relationships are. Here are the entities and values, visualize Stackoverflow's database design, a question has many comments, a question has many answers, a given answer has many comments:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TheEntities;
using System.Runtime.Serialization;

namespace TheEntities
{

    [DataContract(IsReference=true)] 
    [KnownType(typeof(Question))]
    [KnownType(typeof(Answer))]
    [KnownType(typeof(QuestionComment))]
    public class Question
    {
        [DataMember] public virtual int QuestionId { get; set; }

        [DataMember] public virtual string TheQuestion { get; set; }
        [DataMember] public virtual string Poster { get; set; }

        [DataMember] public virtual IList<QuestionComment> Comments { get; set; }
        [DataMember] public virtual IList<Answer> Answers { get; set; }
    }

    [DataContract]    
    [KnownType(typeof(Question))]
    [KnownType(typeof(QuestionComment))]
    public class QuestionComment
    {

        [DataMember] public virtual Question Question { get; set; }

        [DataMember] public virtual int QuestionCommentId { get; set; }

        [DataMember] public virtual string TheQuestionComment { get; set; }
        [DataMember] public virtual string Poster { get; set; }
    }


    [DataContract(IsReference=true)]
    [KnownType(typeof(Question))]
    [KnownType(typeof(Answer))]
    [KnownType(typeof(AnswerComment))]
    public class Answer
    {
        [DataMember] public virtual Question Question { get; set; }

        [DataMember] public virtual int AnswerId { get; set; }

        [DataMember] public virtual string TheAnswer { get; set; }
        [DataMember] public virtual string Poster { get; set; }

        [DataMember] public virtual IList<AnswerComment> Comments { get; set; }
    }


    [DataContract]
    [KnownType(typeof(Answer))]
    [KnownType(typeof(AnswerComment))]
    public class AnswerComment
    {
        [DataMember] public virtual Answer Answer { get; set; }

        [DataMember] public virtual int AnswerCommentId { get; set; }

        [DataMember] public virtual string TheAnswerComment { get; set; }
        [DataMember] public virtual string Poster { get; set; }
    }


}

Following is the mapping (we used Fluent NHibernate instead of XML, so as not to muddle the main point of this topic ;-)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FluentNHibernate.LowercaseSystem;
using FluentNHibernate.Mapping;

namespace TheEntities.Mapper
{
    public class QuestionMap : ClassMap<Question>
    {
        public QuestionMap()
        {

            Not.LazyLoad();
            Id(x => x.QuestionId).GeneratedBy.Sequence("question_seq");

            Map(x => x.TheQuestion).Not.Nullable();
            Map(x => x.Poster).Not.Nullable();

            HasMany(x => x.Comments).Inverse().Not.LazyLoad().Cascade.AllDeleteOrphan();
            HasMany(x => x.Answers).Inverse().Not.LazyLoad().Cascade.AllDeleteOrphan();
        }
    }

    public class QuestionCommentMap : ClassMap<QuestionComment>
    {
        public QuestionCommentMap()
        {
            Not.LazyLoad();
            References(x => x.Question);    

            Id(x => x.QuestionCommentId).GeneratedBy.Sequence("question_comment_seq");

            Map(x => x.TheQuestionComment).Not.Nullable();
            Map(x => x.Poster).Not.Nullable();
        }
    }

    public class AnswerMap : ClassMap<Answer>
    {
        public AnswerMap()
        {
            Not.LazyLoad();
            References(x => x.Question);

            Id(x => x.AnswerId).GeneratedBy.Sequence("answer_seq");

            Map(x => x.TheAnswer).Not.Nullable();
            Map(x => x.Poster).Not.Nullable();

            HasMany(x => x.Comments).Inverse().Not.LazyLoad().Cascade.AllDeleteOrphan();
        }
    }

    public class AnswerCommentMap : ClassMap<AnswerComment>
    {
        public AnswerCommentMap()
        {
            Not.LazyLoad();
            References(x => x.Answer).Not.Nullable();

            Id(x => x.AnswerCommentId).GeneratedBy.Sequence("answer_comment_seq");

            Map(x => x.TheAnswerComment).Not.Nullable();
            Map(x => x.Poster).Not.Nullable();
        }
    }

}

Everything are pretty much standard routine (tagging DataContract, KnownType, DataMember attributes) to adhere to when you need to transport your POCO across the wire, those attributes has no bearing on NHibernate. The only point of interest is the IsReference(works out-of-the-box on .NET 4.0 or 3.5 SP1) property of DataContract attribute; you need to use it when your child table need to reference the parent table, failing to do so will result to this error:

Object graph for type 'X.Y.Z' contains cycles and cannot be serialized if reference tracking is disabled

That attribute signals the WCF not to serialize the whole contents of the object referenced by the child table, instead it will persist those multiple object references on parent entity (as referenced from child entities) with internal id, and this internal id has no bearing on database id nor NHibernate.



Another point of interest is the way the WCF interpret your collection, which by default is set to System.Array, you need to change that to System.Collections.Generic.List so you can add and delete records. If you just keep it as System.Array, the only thing you can do with records is updating them, adding and deleting record would not be available. This can be done by selecting Configure Service Reference...; this is under the Service References of your project



To visualize the sample entity relationships we are persisting, here's the screenshot:

Here's your data structure:

create table question (question_id int4 not null, the_question varchar(255) not null, poster varchar(255) not null, primary key (question_id));

create table question_comment (question_comment_id int4 not null, the_question_comment varchar(255) not null, poster varchar(255) not null, question_id int4, primary key (question_comment_id));

create table answer (answer_id int4 not null, the_answer varchar(255) not null, poster varchar(255) not null, question_id int4, primary key (answer_id));

create table answer_comment (answer_comment_id int4 not null, the_answer_comment varchar(255) not null, poster varchar(255) not null, answer_id int4 not null,
primary key (answer_comment_id));

alter table question_comment add constraint FK83AC3D002500E3C7 foreign key (question_id) references question;

alter table answer add constraint FK77FA76182500E3C7 foreign key (question_id) references question;

alter table answer_comment add constraint FKD5BEEC96136C8DAF foreign key (answer_id) references answer;

create sequence question_seq;
create sequence question_comment_seq;
create sequence answer_seq;
create sequence answer_comment_seq

Sql Server-compatible:

create table question (question_id INT IDENTITY NOT NULL, the_question NVARCHAR(255) not null, poster NVARCHAR(255) not null, primary key (question_id));
create table question_comment (question_comment_id INT IDENTITY NOT NULL, the_question_comment NVARCHAR(255) not null, poster NVARCHAR(255) not null, question_id INT null, primary key (question_comment_id));
create table answer (answer_id INT IDENTITY NOT NULL, the_answer NVARCHAR(255) not null, poster NVARCHAR(255) not null, question_id INT null, primary key (answer_id));
create table answer_comment (answer_comment_id INT IDENTITY NOT NULL, the_answer_comment NVARCHAR(255) not null, poster NVARCHAR(255) not null, answer_id INT not null, primary key (answer_comment_id));
alter table question_comment add constraint FK83AC3D002500E3C7 foreign key (question_id) references question;
alter table answer add constraint FK77FA76182500E3C7 foreign key (question_id) references question;
alter table answer_comment add constraint FKD5BEEC96136C8DAF foreign key (answer_id) references answer

Download proof of concept for NHibernate WCF



An aside, now I feel dirty I handcrafted SqlDataAdapter, Command, Parameters etc on my projects before. The complexity of persisting your data using the traditional ADO.NET approach is directly proportional to the complexity and number of your tables and its relationships, though the code generator I made have alleviated those problems a bit; but still, the code quality feels brittle. I had a desktop application that has 200 lines of code just to persist a 4 level deep table relationships. With NHibernate, your persistence code can still maintain its five-lines-ness regardless of complexities of your table relationships. I should have used NHibernate(or any good ORM for that matter) long time ago