Monday, August 26, 2013

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


    }

}

No comments:

Post a Comment