NbearV3.7新增强类型查询功能演示教程

本教程演示了NBear中典型的的各种查询功能。包括：join，group by，paging等的支持。

下载地址：http://nbear.org/Modules/Articles/Detail.aspx?i=59

教程内容（同运行时效果）：

The StrongTypeQuery tutorial demostrates the usage of the powerful strong type query syntax of NBear.

---

Gateway.From

---

Sample1 - Basic Where() & ToArray():

Product[] products = gateway.From<Product>().Where((Product._.UnitsInStock <= Product._.ReorderLevel && !(Product._.Discontinued == true)) || Product._.UnitPrice < 10m).ToArray<Product>;
Log:
Text 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] = @pqanswaefvd06m7 OR [Products].[UnitPrice] < @p6xa720w2pfnvsv
Parameters:
@pqanswaefvd06m7[Boolean] = 1
@p6xa720w2pfnvsv[Decimal] = 10

Sample2 - Basic Where() & OrderBy() & ToArrayList():

EntityArrayList<Employee> employeeList = gateway.From<Employee>().Where(Employee._.HireDate >= new DateTime(1994, 1, 1)).OrderBy(Employee._.Country.Asc && Employee._.EmployeeID.Desc).ToArrayList<Employee>();
Log:
Text 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 [Employees].[HireDate] >= @pwpqblpc0s4le6w ORDER BY [Employees].[Country],[Employees].[EmployeeID] DESC
Parameters:
@pwpqblpc0s4le6w[DateTime] = 1994-1-1 0:00:00

Sample3 - No Where() and OrderBy() & ToArrayList() & ToArrayList().Filter():

EntityArrayList<Employee> employeeList2 = gateway.From<Employee>().ToArrayList<Employee>();
Log:
Text 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]


We can filter items in employeeList2 by strong type query condition through the Filter method of EntityArrayList:
Notice: EntityArrayList.Filter() supports both where and order by condition, it does in-memory filtering and will not cause any database queries.
Employee[] filterredEmps = employeeList2.Filter(Employee._.HireDate >= new DateTime(1994, 1, 1), Employee._.City.Asc && Employee._.EmployeeID.Desc);

Sample4 - Count() & GroupBy() & OrderBy() & Select() & ToFirst():

CustOrderHistResult firstCountProductGroupByNameDesc = gateway.From<Product>().GroupBy(Product._.ProductName.GroupBy).OrderBy(Product._.ProductName.Desc).Select(Product._.ProductName, Product._.ProductID.Count()).ToFirst<CustOrderHistResult>();
Log:
Text SELECT [Products].[ProductName],COUNT([Products].[ProductID]) FROM [Products] GROUP BY [Products].[ProductName] ORDER BY [Products].[ProductName] DESC

Sample5 - Max() & Where() & ToScalar():

int maxProductUnit = Convert.ToInt32(gateway.From<Product>().Where(Product._.Discontinued == true).Select(Product._.ProductID.Max()).ToScalar());
Log:
Text SELECT MAX([Products].[ProductID]) FROM [Products] WHERE [Products].[Discontinued] = @pj952ghh7lxivtu
Parameters:
@pj952ghh7lxivtu[Boolean] = 1

Sample6 - Top & Skip & Paging:

Get page count first:
int catsPageCount = gateway.CountPage<Category>(Category._.Description != null, 5);
Log:
Text SELECT COUNT(*) FROM [Categories] WHERE NOT [Categories].[Description] IS NULL


Get 1st page with pagesize = 5:
Notice: For SqlServer, getting 1st page using the select top clause.
Category[] firstPageCats = gateway.From<Category>().Where(Category._.Description != null).ToArray<Category>(5);
Log:
Text SELECT TOP 5 [Categories].[CategoryID],[Categories].[CategoryName],[Categories].[Description],[Categories].[Picture] FROM [Categories] WHERE NOT [Categories].[Description] IS NULL


Get 3rd page with pagesize = 5:
Notice: For getting specific page, you should specify both the pagesize and the SKIP ITEM COUNT - be careful, it is not Page No but skip item count. e.g. For getting the 3rd page with pagesize 5, value of the second parameter should be (PageNo - 1) * pagesize = (3 - 1) * 5 = 10
Category[] thirdPageCats = gateway.From<Category>().Where(Category._.Description != null).ToArray<Category>(5, 10);
Log:
Text SELECT TOP 5 [Categories].[CategoryID],[Categories].[CategoryName],[Categories].[Description],[Categories].[Picture] FROM [Categories] WHERE (NOT [Categories].[Description] IS NULL ) AND [Categories].[CategoryID] NOT IN (SELECT TOP 10 [Categories].[CategoryID] FROM [Categories] WHERE NOT [Categories].[Description] IS NULL )

Sample7 - String Functions:

Notice: Supported string functions include Contains()/StartsWith()/EndsWith()/Length/IndexOf()/Like()/Replace()/Trim()/SubString()/ToUpper()/ToLower()
Category[] testStringFunctionsCats = gateway.From<Category>().Where(Category._.CategoryName.Contains("a") && Category._.CategoryName.Length > 2).ToArray<Category>();
Log:
Text SELECT [Categories].[CategoryID],[Categories].[CategoryName],[Categories].[Description],[Categories].[Picture] FROM [Categories] WHERE [Categories].[CategoryName] LIKE @p58jl5yv5k10lsh AND LEN([Categories].[CategoryName]) > @pvj5m0lojxkxnew
Parameters:
@p58jl5yv5k10lsh[String] = %a%
@pvj5m0lojxkxnew[Int32] = 2

Sample8 - Date Functions:

