Try .NET /elinq/
Powered by Try .NET

SQL Server ROW_NUMBER Function

Using SQL Server ROW_NUMBER() function over a result set example

var query = DbContext.Set<CustomerOrder>()
                .Query((Customers customer, CustomerOrder alias) =>
                {
                    var rowNum = AggregateBy(ROW_NUMBER())
                        .OVER(ORDER(BY(customer.FirstName)));

                    var r = SELECT<CustomerOrder>(customer.CustomerId.@as(alias.Customer.CustomerId),
                        rowNum.@as(alias.RowNumber));
                    FROM(customer);

                    return r;
                })
                .Include(r => r.Customer);

foreach (var order in query.Take(3))
    Console.WriteLine((order.RowNumber, order.Customer.FirstName, order.Customer.LastName, order.Customer.City));

Using SQL Server ROW_NUMBER() over partitions example

var query = DbContext.Set<CustomerOrder>()
                .Query((Customers customer, CustomerOrder alias) =>
                {
                    var rowNum = AggregateBy(ROW_NUMBER())
                        .OVER(PARTITION(BY(customer.City))
                            .ORDER(BY(customer.FirstName)));

                    var r = SELECT<CustomerOrder>(customer.CustomerId.@as(alias.Customer.CustomerId),
                        rowNum.@as(alias.RowNumber));
                    FROM(customer);

                    return r;
                })
                .OrderBy(r => r.Customer.City)
                .Include(r => r.Customer);

foreach (var order in query.Take(5))
    Console.WriteLine((order.RowNumber, order.Customer.FirstName, order.Customer.LastName, order.Customer.City));

Using SQL Server ROW_NUMBER() for pagination

var query = DbContext.Customers
                .Query((Customers customer) =>
                {
                    var order = SubQuery((CustomerOrder alias) =>
                    {
                        var rowNum = AggregateBy(ROW_NUMBER())
                            .OVER(ORDER(BY(customer.FirstName), BY(customer.LastName)));

                        var r = SELECT<CustomerOrder>(customer.CustomerId.@as(alias.Customer.CustomerId),
                            rowNum.@as(alias.RowNumber));
                        FROM(customer);

                        return r;
                    });

                    var r = SELECT(customer);
                    FROM(customer).JOIN(order).ON(customer == order.Customer);
                    WHERE(order.RowNumber > 20 && order.RowNumber <= 30);
                    return r;
                });

foreach (var customer in query)
    Console.WriteLine((customer.CustomerId, customer.FirstName, customer.LastName));

< BACK | HOME