CODEWITHSUNDEEP
c#linqentity-frameworklinq-to-entities
David C
David C

Reputation: 2806

LINQ Lambda: Getting Last item (where == ) of sub collection

I have the following (which I've used in CodeFirst):

public class MyForm
    {
        public int MyFormId { get; set; }
        //Many Form Properties
        public virtual ICollection<AuditItem> AuditItems { get; set; }
    }

public class AuditItem
    {
        public int AuditItemId { get; set; }
        public int MyFormId { get; set; }
        public IAppUser User { get; set; }
        public string ActionByUserStr { get; set; }
        public string ActionByUserDisplayName { get; set; }
        public DateTime DateOfAction { get; set; }
        public string TypeOfAction { get; set; }
        public int Step { get; set; }

        public virtual MyForm MyForm { get; set; }
    }

The AuditItem tracks many actions carried out on MyForm

I'm presenting an index page showing a table where the AuditItem's last Step = 1.

I do an orderby DateOfAction (because the Step can get setback to 0, so I wouldn't want it to equal 1 unless it is also the last AuditItem Record for that MyForm), then Select the last record's step, then query on that. I want to query as early as I can so that I'm not pulling back unrequired MyForms records.

This is the query I have:

var myFormsAtStep1 =
                context.MyForms.Include("AuditItems").Where(
                    mf => mf.AuditItems.OrderBy(ai => ai.DateOfAction).Last().Step == 1);

It gives this error:

LINQ to Entities does not recognize the method 'MyForms.Models.AuditItem Last[AuditItem](System.Collections.Generic.IEnumerable`1[MyForms.Models.AuditItem])' method, and this method cannot be translated into a store expression.

Upvotes: 1

Views: 13827

Answers (2)

sloth
sloth

Reputation: 101142

Have a look at the list of Supported and Unsupported LINQ Methods.

Last() is not supported, but First() is, so you can just go on and reverse your ordering and use First() instead of Last().

mf => mf.AuditItems.OrderByDescending(ai => ai.DateOfAction).First().Step == 1);

Upvotes: 6

CMircea
CMircea

Reputation: 3568

You simply sort in descending order and get the first item.

var myFormsAtStep1 =
    context.MyForms.Include("AuditItems").Where(
        mf => mf.AuditItems.OrderByDescending(
            ai => ai.DateOfAction).First().Step == 1);

I would also use the query syntax, which would be more readable, as well as the strongly-typed Include (import System.Data.Entity):

from mf in context.MyForms.Include(x => x.AuditItems)
let items = from ai in mf.AuditItems
            orderby ai.DateOfAction descending
            select ai;
where items.First().Step == 1
select mf

Upvotes: 2

Related Questions