LINQ to SQL lambda exp. OrderBy, Case When

Question

Hi guys!

Going to need your help on this one.

I'm trying to OrderBy first reply datetime if present. If it's empty/null, it must order by topic datetime. I've ended up with the following expression, but it just doesn't seem to work :(

return db.Topics
.Where(t => t.ForumID == id)
.OrderBy(
    t => t.Replies
    .OrderBy(r => r.AddDatetime.Equals(System.Data.SqlTypes.SqlDateTime.Null.Value) ? t.AddDatetime : r.AddDatetime)
    .Select(r => r.AddDatetime)
    .First()
);

Answer 1

You will need two Linq statements, one for each OrderBy. Use an If statement to decide which Linq statement to return.

The problem with your existing code is that the lambda is expecting a reference to a field, but the result of the ternary operation is a value.

Answer 2

If you can live with returning a POCO object you can do:

var pocoenum = db.Topics
.Where(t => t.ForumID == id)
.Select(
new {
    ...
    AddDatetime = t.Replies.Select(r => r.AddDateTime).FirstOrDefault == null ? t.AddDatetime : t.Replies.Max(r => r.AddDateTime)
};

return pocoenum.OrderBy(p => p.AddDatetime)

SQL will not be most beautiful though.

Answer 3

why cant you just do

return db.Topics .Where(t => t.ForumID == id) .OrderBy( t.Replies.Min(r=>(DateTime?)r.AddDateTime) ?? t.AddDateTime) .Select(r => r.AddDatetime) .First()

if r.AddDatetime itself is nullable then you can remove the cast, otherwise you need that cast in to make sure Min function returns DateTime? and not DateTime