LEFT JOIN GROUP BY HAVING COUNT on Entity Framework

Objective, create a Linq query that shows all persons with pay history count of zero or three on entities


This is how our Linq's query should be like in SQL:

select p.BusinessEntityID, p.FirstName, TheCount = count(ph.BusinessEntityId)
from Person.Person p
left join HumanResources.EmployeePayHistory ph on p.BusinessEntityID = ph.BusinessEntityID
group by p.BusinessEntityID, p.FirstName
having count(ph.BusinessEntityId) = 0 or count(ph.BusinessEntityId) = 3
order by TheCount

The two domain entities however doesn't belong in same aggregate..

namespace Domain
{
    public static class ThePerson
    {
        public class Person
        {
            public int    BusinessEntityId { get; set; }

            public string Title            { get; set; }
            public string FirstName        { get; set; }
            public string MiddleName       { get; set; }
            public string LastName         { get; set; }

            // public virtual ICollection<TheHumanResources.EmployeePayHistory> PayHistories { get; set; }
        }
    }
}


namespace Domain
{
    public static class TheHumanResources
    {
        public class EmployeePayHistory
        {
            public int                       BusinessEntityId { get; set; }
            public DateTime                  RateChangeDate   { get; set; }

            public virtual ThePerson.Person  Person           { get; set; }
            public decimal                   Rate             { get; set; } 
        }
    }
}


namespace DomainMapping
{
    public class TheDbContext : DbContext
    {
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<ThePerson.Person>().ToTable("Person.Person")
                .HasKey(x => x.BusinessEntityId);
                // .HasMany(x => x.PayHistories).WithRequired().HasForeignKey(x => x.BusinessEntityId);

                
               


            modelBuilder.Entity<TheHumanResources.EmployeePayHistory>()
                .ToTable("HumanResources.EmployeePayHistory")
                .HasKey(x => new { x.BusinessEntityId, x.RateChangeDate } )
                .HasRequired(x => x.Person)
                .WithRequiredDependent()
                .Map(m => m.MapKey("BusinessEntityID"));

        }
    }
}

..hence we can't make a simpler query like this:

var query =
    from person in ctx.Set<ThePerson.Person>().SelectMany(x => x.PayHistories.DefaultIfEmpty(), (person, payHistory) => new { person, payHistory })
    
    group person by new { person.person.BusinessEntityId, person.person.FirstName } into persons
    
    let TheCount = persons.Sum(x => x.payHistory != null ? 1 : 0)
    
    where TheCount == 0 || TheCount == 3
    
    orderby TheCount
    
    select new { persons.Key.BusinessEntityId, persons.Key.FirstName, TheCount };

Output:

SELECT
    [GroupBy1].[K1] AS [BusinessEntityId],
    [GroupBy1].[K2] AS [FirstName],
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT
        [Join1].[K1] AS [K1],
        [Join1].[K2] AS [K2],
        SUM([Join1].[A1]) AS [A1]
        FROM ( SELECT
            [Extent1].[BusinessEntityId] AS [K1],
            [Extent1].[FirstName] AS [K2],
            CASE WHEN ( NOT (([Extent2].[BusinessEntityId] IS NULL) AND ([Extent2].[RateChangeDate] IS NULL))) THEN 1 ELSE 0 END AS [A1]
            FROM  [Person].[Person] AS [Extent1]
            LEFT OUTER JOIN [HumanResources].[EmployeePayHistory] AS [Extent2] ON [Extent1].[BusinessEntityId] = [Extent2].[Person_BusinessEntityId1]
        )  AS [Join1]
        GROUP BY [K1], [K2]
    )  AS [GroupBy1]
    WHERE [GroupBy1].[A1] IN (0,3)
    ORDER BY [GroupBy1].[A1] ASC

Following is easy to read Linq with left join group by and having, but it's not efficient, this creates a subquery in SELECT:

var query = from person in ctx.Set<ThePerson.Person>()

            join payHistory in ctx.Set<TheHumanResources.EmployeePayHistory>() on person.BusinessEntityId equals payHistory.BusinessEntityId into payHistories
            from payHistoryNullable in payHistories.DefaultIfEmpty()

            let TheCount = payHistories.Count()

            where TheCount == 0 || TheCount == 3

            orderby TheCount ascending

            select new { person.BusinessEntityId, person.FirstName, TheCount };

Output:

SELECT
    [Project1].[BusinessEntityId] AS [BusinessEntityId],
    [Project1].[FirstName] AS [FirstName],
    [Project1].[C1] AS [C1]
    FROM ( SELECT
        [Extent1].[BusinessEntityId] AS [BusinessEntityId],
        [Extent1].[FirstName] AS [FirstName],
        -- subquery        
        (SELECT
            COUNT(1) AS [A1]
            FROM [HumanResources].[EmployeePayHistory] AS [Extent3]
            WHERE [Extent1].[BusinessEntityId] = [Extent3].[BusinessEntityId]) AS [C1]
        FROM  [Person].[Person] AS [Extent1]
        LEFT OUTER JOIN [HumanResources].[EmployeePayHistory] AS [Extent2] ON [Extent1].[BusinessEntityId] = [Extent2].[BusinessEntityId]
    )  AS [Project1]
    WHERE [Project1].[C1] IN (0,3)
    ORDER BY [Project1].[C1] ASC

Not as readable as the Linq query above, but this is better as this uses GROUP BY:

var query = from person in ctx.Set<ThePerson.Person>()

            join payHistory in ctx.Set<TheHumanResources.EmployeePayHistory>() on person.BusinessEntityId equals payHistory.BusinessEntityId into payHistories
            from payHistoryNullable in payHistories.DefaultIfEmpty()

            group payHistoryNullable by new { person.BusinessEntityId, person.FirstName } into payHistoriesNullable

            let TheCount = payHistoriesNullable.Sum(eachPayHistoryNullable => eachPayHistoryNullable != null ? 1 : 0)

            where TheCount == 0 || TheCount == 3

            orderby TheCount ascending
            select new { payHistoriesNullable.Key.BusinessEntityId, payHistoriesNullable.Key.FirstName, TheCount };

Output:

