基于参数构建linq查询

本文关键字：构建 linq 查询 参数 于参数 | 更新日期: 2023-09-27 17:59:01

我正试图根据数据表中的参数创建一个查询。我正在尝试执行类似于以下的操作，只是我不断收到错误，并且必须在每个case语句中基本上用一个不同的参数重新创建整个linq查询，即OrderBy或OrderByDecending。

是否有一种基于参数构建查询的方法？

 public JsonResult IndexData(DTParameterViewModel param)
    {
          IEnumerable<DataTableRowViewModel> rows = (from j in jobs select j)
                       .Skip(param.Start)
                       .Take(param.Length)
                        .AsEnumerable()
                       .Select(j => new DataTableRowViewModel
                       {
                           Id = j.Id,
                           ArrivalDate = j.ArrivalDate.ToString(Constants.DATE_FORMAT),
                           DueDate = j.DueDate?.ToString(Constants.DATE_TIME_FORMAT),
                           Contact = j.Contact,
                           Priority = j.Priority.GetDisplayName(),
                           JobType = j.JobType.GetDisplayName(),
                           SapDate = j.SapDate.ToString()
                       });
       foreach(DTOrder order in param.Order)
        {
            ascDesc = order.Dir;
            switch (order.Column)
            {
                case 0:
                    orderCol = 0;
                    colName = "Id";
                    if (ascDesc == "desc")
                    {
                        rows = (from j in jobs select j)
                        .OrderByDescending(j => j.Id);
                    }
                    else
                    {
                        rows = (from j in jobs select j)
                       .OrderBy(j => j.Id)
                    }
                    break;
                case 1:
                    orderCol = 1;
                    colName = "ArrivalDate";
                    if (ascDesc == "desc")
                    {
                        rows = (from j in jobs select j)
                       .OrderByDescending(j => j.ArrivalDate)
                    }
                    else
                    {
                        rows = (from j in jobs select j)
                       .OrderBy(j => j.ArrivalDate)
                    }
                    break;
    }

基于参数构建linq查询

有更复杂的方法，但我发现这是最容易阅读的，尤其是对LINQ初学者来说：

var first=true;
foreach(DTOrder order in param.Order)
{
  switch(order.Column)
  {
    case 0:
      if (first)
      {
        rows=(order.Dir=="asc")?rows.OrderBy(r=>r.Id):rows.OrderByDescending(r=>r.Id);
      } else {
        rows=(order.Dir=="asc")?rows.ThenBy(r=>r.Id):rows.ThenByDescending(r=>r.Id);
      }
      break;
    case 1:
      ...
  }
  first=false;
}

然而，通常情况下，您会想在订单后进行Take和Skip，所以您会这样做：

public JsonResult IndexData(DTParameterViewModel param)
{
  // Start with the base query
  var rows = jobs.AsQueryable();
  // Order the query
  var first=true;
  foreach(DTOrder order in param.Order)
  {
    switch(order.Column)
    {
      case 0:
        if (first)
        {
          rows=(order.Dir=="asc")?rows.OrderBy(r=>r.Id):rows.OrderByDescending(r=>r.Id);
        } else {
          rows=(order.Dir=="asc")?rows.ThenBy(r=>r.Id):rows.ThenByDescending(r=>r.Id);
        }
        break;
      case 1:
        ...
    }
    first=false;
  }
  // Partition the query
  rows=rows
    .Skip(param.Start)
    .Take(param.Length)
    .AsEnumerable(); // Or place the AsEnumerable in the projection
  // Project the query
  var result=rows.Select(j => new DataTableRowViewModel
    {
      Id = j.Id,
      ArrivalDate = j.ArrivalDate.ToString(Constants.DATE_FORMAT),
      DueDate = j.DueDate?.ToString(Constants.DATE_TIME_FORMAT),
      Contact = j.Contact,
      Priority = j.Priority.GetDisplayName(),
      JobType = j.JobType.GetDisplayName(),
      SapDate = j.SapDate.ToString()
    });
   return result;
}

进一步优化（假设作业来自数据库），然后您应该进行中间投影以限制返回的列，更改：

  // Partition the query
  rows=rows
    .Skip(param.Start)
    .Take(param.Length)
    .AsEnumerable(); // Or place the AsEnumerable in the projection
  // Project the query
  var result=rows.Select(j => new DataTableRowViewModel

到此：

  // Partition the query
  var result1=rows
    .Skip(param.Start)
    .Take(param.Length)
    /* Selecting only the fields we need */
    .Select(j=> new {
      j.Id, 
      j.ArrivalDate,
      j.DueDate,
      j.Contact,
      j.Priority,
      j.JobType,
      j.SapDate
    })
    .AsEnumerable(); // Or place the AsEnumerable in the projection
  // Project the query
  var result=result1.Select(j => new DataTableRowViewModel
相关文章：