CODEWITHSUNDEEP
c#linqlinq-to-entitiesentity-framework-5
Cyber Slueth Omega
Cyber Slueth Omega

Reputation: 399

How do I find max date for matching entities by entity.ids using linq

How do I use linq to find the last cat status for matching cat ids using mvc 4, ef5, and linq?

Example doesn't currently work right b/c max doesn't allow for datetime only ints, dec, etc... I would have to break this up into like at least 7 or 8 max checks for each part of a date time like years, months, days, hours, minutes, seconds, miliseconds, etc... but am searching for a better way than this:

cat.CatStatuses = dbCat.CatStatuses.Where(catstatus => catstatus.id == catstatus.id).Where(catstatus => catstatus.date == (dbCat.CatStatuses.Max(catstatus.date)));

Is there a better more efficient way to do this in linq, mvc 4, and entity framework? I was trying to use the func statement to make this work better.

Cat status might be like: "lay down", "get up", "growl", "meow", "scratch master", "climb", "dine", "catch mouse"

Cat status date is the date and time the status occurred.

My question is how do I get last cat status by the max cat status date in the most efficient manner using linq in an ef5 environment?

Upvotes: 2

Views: 1300

Answers (3)

Danny Varod
Danny Varod

Reputation: 18068

Jonni's suggestion was good, however, you should reduce the data you select like so:

dbCat.CatStatuses =
    dbCat.CatStatuses.Where(cs => cs.id == catStatusId)
        .OrderByDescending(cs => cs.Date)
        .Select(cs => cs.status)
        .FirstOrDefault();

This is more or less equivalent to:

select top(1)
    status
from DbCat.dbo.CatStatuses cs
where cs.id == @catStatusId
order by cs.Date desc

or perhaps more like:

select top(1)
    status
    from
    (
        select status, date
        from DbCat.dbo.CatStatuses cs
        where cs.id == @catStatusId
        order by cs.date desc
    ) x

Which is efficient if you have the right indexes.

Upvotes: 0

jonni
jonni

Reputation: 338

//where the variable catStatusId contains the value of the id you want.
cat.CatStatuses = dbCat.CatStatuses.Where(catstatus => catstatus.id == catStatusId).OrderByDescending(catstatus=>catstatus.Date).FirstOrDefault();

Upvotes: 1

haim770
haim770

Reputation: 49095

Try:

dbCat.CatStatuses.OrderBy(x => x.date).Last();

Upvotes: 4

Related Questions