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