根据条件返回某些记录 [英] Return certain record based on criteria

问题描述

获取此结果列表

  Date     | YESorNO
---------------------
01/01/2001 | NO
01/01/2002 | NO
01/01/2003 | YES
01/01/2004 | NO
01/01/2005 | NO
01/01/2006 | NO
01/01/2007 | YES
01/01/2008 | YES
01/01/2009 | YES

列表是有序的，除了降序/升序外，我无法通过其他任何方式对其进行排序.

The list is ordered and I cannot sort this is any other way other than descending / ascending.

在所有否"都被考虑后，我希望能够返回第一个是".

I want to be able to return the first 'YES' after all 'NO's have been accounted for.

在上面的示例中，第7行是我要返回的记录(2007年1月1日)

In the above example, row 7 is the record I want returned (on 01/01/2007)

我的代码如下

var query = 
(
    from m in db.MyTable
    where m.Criteria == XYZ
    select new
    {
      Date = m.Date, 
      YESorNO = m.YESorNO
    }
).OrderBy(x => x.Date);

使用.FirstOrDefault(x => x.YesOrNO == "YES")返回第三条记录.

感谢您的帮助.

推荐答案

您可以通过对降序进行排序并获取第一组是"中的最后一个元素来完成此操作:

You can do this by ordering descending and take the last element of the first group of "YES":

var query = 
(
    from m in db.MyTable
    where m.Criteria == XYZ
    select new
    {
      Date = m.Date, 
      YESorNO = m.YESorNO
    }
).OrderByDescending(x => x.Date);

var result = query.AsEnumerable()
                  .TakeWhile(x => x.YESorNO == "YES")
                  .LastOrDefault();

我在这里使用AsEnumerable()，因为我不确定哪些运算符可转换为SQL，TakeWhile肯定不是.

I use AsEnumerable() here, because I'm not sure which operators are translatable into SQL, TakeWhile is surely not.

如果最后一个否"之后没有是"，则返回null.

If there is no "YES" after the latest "NO", this returns null.

这篇关于根据条件返回某些记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！