Notice: Supported date functions include GetYear()/GetMonth()/GetDay()/GetCurrentDate()/Date Comparasion/Add/Substract
Notice: PropertyItem.GetCurrentDate() is very important at some situation, because it returns the current datetime on database server, while System.DateTime.Now returns the current datetime on application server.
DataSet testDateFunctionsDs = gateway.From<Employee>().Where(Employee._.HireDate.GetYear() == 1999 && Employee._.HireDate > PropertyItem.GetCurrentDate() - new TimeSpan(1000, 0, 0, 0)).ToDataSet();
Log:
Text 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]) = @p1v5pc8almjugsf AND [Employees].[HireDate] > GETDATE() - @pn5rtnya2svl87j
Parameters:
@p1v5pc8almjugsf[Int32] = 1999
@pn5rtnya2svl87j[DateTime] = 1902-9-28 0:00:00

Sample9 - Implicit Join:

Notice: When an entity has properties maked with [FkReverseQuery], which means these properties map to friend key columns, your can use the following simple clause to query your entity, but at internal, the code will be transalated as sql joins.
Notice: Product._.Category.CategoryName is CategoryName property of Product.Category property and Product._.Supplier.Country is Country property of Product._.Supplier property.
Product[] testImplicitJoinsOfProducts = gateway.From<Product>().Where(Product._.Category.CategoryName.ToUpper() == "TEST" && Product._.Supplier.Country.ToLower() == "china").ToArray<Product>();
Log:
Text 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]) = @p0ynfjx4nvi0k80 AND LOWER([Products_Supplier_Suppliers].[Country]) = @pvjqsr9vts4yfmx
Parameters:
@p0ynfjx4nvi0k80[String] = TEST
@pvjqsr9vts4yfmx[String] = china


Notice: Even implict self join is supported:
Employee[] testImplicitJoinsOfEmps = gateway.From<Employee>().Where(Employee._.ReportsToEmployee.FirstName == "teddy").ToArray<Employee>();
Log:
Text 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] = @p48552pbnvevtc1
Parameters:
@p48552pbnvevtc1[String] = teddy

Sample10 - Explicit Join & Join with alias name:

Notice: When an entity hasn't properties maked with [FkReverseQuery], but has friend key properties, you still can use explicit join clause.
Product[] testExplicitJoinsOfProducts = gateway.From<Product>().Join<Category>(Product._.CategoryID == Category._.CategoryID).Where(Category._.CategoryName.ToUpper() == "TEST").ToArray<Product>();
Log:
Text 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]) = @p80k3r1695rewnf
Parameters:
@p80k3r1695rewnf[String] = TEST


Notice: Explicit self join always means you must specify an alias name for join:
Notice: Be careful, when using alias name in joins, you must always use the same alias name in Join() and Where(), you Must use XXX.__Alias(aliasname).Property to access the query property in query condition.
Employee[] testExplicitJoinsOfEmps = gateway.From<Employee>().Join<Employee>("reportToEmp", Employee._.ReportsToEmployeeID == Employee.__Alias("reportToEmp").EmployeeID).Where(Employee._.ReportsToEmployee.FirstName == "teddy").ToArray<Employee>();
Log:
Text 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] [reportToEmp_Employees] ON [Employees].[ReportsTo] = [reportToEmp_Employees].[EmployeeID]) INNER JOIN [Employees] [Employees_ReportsToEmployee_Employees] ON [Employees_ReportsToEmployee_Employees].[EmployeeID] = [Employees].[ReportsTo] WHERE [Employees_ReportsToEmployee_Employees].[FirstName] = @p6kg5b5jnd5jgw9
Parameters:
@p6kg5b5jnd5jgw9[String] = teddy

---

Gateway.FromCustomSql

---

Sample1 - ToDataSet():

DataSet dsCats = gateway.FromCustomSql("select * from [Categories] where [CategoryID] > @p1 and [CategoryID] < @p2").AddInputParameter("p1", DbType.Int32, 100).AddInputParameter("p2", DbType.Int32, 2000).ToDataSet();
Log:
Text select * from [Categories] where [CategoryID] > @p1 and [CategoryID] < @p2
Parameters:
@p1[Int32] = 100
@p2[Int32] = 2000

Sample2 - ToArray():

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();
Notice: When using XXX.ToArray() or XXX.ToArrayList(), you must ensure the order of select column list of custom sql matches the order of target entity's properties' definition.
Log:
Text select [CategoryID], [CategoryName], [Description], [Picture] from [Categories] where [CategoryID] > @p1 and [CategoryID] < @p2
Parameters:
@p1[Int32] = 100
@p2[Int32] = 2000

---

Gateway.FromStoredProcedure

---

Sample1 - ToDataSet():

DataSet dsCustHits = gateway.FromStoredProcedure("CustOrderHist").AddInputParameter("CustomerID", DbType.String, "ALFKI").ToDataSet();
Log:
StoredProcedure CustOrderHist
Parameters:
@CustomerID[String] = ALFKI

Sample2 - ToArray():

DataSet dsCustHits = gateway.FromStoredProcedure("CustOrderHist").AddInputParameter("CustomerID", DbType.String, "ALFKI").ToDataSet();
Notice: When using XXX.ToArray() or XXX.ToArrayList(), you must ensure the order of select column list of stored procedure matches the order of target entity's properties' definition.
Notice: Gateway.FromStoredProcedure() supports adding all the 4 types of sql parameters: AddInputParameter(), AddInputOutputParameter(), AddOutputParameter(), SetReturnParameter(). Be careful to use the correct method matches your parameter type.
Log:
StoredProcedure CustOrderHist
Parameters:
@CustomerID[String] = ALFKI