如何用LINQ c#生成等价查询

Question

这是我的内联sql查询。我喜欢由LINQ生成的相同的等价查询。

var lowerPageBoundary = 0;
var tableName = "Orders";
var columnToSortBy = "OrderID";
var sortColumn = "OrderID";
var rowsPerPage = 16;
var CommaSeparatedListOfColumnNames = "OrderID,CustomerID,EmployeeID ";

var SQL = "Select Top " + rowsPerPage + " " +
    CommaSeparatedListOfColumnNames + " From " + tableName +
    " WHERE " + columnToSortBy + " NOT IN (SELECT TOP " +
    lowerPageBoundary + " " + columnToSortBy + " From " +
    tableName + " Order By " + sortColumn +
    ") Order By " + sortColumn;

到目前为止这是我的LINQ查询。只需告诉我如何自定义它以获得像上面这样的在线sql查询的输出。

    XDocument document = XDocument.Load(xmlFilePath);
    var query = from r in document.Descendants("orders")
        select new
        {
            OrderID = r.Element("OrderID").Value,
            CustomerID = r.Element("CustomerID").Value,
            EmployeeID = r.Element("EmployeeID").Value
        };
    query = query.OrderBy(sortColumn + " " + OrderDirection);

如何将其嵌套到上面的sql查询中，并添加类似于上面的sql查询的分页概念。

Answer 1

如果您发布原始SQL查询，会更容易一些，但以下是我的看法：

document
.Where(x=> !documents.OrderBy(p=> p.OrderID)
                     .Take(sortColumn)
                     .Contains(x))
.Select(p=> new {...})
.OrderBy(...).Take(...)

编辑：

或者更好的是：

var hashSet = new HashSet<Whatever>(
                     documents.OrderBy(p=> p.OrderID)
                              .Take(sortColumn));
document
.Where(x=> !hashSet.Contains(x))
.Select(p=> new {...})
.OrderBy(...).Take(...)