NBear.Data.Gateway强类型查询简介

    NBear的持久化组件提供类似Linq的强类型查询语法，支持主外键关联、继承关联、多对多关联、级联更新、联结、分组、分页查询等，对应90%以上的常用SQL查询语法。并提供对内存数组的强类型查询过滤、查询缓存、对自定义SQL和存储过程的强类型查询封装等功能。

    下面是一些典型的强类型查询语法及对应的运行时的SQL：

//带查询条件的简单查询
Product[] products = gateway.From<Product>().Where((Product._.UnitsInStock <= Product._.ReorderLevel && !(Product._.Discontinued == true)) || Product._.UnitPrice < 10m).ToArray<Product>;
SQL:
SELECT [Products].[ProductID],[Products].[ProductName],[Products].[SupplierID],[Products].[CategoryID],[Products].[QuantityPerUnit],[Products].[UnitPrice],[Products].[UnitsInStock],[Products].[UnitsOnOrder],[Products].[ReorderLevel],[Products].[Discontinued] FROM [Products] WHERE ((([Products].[UnitsInStock] <= [Products].[ReorderLevel]) AND NOT (([Products].[Discontinued] = @p3bue3xtylvv4kh))) OR ([Products].[UnitPrice] < @p09bi3366a4l612))
Parameters:
@p3bue3xtylvv4kh[Boolean] = 1
@p09bi3366a4l612[Decimal] = 10
-
//不带条件的分页查询
OrderDetailsExtended[] orders = gateway.From<OrderDetailsExtended>().ToArrayList<OrderDetailsExtended>(10, 10).ToArray();
SQL:
SELECT TOP 10 [Order Details Extended].[OrderID],[Order Details Extended].[ProductID],[Order Details Extended].[ProductName],[Order Details Extended].[UnitPrice],[Order Details Extended].[Quantity],[Order Details Extended].[Discount],[Order Details Extended].[ExtendedPrice] FROM [Order Details Extended] WHERE [Order Details Extended].[OrderID]>(SELECT MAX([__T].[OrderID]) FROM (SELECT TOP 10 [Order Details Extended].[OrderID] AS [OrderID] FROM [Order Details Extended] ORDER BY [Order Details Extended].[OrderID]) [__T]) ORDER BY [Order Details Extended].[OrderID]
-
//对查询结果在内存中二次过滤
EntityArrayList<Employee> employeeList2 = gateway.From<Employee>().ToArrayList<Employee>();
SQL:
SELECT [Employees].[EmployeeID],[Employees].[LastName],[Employees].[FirstName],[Employees].[Title],[Employees].[TitleOfCourtesy],[Employees].[BirthDate],[Employees].[HireDate],[Employees].[Address],[Employees].[City],[Employees].[Region],[Employees].[PostalCode],[Employees].[Country],[Employees].[HomePhone],[Employees].[Extension],[Employees].[Photo],[Employees].[Notes],[Employees].[ReportsTo],[Employees].[PhotoPath] FROM [Employees]
//内存中查询过滤
Employee[] filterredEmps = employeeList2.Filter(Employee._.HireDate >= new DateTime(1994, 1, 1), Employee._.City.Asc && Employee._.EmployeeID.Desc);
-
//分组和统计
CustOrderHistResult firstCountProductGroupByNameDesc = gateway.From<Product>().GroupBy(Product._.ProductName.GroupBy).OrderBy(Product._.ProductName.Desc).Select(Product._.ProductName, Product._.ProductID.Count()).ToFirst<CustOrderHistResult>();
SQL:
SELECT [Products].[ProductName],COUNT([Products].[ProductID]) FROM [Products] GROUP BY [Products].[ProductName] ORDER BY [Products].[ProductName] DESC
-
//聚合和查询函数
int maxProductUnit = Convert.ToInt32(gateway.From<Product>().Where(Product._.Discontinued == true).Select(Product._.ProductID.Max()).ToScalar());
SQL:
Text SELECT MAX([Products].[ProductID]) FROM [Products] WHERE ([Products].[Discontinued] = @p2qrwxrrwspri4h)
Parameters:
@p2qrwxrrwspri4h[Boolean] = 1
-
//查询中使用字符串函数
Category[] testStringFunctionsCats = gateway.From<Category>().Where(Category._.CategoryName.Contains("a") && Category._.CategoryName.Length > 2).ToArray<Category>();
SQL:
SELECT [Categories].[CategoryID],[Categories].[CategoryName],[Categories].[Description],[Categories].[Picture] FROM [Categories] WHERE (([Categories].[CategoryName] LIKE @pvvkhm25okrj9g8) AND (LEN([Categories].[CategoryName]) > @p1m9ulgpjn1llb4))
Parameters:
@pvvkhm25okrj9g8[String] = %a%
@p1m9ulgpjn1llb4[Int32] = 2
-
//查询中使用日期函数
DataSet testDateFunctionsDs = gateway.From<Employee>().Where(Employee._.HireDate.GetYear() == 1999 && Employee._.HireDate > PropertyItem.GetCurrentDate() - new TimeSpan(1000, 0, 0, 0)).ToDataSet();
SQL:
SELECT [Employees].[EmployeeID],[Employees].[LastName],[Employees].[FirstName],[Employees].[Title],[Employees].[TitleOfCourtesy],[Employees].[BirthDate],[Employees].[HireDate],[Employees].[Address],[Employees].[City],[Employees].[Region],[Employees].[PostalCode],[Employees].[Country],[Employees].[HomePhone],[Employees].[Extension],[Employees].[Photo],[Employees].[Notes],[Employees].[ReportsTo],[Employees].[PhotoPath] FROM [Employees] WHERE ((DATEPART(Year,[Employees].[HireDate]) = @pqr32n34ruererb) AND ([Employees].[HireDate] > GETDATE() - @p71cpbjv7gwwmqb))
Parameters:
@pqr32n34ruererb[Int32] = 1999
@p71cpbjv7gwwmqb[DateTime] = 1902-9-28 0:00:00
-
//透明的关联条件查询
Product[] testImplicitJoinsOfProducts = gateway.From<Product>().Where(Product._.Category.CategoryName.ToUpper() == "TEST" && Product._.Supplier.Country.ToLower() == "china").ToArray<Product>();
SQL:
SELECT [Products].[ProductID],[Products].[ProductName],[Products].[SupplierID],[Products].[CategoryID],[Products].[QuantityPerUnit],[Products].[UnitPrice],[Products].[UnitsInStock],[Products].[UnitsOnOrder],[Products].[ReorderLevel],[Products].[Discontinued] FROM ([Products] INNER JOIN [Categories] [Products_Category_Categories] ON [Products_Category_Categories].[CategoryID] = [Products].[CategoryID]) INNER JOIN [Suppliers] [Products_Supplier_Suppliers] ON [Products_Supplier_Suppliers].[SupplierID] = [Products].[SupplierID] WHERE ((UPPER([Products_Category_Categories].[CategoryName]) = @p8jv2o2wsphje48) AND (LOWER([Products_Supplier_Suppliers].[Country]) = @pqt37nednnvar7k))
Parameters:
@p8jv2o2wsphje48[String] = TEST
@pqt37nednnvar7k[String] = china
-
//透明的自关联查询
Employee[] testImplicitJoinsOfEmps = gateway.From<Employee>().Where(Employee._.ReportsToEmployee.FirstName == "teddy").ToArray<Employee>();
SQL:
SELECT [Employees].[EmployeeID],[Employees].[LastName],[Employees].[FirstName],[Employees].[Title],[Employees].[TitleOfCourtesy],[Employees].[BirthDate],[Employees].[HireDate],[Employees].[Address],[Employees].[City],[Employees].[Region],[Employees].[PostalCode],[Employees].[Country],[Employees].[HomePhone],[Employees].[Extension],[Employees].[Photo],[Employees].[Notes],[Employees].[ReportsTo],[Employees].[PhotoPath] FROM [Employees] INNER JOIN [Employees] [Employees_ReportsToEmployee_Employees] ON [Employees_ReportsToEmployee_Employees].[EmployeeID] = [Employees].[ReportsTo] WHERE ([Employees_ReportsToEmployee_Employees].[FirstName] = @pa552arjaiqopdc)
Parameters:
@pa552arjaiqopdc[String] = teddy
-
//显式的自定义join查询
Product[] testExplicitJoinsOfProducts = gateway.From<Product>().Join<Category>(Product._.CategoryID == Category._.CategoryID).Where(Category._.CategoryName.ToUpper() == "TEST").ToArray<Product>();
SQL:
SELECT [Products].[ProductID],[Products].[ProductName],[Products].[SupplierID],[Products].[CategoryID],[Products].[QuantityPerUnit],[Products].[UnitPrice],[Products].[UnitsInStock],[Products].[UnitsOnOrder],[Products].[ReorderLevel],[Products].[Discontinued] FROM [Products] INNER JOIN [Categories] ON ([Products].[CategoryID] = [Categories].[CategoryID]) WHERE (UPPER([Categories].[CategoryName]) = @pau7vfnemtpxcwj)
Parameters:
@pau7vfnemtpxcwj[String] = TEST
-
//带参数的自定义SQL查询
Category[] cats = gateway.FromCustomSql("select [CategoryID], [CategoryName], [Description], [Picture] from [Categories] where [CategoryID] > @p1 and [CategoryID] < @p2").AddInputParameter("p1", DbType.Int32, 100).AddInputParameter("p2", DbType.Int32, 2000).ToDataSet();
SQL:
select [CategoryID], [CategoryName], [Description], [Picture] from [Categories] where [CategoryID] > @p1 and [CategoryID] < @p2
Parameters:
@p1[Int32] = 100
@p2[Int32] = 2000
-
//调用存储过程
DataSet dsCustHits = gateway.FromStoredProcedure("CustOrderHist").AddInputParameter("CustomerID", DbType.String, "ALFKI").ToDataSet();
SQL:
CustOrderHist
Parameters:
@CustomerID[String] = ALFKI