SELECT
    [GroupBy1].[K1] AS [BusinessEntityId],
    [GroupBy1].[K2] AS [FirstName],
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT
        [Join1].[K1] AS [K1],
        [Join1].[K2] AS [K2],
        SUM([Join1].[A1]) AS [A1]
        FROM ( SELECT
            [Extent1].[BusinessEntityId] AS [K1],
            [Extent1].[FirstName] AS [K2],
            CASE WHEN ( NOT (([Extent2].[BusinessEntityId] IS NULL) AND ([Extent2].[RateChangeDate] IS NULL))) THEN 1 ELSE 0 END AS [A1]
            FROM  [Person].[Person] AS [Extent1]
            LEFT OUTER JOIN [HumanResources].[EmployeePayHistory] AS [Extent2] ON [Extent1].[BusinessEntityId] = [Extent2].[BusinessEntityId]
        )  AS [Join1]
        -- efficient, uses GROUP BY 
        GROUP BY [K1], [K2] 
    )  AS [GroupBy1]
    WHERE [GroupBy1].[A1] IN (0,3)
    ORDER BY [GroupBy1].[A1] ASC            

Happy Coding!

SELECT BOTTOM with Linq

Added Last/LastOrDefault functionality in our custom ORM, so we can now do things in idiomatic way, e.g., getting the last country alphabetically.

Non-idiomatic, we are getting the last country, yet we are using FirstOrDefault:

var lastCountry = countryDao.Context.OrderByDescending(x => x.CountryName).FirstOrDefault();

Idiomatic way to get the last row:

var lastCountry = countryDao.Context.OrderBy(x => x.CountryName).LastOrDefault();

Both of the codes above resolves to SELECT TOP ORDER BY DESC, as there's no SELECT BOTTOM in SQL:

SELECT TOP 1 c.CountryId, c.CountryName, c.Population
FROM Countries c
ORDER BY c.CountryName DESC

I naturally assumed NHibernate and Entity Framework supports Last/LastOrDefault, just tried them now, but they haven't supported it yet

Linq's Composability and Reusability

Nothing can beat the composability and reusability of Linq, it enables one-stop-shop coding.


Case in point, getting a page from a filtered list and getting the total count of the filtered list is just a walk in the park with Linq, they can all be done on one spot.

using (var session = TheMapper.GetSessionFactory().OpenSession())
using (var tx = session.BeginTransaction())
{
    var products = session.Query<Product>();

    if (filter.ProductName != null) 
        products = products.Where(x => x.Name.Contains(filter.ProductName));                

    if (filter.ProductModelName != null)
        products = products.Where(x => x.ProductModel.Name.Contains(filter.ProductModelName));


    var result = new ProductResultDto();                
    
    result.Products =
            products
            .OrderBy(x => x.Name)
            .Take(filter.PageSize).Skip((filter.PageNumber - 1) * filter.PageSize)
            .Select(x => new ProductDto { ProductName = x.Name, ProductModelName = x.ProductModel.Name })
            .ToList();

    result.Count = products.Count();

    return result;                
}//using

How it is done when writing queries directly on database instead:

create procedure GetList(
    @ProductName nvarchar(400),
    @ProductModelName nvarchar(400),
    @PageNumber int,
    @PageSize int
)
as
begin


    declare @rn int = (@PageNumber-1) * @PageSize;

    with a as
    (
        select 
            RN = row_number() over(order by p.Name),
            ProductName = p.Name, ProductModelName = m.Name
        from Production.Product p
        left join Production.ProductModel m on p.ProductModelID = m.ProductModelID
        where 
            (
                @ProductName is null 
                or p.Name like '%' + @ProductName + '%'
            )
            and
            (
                @ProductModelName is null 
                or m.Name like '%' + @ProductModelName + '%'
            )
    )
    select 
        top (@PageSize)
        a.ProductName, a.ProductModelName 
    from a
    where a.rn > @rn;


    select count(*) as cnt
    from Production.Product p
    left join Production.ProductModel m on p.ProductModelID = m.ProductModelID
    where 
        (
            @ProductName is null 
            or p.Name like '%' + @ProductName + '%'
        )
        and
        (
            @ProductModelName is null 
            or m.Name like '%' + @ProductModelName + '%'
        );


end;

The above code is not DRY, you can see the filter being repeated in two places. We can avoid wetting the query by refactoring it:

create function GetList_Func(
    @ProductName nvarchar(400),
    @ProductModelName nvarchar(400)
) returns table
as
return
    select 
        RN = row_number() over(order by p.Name),
        ProductName = p.Name, ProductModelName = m.Name
    from Production.Product p
    left join Production.ProductModel m on p.ProductModelID = m.ProductModelID
    where 
        (
            @ProductName is null 
            or p.Name like '%' + @ProductName + '%'
        )
        and
        (
            @ProductModelName is null 
            or m.Name like '%' + @ProductModelName + '%'
        );
go



create procedure GetList_Refactored(
    @ProductName nvarchar(400),
    @ProductModelName nvarchar(400),
    @PageNumber int,
    @PageSize int
)
as
begin

    declare @rn int = (@PageNumber-1) * @PageSize;
    
    select 
        top (@PageSize)
        a.ProductName, a.ProductModelName 
    from GetList_Func(@ProductName, @ProductModelName) a
    where a.rn > @rn;

    select count(*) as cnt
    from GetList_Func(@ProductName, @ProductModelName);

end;

go

However, it's not one-stop-shop anymore. The query is now being defined in two places.



Happy Computing! ツ


One-stop-shop code:

namespace OrmFtw
{

    using System;
    using System.Collections.Generic;
    using System.Linq;

    using NHibernate.Linq;

    using OrmFtw.Models;
    using OrmFtw.Mapper;


    class Program
    {
        static void Main(string[] args)
        {
            // var result = GetList(new FilterRequest { ProductName = "Black", PageSize = 10, PageNumber = 2 });
            var result = GetList(new FilterRequest { ProductModelName = "Frame", PageSize = 10, PageNumber = 2 });

            Console.WriteLine("Total: {0}", result.Count);
            Console.WriteLine("Second Page: ");
            foreach (var prod in result.Products)
            {
                Console.WriteLine("Product: {0}", prod.ProductName);
                Console.WriteLine("Model: {0}", prod.ProductModelName);
            }

            Console.ReadKey();
        }

