CODEWITHSUNDEEP
linq-to-sqldatetimelambdasql-order-bycase
Kordonme
Kordonme

Reputation: 2294

LINQ to SQL lambda exp. OrderBy, Case When

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

Upvotes: 0

Views: 4197

Answers (3)

Bob Vale
Bob Vale

Reputation: 18474

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

Upvotes: 1

aanund
aanund

Reputation: 1493

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.

Upvotes: 1

Robert Harvey
Robert Harvey

Reputation: 180787

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.

Upvotes: 0

Related Questions