Microorm. Net (8) Table. Query ()-distinct, skip & take

Microorm. Net homepage:

Http://code.google.com/p/micro-orm-net/

Overview of the microorm. Net series directories:

Http://www.cnblogs.com/mapserver/archive/2012/04/07/2436129.html

 

In the previous article, we have explained the vast majority of microorm. Net queries. This article will explain the last several features that are frequently used in queries in the future.

Skip & take: Mainly used on the query page. The API is as follows:

Query Skip(int skip)Query Take(int take)

 

Both methods have only one int type parameter. The method is as follows:

db.OrderDetail.Query()        .Skip(10)        .Take(10)

The code above indicates taking 11th to 20th pieces of data. In the SQL server section of microorm. net, two built-in functions, row_number () and over (), are used.

 

Distinct: filters out duplicate data. The API is as follows:

Query Distinct()

It does not have any parameter parameters. The Code is as follows:

db.OrderDetail.Query()        .Select(db.OrderDetal.ProductId)        .Distinct()

 

Distinct and skip & take are both simple, but note that these three methods can only be executed once on a microorm. Net query object. The following code will report an error.

DB. orderdetail. Query ()

. Skip (10). Skip (1)

. Take (10). Take (1)

. Distinct (). Distinct ()

 

The query part of microorm. NET is over now. Let's end this article with a slightly more complex code:

var od = db.OrderDetail.Query()        .Where(db.OrderDetail.OrderId == "001")        .Where(db.OrderDetail.Qty > "0")        .Where(db.Order.CustomerId == "001")        .GroupBy(db.OrderDetail.OrderId)        .Join(db.Order, db.Order.OrderId == db.OrderDetail.OrderId)        .Skip(0)        .Take(1)        .OrderBy(db.OrderDetail.OrderId)        .Having(db.OrderDetail.Qty.Sum() > 0)        .Select(db.OrderDetail.OrderId, db.OrderDetail.Qty.Sum())        .First();

The SQL statements generated on SQL Server are as follows:

exec sp_executesql N'SELECT * FROM (SELECT [_#0_].[OrderId] [OrderId],SUM([_#0_].[Qty]) [Qty] ,ROW_NUMBER() OVER( ORDER BY [_#0_].[OrderId] ASC) [_ROW_NUMBER_] FROM [dbo].[OrderDetail] [_#0_] INNER JOIN [dbo].[Order] [o] ON o.[OrderId]=_#0_.[OrderId] WHERE [_#0_].[OrderId] = @p0 AND [_#0_].[Qty] > @p1 AND [o].[CustomerId] = @p2 GROUP BY [_#0_].[OrderId] HAVING SUM([_#0_].[Qty]) > @p3 ) _Data WHERE [_ROW_NUMBER_] BETWEEN 0 AND 1 ',N'@p0 nvarchar(3),@p1 int,@p2 nvarchar(3),@p3 int',@p0=N'001',@p1=0,@p2=N'001',@p3=0