        private static ProductResultDto GetList(FilterRequest filter)
        {
            using (var session = TheMapper.GetSessionFactory().OpenSession())
            using (var tx = session.BeginTransaction())
            {
                var products = session.Query<Product>();

                if (filter.ProductName != null)
                    products = products.Where(x => x.Name.Contains(filter.ProductName));

                if (filter.ProductModelName != null)
                    products = products.Where(x => x.ProductModel.Name.Contains(filter.ProductModelName));


                var result = new ProductResultDto();

                result.Products =
                        products
                        .OrderBy(x => x.Name)
                        .Take(filter.PageSize).Skip((filter.PageNumber - 1) * filter.PageSize)
                        .Select(x => new ProductDto { ProductName = x.Name, ProductModelName = x.ProductModel.Name })
                        .ToList();

                result.Count = products.Count();

                return result;
            }//using

        }//GetList()
    }

    public class FilterRequest
    {
        public string ProductName { get; set; }
        public string ProductModelName { get; set; }

        public int PageNumber { get; set; }
        public int PageSize { get; set; }
    }

    public class ProductResultDto
    {
        public int Count { get; set; }

        public IList<ProductDto> Products { get; set; }
    }

    public class ProductDto
    {
        public string ProductName { get; set; }
        public string ProductModelName { get; set; }
    }

}



namespace OrmFtw.Models
{
    class Product
    {
        public virtual int ProductID { get; set; }
        public virtual string Name { get; set; }
        public virtual string ProductNumber { get; set; }

        public virtual ProductModel ProductModel { get; set; }
    }
}


namespace OrmFtw.Models
{

    public class ProductModel
    {
        public virtual int ProductModelID { get; set; }
        public virtual string Name { get; set; }
    }
}

namespace OrmFtw.ModelMappings
{
    using NHibernate.Mapping.ByCode.Conformist;
    using OrmFtw.Models;

    class ProductMapping : ClassMapping<Product>
    {
        public ProductMapping()
        {
            Table("Production.Product");
            Id(x => x.ProductID);
            Property(x => x.Name);
            Property(x => x.ProductNumber);


            ManyToOne(x => x.ProductModel, x => x.Column("ProductModelID"));

        }
    }
}


namespace OrmFtw.ModelMappings
{

    using NHibernate.Mapping.ByCode.Conformist;
    using OrmFtw.Models;

    class ProductModelMapping : ClassMapping<ProductModel>
    {
        public ProductModelMapping()
        {
            Table("Production.ProductModel");
            Id(x => x.ProductModelID);
            Property(x => x.Name);
        }
    }
}



namespace OrmFtw.Mapper
{
    using NHibernate;
    using NHibernate.Cfg;
    using NHibernate.Mapping.ByCode;
    using NHibernate.Cfg.MappingSchema;

    using OrmFtw.ModelMappings;

    public class TheMapper
    {
        static ISessionFactory _sessionFactory;
        public static ISessionFactory GetSessionFactory()
        {
            if (_sessionFactory != null)
                return _sessionFactory;

            var cfg = new Configuration();
            var mapper = new ModelMapper();

            mapper.AddMappings(
                new[] {
                    // Entities
                    typeof(ProductMapping), 
                    typeof(ProductModelMapping)
                });



            cfg.DataBaseIntegration(c =>
            {
                c.Driver<NHibernate.Driver.Sql2008ClientDriver>();
                c.Dialect<NHibernate.Dialect.MsSql2012Dialect>();
                c.ConnectionString = "Server=localhost; Database=AdventureWorks2012; Trusted_Connection=true;";

                c.LogFormattedSql = true;
                c.LogSqlInConsole = true;
            });


            HbmMapping domainMapping = mapper.CompileMappingForAllExplicitlyAddedEntities();

            cfg.AddMapping(domainMapping);

            _sessionFactory = cfg.BuildSessionFactory();

            return _sessionFactory;
        }


    }

}

Safe Navigation via Linq

AdventureWorks product's model is nullable. Null exception will be raised when we navigate the Name of the ProductModel from the Product.

var products = session.Query<Product>().Fetch(x => x.ProductModel);

foreach (var prod in products)
{
        Console.WriteLine("Product: {0}", prod.Name);
   
        // this will raise a null exception when Production.Product.ProductModelID is null
        Console.WriteLine("Model: {0}", prod.ProductModel.Name); 
}

Aside from using null-safe navigation extension method...

var products = session.Query<Product>().Fetch(x => x.ProductModel);

foreach (var prod in products)
{
        Console.WriteLine("Product: {0}", prod.Name);
        Console.WriteLine("Model: {0}", prod.ProductModel.NullSafe(x => x.Name));
}

...we can just flatten the domain models from Linq's projection, the database won't balk on navigating nullable product model from the product, then the ORM can happily map the null value to the property.

var products = session.Query<Product>()
                    // .Fetch(x => x.ProductModel) // This is not needed anymore, as we efficiently select only the needed columns on our projection
                    .Select(x => 
                            new { 
                                 ProductName = x.Name, 
                                 ProductModelName = x.ProductModel.Name // database don't have problem projecting null
                            });

foreach (var prod in products)
{
        Console.WriteLine("Product: {0}", prod.ProductName);
        Console.WriteLine("Model: {0}", prod.ProductModelName);
}

We can't do that trick on in-memory objects, for those scenario just use safe navigation extension method.



Happy Coding! ツ

Returning strongly-typed distinct objects from a function without the drama of GetHashCode and Equals

On my last post, I detailed how to tame Distinct. The following does exactly that without the drama of GetHashCode and Equals. You might still need GetHashCode and Equals if you are using ORMs. However, for in-memory operations, a GroupBy approach would suffice for a distinct operation:

using System;
using System.Linq;
using System.Collections.Generic;
 
public class Test
{
        public static void Main()
        {
                var list = new List<Fact> () {
                            new Fact { Thing = "Roses are", Color = 0xFF0000 },
                                    new Fact { Thing = "Roses are", Color = 0xFF0000 },
                                    new Fact { Thing = "Violets are", Color = 0x0000FF },
                                    new Fact { Thing = "Sugar is", Color = 0x1337 },
                                    new Fact { Thing = "Sugar is", Color = 0x1337 },
                            };                            
                foreach(var item in GetFacts(list)) {
                    Console.WriteLine ("{0:x} {1:x}", item.Thing, item.Color);
                }
        }
        
        public static IEnumerable<Fact> GetFacts(IEnumerable<Fact> list) {
            return list.GroupBy(x => new { x.Thing, x.Color }, (key,group) => group.First());
        }
}
 
public class Fact
{
    public string Thing { get; set; }
    public int Color { get; set; }
}

