CODEWITHSUNDEEP
c#sqllinq
Skaterhaz
Skaterhaz

Reputation: 299

Return certain record based on criteria

Take this list of results

  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.

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

My code is as follows

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

Using .FirstOrDefault(x => x.YesOrNO == "YES") returns the 3rd record.

Thanks for any help.

Upvotes: 0

Views: 75

Answers (2)

Alex S
Alex S

Reputation: 111

var query = db.MyTable
    .Where(m.Criteria == XYZ)
    .Select(e => new { Date = e.Date, YESorNO = e.YESorNO })
    .OrderBy(e => x.Date);

var latestNODate = query.Where(ee => ee.YESorNO == "NO").Last().Date;
var element = query.Where(e => e.Date > latestNODate && e.YESorNO == "YES").FirstOrDefault();

Upvotes: 0

René Vogt
René Vogt

Reputation: 43876

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();

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

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

Upvotes: 5

Related Questions