LINQ Where Exists GROUP BY
How can i convert this in LINQ?
SELECT B.SENDER, B.SENDNUMBER, B.SMSTIME, B.SMSTEXT
FROM MESSAGES B
WHERE EXISTS ( SELECT A.SENDER
FROM MESSAGES A
WHERE A.SENDER = B.SENDER
GROUP BY A.SENDER
开发者_如何学编程HAVING B.SMSTIME = MAX( A.SMSTIME))
GROUP BY B.SENDER, B.SENDNUMBER, B.SMSTIME, B.SMSTEXT ;
Thanks a lot :)
EDIT!!
Resolved with:
var Condition = "order by SMSTime desc";
IEnumerable<ClassMessaggio> messaggi = Database.Select<ClassMessaggio>(Condition);; // Load all but sorted
ElencoConversazioni = messaggi.GroupBy(m => new { m.Number })
.Select(g => g.OrderByDescending(m => m.SMSTime).First()).ToObservableCollection();
Try
db.Messages.Where(b => b.SmsTime == Messages.Where(a => a.Sender == b.Sender)
.Max(a => a.SmsTime))
Or
db.Messsages.GroupBy(m => new { m.Sender, m.SendNumber })
.Select(g => g.OrderByDescending(m => m.SmsTime).First())
Where db
is your DataContext.
To show the list of conversations along with "the last message of every contact", you could try something like:
// a query to find the last Sms time per sender
var lastSmsQuery = from m in db.messages
group m by m.Sender into grouping
select new
{
Sender = grouping.Key,
LastSmsTime = grouping.Max(x => x.SmsTime)
};
// a query to find the messages linked to the last Sms per sender
var lastMessageQuery = from m in db.messages
join l in lastSmsQuery on new { m.Sender, m.SmsTime } equals new { l.Sender, l.LastSmsTime }
select m;
The 2-query method used is similar to that of this question from earlier today - Convert SQL Sub Query to In to Linq Lambda
精彩评论