Roses are ff0000
Violets are ff
Sugar is 1337

To some, the above distinct construct maybe obvious, but rediscovering what Linq/lambda can do gives me a feeling of impostor syndrome, especially I'm way past the 10,000 hours on this beautiful craft called programming. Well, I'll just heed Scott Hanselman's advice, read the advice at the last part of this article: http://www.hanselman.com/blog/ImAPhonyAreYou.aspx


Live code: http://ideone.com/uWtZrT


If Linq is your thing, you can also implement the above code the following way:

public static IEnumerable<Fact> GetFacts(IEnumerable<Fact> list) 
{
    return 
        from x in list
        group x by new { x.Thing, x.Color } into grp
        select grp.First();                 
}

Live Code: http://ideone.com/J0PzRN

Output:

Roses are ff0000
Violets are ff
Sugar is 1337

If you are not a technology bigot, you won't be snotty on pure Linq or pure lambda approach, you will use the right tool for the job, in fact you won't mind mixing Linq and lambda in one statement, especially if this approach gives you the right balance on code maintainability and readability:

public static IEnumerable<Fact> GetFacts(IEnumerable<Fact> list)
{
     return 
         from x in list.GroupBy(x => new {x.Thing, x.Color })                 
         select x.First();                      
}

Live Code: http://ideone.com/tVMHS6

Output:

Roses are ff0000
Violets are ff
Sugar is 1337

And then there's another approach:

public static IEnumerable<Fact> GetFacts(IEnumerable<Fact> list) 
{
    return list.GroupBy(x => new {x.Thing, x.Color }).Select(x => x.First());                  
}

Live Code: http://ideone.com/9aUl4W

Output:

Roses are ff0000
Violets are ff
Sugar is 1337

This is why many folks called programming a subjective, er.. scratch that, an art thing. There are way too many approaches to achieve a common goal. Beauty is in the eye of the beholder and so they say.


If I become blind and my profession is still programming, the last thing I would want to do is to write things in pure lambda approach and forces me to make sure that every parenthesis are properly matched, lambda syntax won't let me fluently phrase the logic in the most natural way, it's not friendly to the finger joints too as one needs to incessantly press the shift key when typing fat arrows and parenthesis. Using Linq syntax instead of lambda, would allow me to write logic fluently, I can just fluently write a code without incessantly worrying if the parenthesis matches, there's little to no parenthesis if one goes the Linq approach, and it's not discriminating to people with impaired vision.


To sway you to avoid exclusively writing in Lambda approach and sell you on using Linq(plus mixing some lambda here and there if it would make sense and make the code readable) approach instead, imagine you are the protagonist in the movie Crank, if you are not able to see an output as fast as you needed to see, your heart rate will drop, then you will die in the process.

And so Jason Statham write the join construct using Lambda approach:

var categorizedProducts = product
    .Join(productcategory, p => p.Id, pc => pc.ProdId, (p, pc) => new { p, pc })
    .Join(category, ppc => ppc.CatId, c => c.Id, (ppc, c) => new { ppc, c }))
    .Select(m => new { 
        ProdId = m.ppc.Id, 
        CatId = m.c.CatId
        // other assignments
    });

And much to his chagrin, when he hit build, there are errors that indicates some properties cannot be found on object, and to add insult to his heart injury, the compiler says there's a syntax error, the frustration sets in, then his heart rate drops, then he die. If you cannot spot the errors on the above lambda construct, you shall die too! lol


To spot where the error on the above lambda code is, get the right code here: http://stackoverflow.com/questions/9720225/how-to-perform-join-between-multiple-tables-in-linq-lambda/9722744#9722744, then use the Diff plugin of Notepad++ to compare the code above to the right code. That has only two joins, but the navigation for ProdId is already three dots away(i.e., m.ppc.p.Id), then imagine if the above code has 5+ joins, property navigation will become way too deep, the volume of dots on the lambda approach could put to shame the amount of parenthesis in a typical Lisp code


Writing code in lambda would not make a good Crank movie script, the protagonist will die way too early. Upon realizing that grave mistake, the scriptwriter rewrite the part where Jason Statham is writing the join construct using lambda syntax; on the revised script, Jason Statham is writing the join construct using Linq syntax instead:

var categorizedProducts =
    from p in product
    join pc in productcategory on p.Id equals pc.ProdId
    join c in category on pc.CatId equals c.Id
    select new {
        ProdId = p.Id, // or pc.ProdId
        CatId = c.CatId
        // other assignments
    };

He was able to produce results with the above Linq code in no time, his heart rate didn't drop a bit, and for that Linq code he was able to survive and made the sequel a possibility. I think on the sequel he wrote AngularJS code instead of jQuery lol :D


Happy Computing! ツ

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

Linq's syntax symmetry with Postgres

Can't help but notice, Linq multiple condition has more affinity with Postgres flexible syntax:

from c in Customers
join o in Orders on new {c.CompanyID, c.CustomerID} equals new {o.CompanyID, o.CustomerID}

Postgresql:

from Customers as c 
join Orders as o on (c.CompanyID,c.CustomerID) = (o.CompanyID,o.CustomerID)

Linq is the new Stored Procedure

After reading Rob Conery's http://blog.wekeroad.com/2009/06/11/youre-not-your-data-access And seeing this Aaron Bertrand's comment on stackoverflow:

Then I suspect brainwashing may have been involved

I believe that .NET developers should practice Buddhism.

Most .NET developers don't know the middle path. If they chosen Stored Procedures, they will decidedly use stored procedures 100% exclusively, SP foreva! to the detriment on excluding other sound techniques. They forgot to be pragmatic, overlooking the fact that some solutions are meant to be solved in a certain way. If you have flexible query filters, instead of making a stored procedure with gazillion parameters, it's better you use techniques or DAL that is a perfect fit for that. Linq perfectly fits the bill and it is type-safe to boot, as it allows you to chain filters in any way you deem fit, and consequently, making the system performant.

...
var query = MemberRepository.All;

if (lastnameFilter != "")
    query = query.Where(x => x.Lastname == lastnameFilter);
 
if (firstnameFilter != "")
    query = query.Where(x => x.Firstname == firstnameFilter);
 
if (memberSinceFilter != 0)
    query = query.Where(x => x.YearMember >= memberSinceFilter);
 
if (pointsFilter != 0)
    query = query.Where(x => x.Points >= pointsFilter);
 

