有人問過我一個問題，既然用了NHibernate還有必要用Linq嗎？就這個問題，我回答下列幾點：
1.Linq to NHibernate有VS的IDE提示，編譯器可以幫助我提示出錯誤。
2.Linq to NHibernate最大限度的幫我們優化SQL語句，尤其是匿名類的查詢語句和聚合函數的查詢。
3.可以結合Linq加NHibernate各自的延遲加載特性，幫我們在必要的時候生成特定的SQL語句。
4.統一語言，必要的時候不用學習HQL來增加學習成本。
5.方便於滿足復雜查詢的需求(如報表)。
然而對上述觀點我提出如下建議：
1.建議映射文件*.hbm.XML文件的lazy="true"來開啟延遲加載。
2.數據庫訪問對象(Dao)類的返回值建議使用IQueryable<T>泛型類型或者IQueryable接口
以下是Linq to NHibernate查詢語句的代碼。
ProductsDao
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Domain.EntitIEs;
using NHibernate;
using NHibernate.Linq;
using System.Linq.Expressions;
namespace Linq2NHibernate.Dao
{
public class ProductsDao
{
private ISession session = DBUtility.NHibernateHelper.GetCurrentSession();
/// <summary>
/// select 語句
/// sql : SELECT this_.ProductID as ProductID1_0_, this_.ProductName as ProductN2_1_0_,
/// this_.QuantityPerUnit as Quantity3_1_0_, this_.UnitPrice as UnitPrice1_0_,
/// this_.UnitsInStock as UnitsInS5_1_0_, this_.UnitsOnOrder as UnitsOnO6_1_0_,
/// this_.ReorderLevel as ReorderL7_1_0_, this_.Discontinued as Disconti8_1_0_,
/// this_.CategoryID as CategoryID1_0_ FROM Products this_
/// </summary>
/// <returns></returns>
public IQueryable<Products> GetAll()
{
var query = from s in session.Linq<Products>() select s;
return query;
}
/// <summary>
/// 匿名類查詢
/// sql : SELECT this_.ProductID as y0_, this_.ProductName as y1_,
/// this_.QuantityPerUnit as y2_ FROM Products this_
/// 個人認為匿名類查詢可以優化Nhibernate查詢，僅僅生成部分字段的查詢
/// </summary>
/// <returns></returns>
public IQueryable AnonymousType()
{
var query = from products in session.Linq<Products>()
select
new { products.ProductID, products.ProductName, products.QuantityPerUnit };
return query;
}
/// <summary>
/// 查詢第一個記錄 top 1
/// sql : SELECT top 1 this_.ProductID as ProductID1_0_, this_.ProductName as ProductN2_1_0_,
/// this_.QuantityPerUnit as Quantity3_1_0_, this_.UnitPrice as UnitPrice1_0_,
/// this_.UnitsInStock as UnitsInS5_1_0_, this_.UnitsOnOrder as UnitsOnO6_1_0_,
/// this_.ReorderLevel as ReorderL7_1_0_,
/// this_.Discontinued as Disconti8_1_0_, this_.CategoryID as CategoryID1_0_ FROM Products this_
/// </summary>
/// <returns></returns>
public
Products First()
{
var query = from s in session.Linq<Products>() select s;
return query.First<Products>();
}
/// <summary>
/// Count(*) 聚合函數查詢
/// sql : SELECT count(*) as y0_ FROM Products this_
/// </summary>
/// <returns></returns>
public int Count()
{
var query = from s in session.Linq<Products>() select s;
return query.Count<Products>();
}
/// <summary>
/// where ProductName = "Ikura" or ProductName = "Konbu" 語句
/// sql : exec sp_executesql N'SELECT this_.ProductID as ProductID1_0_,
/// this_.ProductName as ProductN2_1_0_, this_.QuantityPerUnit as Quantity3_1_0_,
/// this_.UnitPrice as UnitPrice1_0_, this_.UnitsInStock as UnitsInS5_1_0_,
/// this_.UnitsOnOrder as UnitsOnO6_1_0_, this_.ReorderLevel as ReorderL7_1_0_,
/// this_.Discontinued as Disconti8_1_0_, this_.CategoryID as CategoryID1_0_
/// FROM Products this_ WHERE (this_.ProductName = @p0 or this_.ProductName = @p1)',
/// N'@p0 nvarchar(5),@p1 nvarchar(5)', @p0 = N'Ikura', @p1 = N'Konbu'
/// </summary>
/// <returns></returns>
public IQueryable<Products> SelectOr()
{
var query = from s in session.Linq<Products>()
where s.ProductName == "Ikura" || s.ProductName == "Konbu" select s;
return query;
}
/// <summary>
/// where UnitPrice = 21 and UnitsInStock = 15 語句
/// sql : exec sp_executesql N'SELECT this_.ProductID as ProductID1_0_,
/// this_.ProductName as ProductN2_1_0_, this_.QuantityPerUnit as Quantity3_1_0_,
/// this_.UnitPrice as UnitPrice1_0_, this_.UnitsInStock as UnitsInS5_1_0_,
/// this_.UnitsOnOrder as UnitsOnO6_1_0_, this_.ReorderLevel as ReorderL7_1_0_,
/// this_.Discontinued as Disconti8_1_0_, this_.CategoryID as CategoryID1_0_
/// FROM Products this_ WHERE (this_.UnitPrice = @p0 and this_.UnitsInStock = @p1)',
/// N'@p0 decimal(2,0),@p1 smallint', @p0 = 21, @p1 = 15
/// </summary>
/// <returns></returns>
public IQueryable<Products> SelectAnd()
{
var query = from s in session.Linq<Products>()
where s.UnitPrice == 21 && s.UnitsInStock == 15 select s;
return query;
}
/// <summary>
/// where UnitPrice >= 21 語句
/// sql : exec sp_executesql N'SELECT this_.ProductID as ProductID1_0_,
/// this_.ProductName as ProductN2_1_0_, this_.QuantityPerUnit as Quantity3_1_0_,
/// this_.UnitPrice as UnitPrice1_0_, this_.UnitsInStock as UnitsInS5_1_0_,
/// this_.UnitsOnOrder as UnitsOnO6_1_0_, this_.ReorderLevel as ReorderL7_1_0_,
/// this_.Discontinued as Disconti8_1_0_, this_.CategoryID as CategoryID1_0_
/// FROM Products this_ WHERE this_.UnitPrice >= @p0', N'@p0 decimal(2,0)', @p0 = 21
/// </summary>
/// <returns></returns>
public IQueryable<Products> SelectLe()
{
var query = from s in session.Linq<Products>()
where s.UnitPrice >= 21 select s;
return query;
}
/// <summary>
/// where UnitPrice Between 15 and 100 語句
/// sql : exec sp_executesql N'SELECT this_.ProductID as ProductID1_0_,
/// this_.ProductName as ProductN2_1_0_, this_.QuantityPerUnit as Quantity3_1_0_,
/// this_.UnitPrice as UnitPrice1_0_, this_.UnitsInStock as UnitsInS5_1_0_,
/// this_.UnitsOnOrder as UnitsOnO6_1_0_, this_.ReorderLevel as ReorderL7_1_0_,
/// this_.Discontinued as Disconti8_1_0_, this_.CategoryID as CategoryID1_0_
/// FROM Products this_ WHERE (this_.UnitPrice >= @p0
/// and this_.UnitPrice <= @p1)', N'@p0 decimal(2,0),@p1 decimal(3,0)', @p0 = 15, @p1 = 100
/// </summary>
/// <returns></returns>
public IQueryable<Products> SelectBetweenAnd()
{
var query = from s in session.Linq<Products>()
where s.UnitPrice >= 15 && s.UnitPrice <= 100 select s;
return query;
}
/// <summary>
/// s.QuantityPerUnit == null : where QuantityPerUnit Is Null 語句
/// s.QuantityPerUnit != null : where QuantityPerUnit Is Not Null 語句
/// sql : SELECT this_.ProductID as ProductID1_0_, this_.ProductName as ProductN2_1_0_,
/// this_.QuantityPerUnit as Quantity3_1_0_, this_.UnitPrice as UnitPrice1_0_,
/// this_.UnitsInStock as UnitsInS5_1_0_, this_.UnitsOnOrder as UnitsOnO6_1_0_,
/// this_.ReorderLevel as ReorderL7_1_0_,
/// this_.Discontinued as Disconti8_1_0_, this_.CategoryID as CategoryID1_0_
/// FROM Products this_ WHERE this_.QuantityPerUnit is null
/// </summary>
/// <returns></returns>
public IQueryable<Products> SelectIsNull()
{
var query = from s in session.Linq<Products>()
where s.QuantityPerUnit == null select s;
return query;
}
/// <summary>
/// 動態linq語句
/// sql : exec sp_executesql N'SELECT this_.ProductID as ProductID1_0_,
/// this_.ProductName as ProductN2_1_0_, this_.QuantityPerUnit as Quantity3_1_0_,
/// this
_.UnitPrice as UnitPrice1_0_, this_.UnitsInStock as UnitsInS5_1_0_,
/// this_.UnitsOnOrder as UnitsOnO6_1_0_, this_.ReorderLevel as ReorderL7_1_0_,
/// this_.Discontinued as Disconti8_1_0_, this_.CategoryID as CategoryID1_0_
/// FROM Products this_ WHERE (this_.ProductName = @p0 or this_.ProductName = @p1)',
/// N'@p0 nvarchar(5),@p1 nvarchar(5)', @p0 = N'Ikura', @p1 = N'Konbu'
/// </summary>
/// <returns></returns>
public IQueryable<Products> SelectDynamic()
{
Expression<Func<Products, bool>> expr1 = u => u.ProductName == "Ikura";
Expression<Func<Products, bool>> expr2 = u => u.ProductName == "Konbu";
var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
var dynamicWhereClause = Expression.Lambda<Func<Products, bool>>
(Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
var query = session.Linq<Products>().Where(dynamicWhereClause);
return query;
}
/// <summary>
/// StartsWith : ProductName Like "A%" 語句
/// EndsWith: ProductName Like "%A" 語句
/// Contains: ProductName Like "%A%" 語句
/// sql : exec sp_executesql N'SELECT this_.ProductID as ProductID1_0_,
/// this_.ProductName as ProductN2_1_0_, this_.QuantityPerUnit as Quantity3_1_0_,
/// this_.UnitPrice as UnitPrice1_0_, this_.UnitsInStock as UnitsInS5_1_0_,
/// this_.UnitsOnOrder as UnitsOnO6_1_0_, this_.ReorderLevel as ReorderL7_1_0_,
/// this_.Discontinued as Disconti8_1_0_, this_.CategoryID as CategoryID1_0_ FROM Products this_
/// WHERE this_.ProductName like @p0', N'@p0 nvarchar(2)', @p0 = N'A%'
/// </summary>
/// <returns></returns>
public IQueryable<Products> GetLike()
{
var query = from p in session.Linq<Products>()
where p.ProductName.StartsWith("A") select p;
return query;
}
/// <summary>
/// names.Contains : ProductName In "Tofu,Pavlova" 語句
/// !names.Contains : ProductName Not In "Tofu,Pavlova" 語句
/// sql : exec sp_executesql N'SELECT this_.ProductID as ProductID1_0_,
/// this_.ProductName as ProductN2_1_0_, this_.QuantityPerUnit as Quantity3_1_0_,
/// this_.UnitPrice as UnitPrice1_0_, this_.UnitsInStock as UnitsInS5_1_0_,
/// this_.UnitsOnOrder as UnitsOnO6_1_0_, this_.ReorderLevel as ReorderL7_1_0_,
/// this_.Discontinued as Disconti8_1_0_, this_.CategoryID as CategoryID1_0_
/// FROM Products this_ WHERE this_.ProductName in (@p0, @p1)',
/// N'@p0 nvarchar(4),@p1 nvarchar(7)', @p0= N'Tofu', @p1 = N'Pavlova'
/// </summary>
/// <returns></returns>
public IQueryable<Products> GetIn()
{
var names = new string[] { "Tofu", "Pavlova" };
var query = from p in session.Linq<Products>()
where names.Contains(p.ProductName) select p;
return query;
}
}
}
從上述代碼，我可以看到Linq to NHibernate查詢語句和SQL語句的對照。
本文作者:未知
