Getting MAX Children query RRS feed

  • Question

  • I have 2 DB tables:

    Items(ItemID, Title, Category)

    ItemVersions(ItemID, VersionNumber, SomeData)

    How do I write a LINQ query that given a category will return the most recent version for each Item of that category?

    Wednesday, December 17, 2008 4:28 PM

All replies

  • You could try this:

    from i in db.Items
    where i.Category == someCategory
    select new {
         Item = i,
         LatestVersion = (from v in i.Versions
                                    orderby v.VersionNumber descending
                                    select v).First()

    Wayward LINQ Lacky
    Thursday, December 18, 2008 5:15 AM
  • Hi,

    Thanks for the reply. Your query does exactly what I asked for but does result in n + 1 database hits where n is the number of items in that category.

    Is there anything I can do about this?
    Thursday, December 18, 2008 9:46 AM
  • Anyone?

    Both the query provided by Matt and this similar one hit the database numerous times. Is there a way around this?

    from i in Items
    where i.CategoryName == someCategory
    select i.Versions.OrderByDescending(v => v.VersionNumber).Take(1);
    Friday, December 19, 2008 12:27 PM
  • from i in Items
    where i.CategoryName == someCategory
    from j in i.Versions.OrderByDescending(v => v.VersionNumber).Take(1)
    select j

    That should just get you one query with a join.

    Wayward LINQ Lacky
    Saturday, December 20, 2008 5:13 PM