return query.ToList(); 
... 

If you only have one filter, example on points only, that would generate this short query only:

select * 
from Member 
where Points >= 10000;

Whereas if you use stored procedure, this is how that will be tackled:

create procedure MemberSearch(@Lastname varchar(50), @Firstname varchar(50), @MemberSince int, @Points int)
as

select * 
form Member
where (@Lastname = '' OR Lastname = @Lastname)
      AND (@Firstname = '' OR Firsname = @Firstname)
   AND (@MemberSince = 0 OR YearMember >= @memberSince)
   AND (@Points = 0 OR Points >= @Points)

go

I would hazard a guess, most prefers the Linq approach now, as it produces a performant query. If you filter only on specific column(s), other columns will not be included in the query. Linq is my preferred approach too.


Sadly though, even how awesome Linq is, I'm feeling there's a new Stored Procedure in town. Linq is the new Stored Procedure! Don't be confused I'm praising Linq one second and bashing it another. Let me tell you the premise of this article.

Upon seeing this question: http://stackoverflow.com/questions/11058330/select-collection-of-entities-based-on-value-of-most-recent-related-entity

And seeing this comments:

This is close to the output I am looking for. I want where the most recent action is an open, not the most recent open, but to get that, I would just have to move the Status = Open condition to the second select. The bigger issue, though, is that I am being "strongly encouraged" to use Entity to communicate with the DB instead of directly querying it. – user1459547

For what purpose? If it is harder to make EF generate the query you need, and you know how to do it in SQL... – Aaron Bertrand

I am entirely on your side, but I can't convince the project manager. – user1459547

Then I suspect brainwashing may have been involved... – Aaron Bertrand

It eludes me why the need to impose rules or be dogmatic that all data access should be in Linq, when a straightforward SQL is as readable and more performant than Linq-generated SQL.

I would rather see this operate in production:

;WITH MostRecentActions AS 
(
  SELECT RequestID, ActionTimestamp,
    /* other relevant RequestAction columns, */
    rn = ROW_NUMBER() OVER (PARTITION BY RequestID ORDER BY ActionTimestamp DESC)
  FROM dbo.RequestActions
  WHERE Status = 'Open'
)
SELECT RequestID, ActionTimestamp /* , other columns */ 
  FROM MostRecentActions
  WHERE rn = 1;      

Than this complex Linq-generated one:

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[SomeProp1] AS [SomeProp1], 
[Extent1].[SomeProp2] AS [SomeProp2], -- ...etc.
FROM  [dbo].[Requests] AS [Extent1]
CROSS APPLY  (SELECT TOP (1) [Project1].[Status] AS [Status]
    FROM ( SELECT 
           [Extent2].[Status] AS [Status], 
           [Extent2].[ActionTimestamp] AS [ActionTimestamp]
           FROM [dbo].[RequestActions] AS [Extent2]
           WHERE [Extent1].[Id] = [Extent2].[RequestId]
    ) AS [Project1]
    ORDER BY [Project1].[ActionTimestamp] DESC ) AS [Limit1]
WHERE N'Open' = [Limit1].[Status]

I have an inkling that code is slow compared to the first one(the CTE approach). Ok, an astute dev might actually profile and try these two codes and may found that the latter code is faster, but that is besides the point. The point is why should we impose dogmatic rules, when you can choose an approach that you can implement quickly and works. Sure, some Linq problems are just an stackoverflow away, but don't forget that you can still drop to bare metal SQL if you want to have more confidence the solution works and as smoothly as you wanted them be. Don't be overzealous on a given platform or framework, don't say "Stored Procedure Forever!" Don't say "Stored Procedure is Dead! Long Live Linq!"


Everything should be in moderation, don't be overzealous on one choice only. Programmers hate -ism, but let me take this as an exception, programmers should sometimes practice Buddhism


In line with pragmatism and to avoid becoming overzealous on certain decisions, the only sound bite that should be tolerated is:

Sound bites considered harmful

Context here: http://blog.securemacprogramming.com/?p=462

Counting properly with database Linq/lambda

This query is not translated to back-end version, materializing any of the class in a linq expression will result in all dependent objects being materialized to in-memory objects, and that includes the list

db.Set<Order>().Single(x => x.OrderId == 1).Customer.Country.Languages.Count

To make that efficient, do that query in this manner. Do note that none of the classes are materialized to an object:

int c = (from x in db.Set<Order>()
         where x.OrderId == 1
         from l in x.Customer.Country.Languages
         select l).Count();

Here's the query generated by that:

SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
 COUNT(1) AS [A1]
 FROM   (SELECT [Extent1].[Customer_CustomerId] AS [Customer_CustomerId]
  FROM [dbo].[Order] AS [Extent1]
  WHERE 1 = [Extent1].[OrderId] ) AS [Filter1]
 CROSS APPLY  (SELECT [Extent2].[AssocCountryId] AS [AssocCountryId]
  FROM  [dbo].[LanguageAssocCountry] AS [Extent2]
  INNER JOIN [dbo].[Customer] AS [Extent3] ON [Extent3].[Country_CountryId] = [Extent2].[AssocCountryId]
  WHERE [Filter1].[Customer_CustomerId] = [Extent3].[CustomerId] ) AS [Filter2]
)  AS [GroupBy1]

If you want to simplify the Linq, don't do it this way, this will materialize the dependent object and rows, as the very first object(via Single) was materialized already, not efficient:

int c = (from l in db.Set<Order>().Single(x => x.OrderId == 1).Customer.Country.Languages
         select l).Count()

To shorten it up a bit, do it this way, none of the classes is materialized to object, this is efficient:

int c = (from x in db.Set<Order>().Where(x => x.OrderId == 1)
         from l in x.Customer.Country.Languages
         select l).Count()

Query-wise, that code generates exactly the same query as the first Linq code of this post.

If you want to write it in pure lambda approach, use the following code. This code also generates the exact same query of the second Linq code of this post:

int c = db.Set<Order>().Where(x => x.OrderId == 1)
          .SelectMany(x => x.Customer.Country.Languages).Count();

This is the generated query when you use NHibernate ( just change the db.Set<Order>() to db.Query<Order>() ):

exec sp_executesql 
N'select cast(count(*) as INT) as col_0_0_ 
from [Order] order0_ 
inner join [Customer] customer1_ on order0_.Customer_CustomerId=customer1_.CustomerId 
inner join [Country] country2_ on customer1_.Country_CountryId=country2_.CountryId 
inner join LanguageAssocCountry languages3_ on country2_.CountryId=languages3_.AssocCountryId 
inner join [Language] language4_ on languages3_.AssocLanguageId=language4_.LanguageId 
where order0_.OrderId=@p0',N'@p0 int',@p0=1

When is null is not a null? This is not a philosophical SQL question :-)

Answer: When it is still inside of Linq Expression


Linq operation is one of the rare occasion where C# semantics is not of WYSIWYG one.

var employees =
from x in db.Employees
where x.Manager == null
orderby x.LastName
select new { x.FirstName, x.LastName, x.Manager, ManagerName = x.Manager.LastName };
 
GridView1.DataSource = employees;
GridView1.DataBind();

Given the code above, even if x.Manager is null (or just look like one) the code x.Manager.LastName is not an absurd one. x.Manager == null has no bearing on C# nullability. As far as an ORM is concerned, x.Manager == null needed be translated to its equivalent relational syntax, i.e. that code needed be translated to table2.EmployeeId IS NULL. An ORM is able to do that, as the ORM's mappings contains info on what columns connect one table to another table. The x.Manager.LastName in Linq's select clause is also being translated by Linq Expression provider(e.g. ORM), that's why there won't be any null runtime error on x.Manager.LastName when the LastName is being accessed.


Continuing the code above, when using AsEnumerable this code's null..

var z =
from y in employees.AsEnumerable()
where y.Manager == null
select new { y.FirstName, y.LastName, y.Manager, ManagerName = y.Manager.LastName };

GridView1.DataSource = z;
GridView1.DataBind();


..is now a C#'s null. This code will have a null runtime exception on y.Manager.LastName then.



Perhaps to make code analysis a lot simpler, any code that is still in scope of Linq's Expression should be thought of not as a C#'s construct, and as such, are still subject for translation to whatever SQL syntax it needed be translated, C#'s language rules doesn't come into play when an Expression is still in scope. Linq Expression provider could even translate y.Manager == null to any kind of code; much like C++/C# operator overloading, a plus operator could mean minus, a minus could mean plus. And as far as Linq Expression provider is concerned, the code select new { y.FirstName, y.LastName, y.Manager, ManagerName = y.Manager.LastName } could be translated up to the Linq provider's liking and such can be translated to any kind of code, y.Lastname could be automatically translated to an expression that automatically lowercases, e.g. SELECT Lastname = LCASE(LastName), to even a random one :-)


C# rules could only take effect when things are materialized to objects already, e.g. when using AsEnumerable, ToArray, ToList.


This is the ideal Linq code for properties(e.g. FullName from concatenation in C#) in classes that are not present in database:

var employees =
    (from x in db.Employees
    where x.Manager == null
    orderby x.LastName
    select x) // code here and up are being translated to back-end equivalent, i.e. SQL
 
 
    // code here and below, be it Linq-style or Lambda-style, now follows the normal C# rules,
    // note the null coalesce (??) operator
    .AsEnumerable()
    .Select(y => new
        {
        y.FullName, // Lastname and Firstname is concatenated on C# getter
        ManagerName = (y.Manager ?? new NorthwindModel.Employee()).FullName
    }); 

Max problem with empty IQueryable

Max receives an error on IQueryable if it has no rows

decimal ny = _repoProduct.GetAll()
    .Where(x => x.ProductId != x.ProductId)
    .Max(x => x.MinimumPrice);

On IQueryable obtained from array or anything in-memory list, this is the error:

System.InvalidOperationException: Sequence contains no elements

On IQueryable obtained from NHibernate:

NHibernate.Exceptions.GenericADOException: Could not execute query[SQL: SQL not available] ---> System.ArgumentNullException: Value cannot be null.

On IQueryable obtained from Entity Framework:

System.InvalidOperationException: The cast to value type 'Decimal' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.

The easiest way to rectify that problem is to make the MinimumPrice in your model class as nullable decimal. But that could be a problem if your views has dependent logic on the nullness of the property of your model. Another approach is to cast the target element to nullable type before passing it to aggregate function.

decimal nx = _repoProduct.GetAll()
    .Where(x => x.ProductId != x.ProductId)
    .Select(x => new { TheMinimumPrice = (decimal?)x.MinimumPrice })
    .Max(x => x.TheMinimumPrice) ?? 0;

Since we have only one element on Select, the Linq can be shortened to:

decimal nx = _repoProduct.GetAll()
    .Where(x => x.ProductId != x.ProductId)
    .Select(x => (decimal?)x.MinimumPrice)
    .Max() ?? 0;

Make your query malleable with Linq

Perhaps you've already seen/made this query pattern in your coding career:

SELECT *
FROM SchoolOfThought
WHERE
 (
  @inputTeacher = ''
  OR 
  Teacher = @inputTeacher
 )
 AND
 (
  @inputSubject = ''
  OR
  Subject = @inputSubject
 )
 AND
 (
  @inputQuality IS NULL
  OR
  Quality = @inputQuality
 ) 

That query is synonymous with this approach:

string inputTeacher = Console.ReadLine();
string inputSubject = Console.ReadLine();
string inputQuality = Console.ReadLine();

string filter = "";

if (inputTeacher != "")
 filter = string.Format("Teacher = '{0}'", inputTeacher);
 
if (inputSubject != "")
 filter = filter + (filter.Length != 0 ? " AND " : "" ) + string.Format("Subject = '{0}'", inputSubject);
 
if (inputQuality != "")
 filter = filter + (filter.Length != 0 ? " AND " : "" ) + string.Format("Subject = {0}", int.Parse(inputQuality));
 
string query = "SELECT * FROM SchoolOfThought " + (filter.Length != 0 ? "WHERE " + filter : "");

If the user has input on subject only, say Math, the resulting query is shorter:

SELECT  *
FROM  SchoolOfThought
WHERE Subject = 'Math';

If the user didn't input anything, The resulting query is much shorter:

SELECT  *
FROM  SchoolOfThought;

Even though concatenation approach is performant and lighter on network traffic(but don't do micro-optimizations), you will eschew the concatenation approach in favor of the first code. The reasons are twofold; first, you can't guarantee that you can safeguard your query from SQL-injection; second, code-review-wise, you don't want your code be flagged as having a code smell, the second approach is longer and look every bit as brittle.




Now, everything changes when Linq came to the scene, we no longer have to write our query patterned after the first code. We could now write the code below with a warm and fuzzy feeling that there will be no SQL-injection that could accidentally creep in the code. Lambda-using-Linq:

string inputTeacher = Console.ReadLine();
string inputSubject = Console.ReadLine();
string inputQuality = Console.ReadLine();

var query = s.Query<SchoolOfThought>();

if (inputTeacher.Length > 0)
 query = query.Where(x => x.Teacher == inputTeacher);

if (inputSubject.Length > 0)
 query = query.Where(x => x.Subject == inputSubject);

if (inputQuality.Length > 0)
 query = query.Where(x => x.Quality == int.Parse(inputQuality));

foreach (var item in query)
{
 Console.WriteLine("{0} {1} {2}", item.Teacher, item.Subject, item.Quality);
}

If you only input one variable, say Math on Subject, the resulting query will be this:

exec sp_executesql N'select schoolofth0_.SchoolOfThoughtId as SchoolOf1_3_, schoolofth0_.Teacher as Teacher3_, schoolofth0_.Subject as Subject3_, schoolofth0_.Quality as Quality3_ 
from [SchoolOfThought] schoolofth0_ 
where schoolofth0_.Subject=@p0',N'@p0 nvarchar(4000)',@p0=N'Math'

Linq-chaining has the advantage of preventing Sql-injection while maintaining the advantage of strongly-typed(as opposed to stringly-typed programming, e.g. concatenation) programming, strongly-typed means less error, you have autocomplete at your fingertips when you are using an IDE, and you have a code that is refactoring-friendly anytime. And last but not the least, with Linq you'll have more confidence the program is correct even before it is run.


If the inputSubject and inputQuality are required inputs; compiled-expression-wise, these five code has no differences:

Approach 1

string inputSubject = "Math";
string inputQuality = "80";

var query = 
 from x in s.Query<SchoolOfThought>()
 where x.Subject == inputSubject && x.Quality == int.Parse(inputQuality)
 select x;

Approach 2. Don't worry, parenthesis isn't required, compiler is not confused where the Linq boundaries end, think of from having a built-in open parenthesis, and select having a built-in close parenthesis.

string inputSubject = "Math";
string inputQuality = "80";

var query =
 from x in   
               
      from x in s.Query<SchoolOfThought>()
      where x.Subject == inputSubject
      select x  

 where x.Quality == int.Parse(inputQuality)
 select x;

Approach 3

string inputSubject = "Math";
string inputQuality = "80";

var query =
 (from x in s.Query<SchoolOfThought>()
 where x.Subject == inputSubject
 select x)
 .Where(x => x.Quality == int.Parse(inputQuality));

Approach 4

string inputSubject = "Math";
string inputQuality = "80";

var query = s.Query<SchoolOfThought>()
 .Where(x => x.Subject == inputSubject && x.Quality == int.Parse(inputQuality));

Approach 5

string inputSubject = "Math";
string inputQuality = "80";

var query = s.Query<SchoolOfThought>()
 .Where(x => x.Subject == inputSubject)
 .Where(x => x.Quality == int.Parse(inputQuality));

All the five code approaches above is compiled to this code:

exec sp_executesql 
N'select schoolofth0_.SchoolOfThoughtId as SchoolOf1_3_, schoolofth0_.Teacher as Teacher3_, schoolofth0_.Subject as Subject3_, schoolofth0_.Quality as Quality3_ 
from [SchoolOfThought] schoolofth0_ 
where schoolofth0_.Subject=@p0 and schoolofth0_.Quality=@p1',N'@p0 nvarchar(4000),@p1 int',@p0=N'Math',@p1=80 

Lastly, if you want your Linq-chaining to look as query-like(instead of Lambda-ish approach) as possible:

string inputTeacher = Console.ReadLine();
string inputSubject = Console.ReadLine();
string inputQuality = Console.ReadLine();


var query = s.Query<SchoolOfThought>();

if (inputTeacher.Length > 0)
 query = 
  from x in query 
  where x.Teacher == inputTeacher 
  select x;

if (inputSubject.Length > 0)
 query = 
  from x in query 
  where x.Subject == inputSubject 
  select x;

if (inputQuality.Length > 0)
 query = 
  from x in query 
  where x.Quality == int.Parse(inputQuality)
  select x;

I deliberately wrap the Linq clauses on their own line; so as to sway you to use the Lambda-ish approach instead, which arguably is way much shorter some of the times :-) Some of the times only, not all the times ;-)

Compelling reason to abandon ADO.NET and use Linq-capable ORM instead

Many scratch their head with this innocent question:

How to pass "Philippines,China,Canada" as parameter @list to IN clause? This isn't working: SELECT * FROM Country WHERE CountryName IN (@list)

Using Linq, that problem can be easily solved. There's no method on scalar type that can test against a list, a good Linq provider (used Linq-to-NHibernate here) don't have a problem translating this(list against scalar) though:

var countryList = 
 from c in s.Query<Country>()
 where new[]{ "Philippines", "China" }.Contains(c.CountryName)
 select c;

The generated query:

exec sp_executesql N'select country0_.CountryId as CountryId1_, country0_.CountryName as CountryN2_1_, country0_.Population as Population1_ 
from [Country] country0_ 
where country0_.CountryName in (@p0 , @p1)',N'@p0 nvarchar(4000),@p1 nvarchar(4000)',@p0=N'Philippines',@p1=N'China'

Linq can accept variable list too:

Console.Write("Input list: ");
string[] list = Console.ReadLine().Split(','); // example input is Philippines,Canada,China

var countryList =
 from c in s.Query<Country>()
 where list.Contains(c.CountryName)
 select c;

The generated query:

exec sp_executesql N'select country0_.CountryId as CountryId1_, country0_.CountryName as CountryN2_1_, country0_.Population as Population1_ 
from [Country] country0_ 
where country0_.CountryName in (@p0 , @p1 , @p2)',N'@p0 nvarchar(4000),@p1 nvarchar(4000),@p2 nvarchar(4000)',@p0=N'Philippines',@p1=N'Canada',@p2=N'China'

Linq nuances with WCF Data Service

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

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

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

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

I should have known better :-)

NHibernate 3 + Fluent + Linq = One Stop Shop application development

Do you see yourself years from now, doing repetitive coding, still trying to remember each and every attributes and format of a not-so-compelling XML configuration files? Trying to remember each and every Attributes that needed be attached to your class and its properties? Writing countless simple stored procedures? Neither do I

We are lucky in our time, that the confluence of many programming technologies is enabling us to write verifiable code, a code that can be verified just by reading the er.. code, no more XML, no more magic strings, no need to switch between environments/contexts incessantly, codes that are refactoring-friendly. These technologies also reduces us of mental context-switching

With the introduction of Linq in NHibernate 3, fluent and lambda-based mapping technique(courtesy of Fluent NHibernate), the future is here now. With these technologies, one-stop-shop programming with C# holds more true today than ever before, it's all in the source Luke.

Another one-stop-shop coding in a one-stop-shop coding of FNH is its ConventionBuilder(a topic we will cover on next post), if you have repetitive mappings that follows some patterns/rules and it's a bummer to adhere to them manually on each and every mapping, you can place them in one centralized location, in ConventionBuilders; no more scattered mappings, no more manual typing. And what does Convention-based mappings have that Attributes-based(e.g. Linq to SQL) ones don't have? The latter is not programmable and is scattered on your code.


Following is the simplest code. We omit the repository pattern in our code, yet it maintains its 'real world'-ness, in particular we have prevention mechanism in place for accidentally building a SessionFactory twice. Here's the simplest implementation of NHibernate + Fluent NHibernate + Linq:

using System;
using System.Linq;

using NHibernate;
using NHibernate.Cfg;
using NHibernate.Linq;

/* 
* a bit amused :-) James Gregory has a good program organization,
* he doesn't stash the Fluent NHibernate core functionalities to FluentNHibernate namespace
*/
// using FluentNHibernate; 
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using FluentNHibernate.Mapping;
using FluentNHibernate.Conventions.Helpers;



namespace SimpleFnhLinq
{
    
    class MainClass
    {
        public static void Main (string[] args)
        {
            var sess = SessionFactoryBuilder.GetSessionFactory().OpenSession();
                        
            
            Action listProduct =  delegate
            {
                foreach(var p in sess.Query<Product>())
                {
                    Console.WriteLine("{0}, {1}, {2}, {3}", p.ProductID, p.Name, p.Category, p.Price);
                }
                    
            };
            

            
            
            Console.WriteLine("\nINSERT: ");
            var nh = new Product { Name = "NHibernate", Description = "Awesome", Category = "ORM", Price = 1234567 };
            sess.Save(nh); // buffer it in memory
            sess.Flush(); // actual database act
            listProduct();
            
            
            Console.WriteLine("\nUPDATE: ");
            nh.Name = "NHibernate+Linq";
            sess.Save(nh);
            sess.Flush();             
            listProduct();
            
            
                    
            Console.WriteLine("\nINSERT: ");
            sess.Save(new Product { Name = "Fluent NHibernate", Description = "Bee's Knees", Category = "Friend", Price = 8901234 } );
            sess.Flush();
            listProduct();
            
            
            
            // NHibernate 3 has Linq support
            var q = from p in sess.Query<Product>()
                    where p.Category == "ORM"
                    select p;
            
            
            Console.WriteLine("\n\nList of ORM: ");
            foreach(var p in q)
            {
                Console.WriteLine("{0}, {1}, {2}, {3}", p.ProductID, p.Name, p.Category, p.Price);
            }

            

            Console.WriteLine("\n\nRecords count: {0}", sess.Query<Product>().Count());
            Console.ReadLine();
        }
    }//MainClass


    #region POCO...

    public class Product 
    { 
        public virtual int ProductID { get; set; }
        public virtual string Name { get; set; }
        public virtual string Description { get; set; }
        public virtual decimal Price { get; set; }
        public virtual string Category { get; set; }
    }

    #endregion ...POCO


    #region Mappings...


    public class ProductMap : ClassMap<Product>
    {
        public ProductMap()
        {        
            Id(x => x.ProductID).GeneratedBy.HiLo("1");
            Map(x => x.Name);
            Map(x => x.Description);
            Map(x => x.Category);
            Map(x => x.Price);            
        }
    }
    
    #endregion ...Mappings



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


            if (_sf != null) return _sf;

            var configurer = new IPersistenceConfigurer[]
                {
                    PostgreSQLConfiguration.Standard
                        .ConnectionString("Server=localhost;Database=store;User ID=postgres;Password=opensesame;"),
                    MsSqlConfiguration.MsSql2008
                        .ConnectionString(@"Data Source=.\SQLExpress;Initial Catalog=store;Trusted_Connection=true")
                };

            var dialects = new NHibernate.Dialect.Dialect[]
            {
                new NHibernate.Dialect.PostgreSQLDialect(),
                new NHibernate.Dialect.MsSql2008Dialect()                
            };

            int n = 1; // Sql Server

            _sf = Fluently.Configure()
                .Database(configurer[n])
                .Mappings
                    (m =>
                        m.FluentMappings.AddFromAssemblyOf<MainClass>()
                        .Conventions.Add(ConventionBuilder.Class.Always(x => x.Table(x.TableName.ToLower())))  // Postgresql Compatibility
                    )
                /*.ExposeConfiguration
                    (x => 
                        { 
                        // If you want don't want to create the script yourself copy the string from here and paste it to your database admin tool
                            string ddl = string.Join("; ", x.GenerateSchemaCreationScript(dialects[n]));
                        Console.WriteLine( "{0}", ddl );
                        Console.ReadLine();
                        }
                    )*/
                .BuildSessionFactory();

            return _sf;
        }
    }//SessionFactoryBuilder
    
                
}//namespace

Here's the DDL, compatible to Postgresql and Sql Server

CREATE TABLE Product
(
  ProductId int NOT NULL primary key,
  Name varchar(100) NOT NULL,
  Description varchar(500) NOT NULL,
  Category varchar(50) NOT NULL,
  Price numeric NOT NULL
);


CREATE TABLE hibernate_unique_key
(
  next_hi integer
);

insert into hibernate_unique_key values(1);

The components needed, add this to your project's References:

• fluentnhibernate-NH3.0-binary-1.2.0.694\FluentNHibernate.dll
• NHibernate-3.0.0.GA-bin\Required_Bins\NHibernate.dll
• NHibernate-3.0.0.GA-bin\Required_For_LazyLoading\Castle\NHibernate.ByteCode.Castle.dll