CODEWITHSUNDEEP
linqlinq-to-entities
Bryan
Bryan

Reputation: 3

Get sub objects of sub objects collections

I am having difficulty with using Linq against a dbContext, getting the counts of objects where collections of child object of child objects have particular highest values.

I have the classes Request, Event, User. My simplified object graph is like this:-

Request                        Event                  User
  |                             |                      |-> Username
  |-> EventsCollection          |-> Description
  |-> User                      |-> WhenDone

I'm sure the domain model could be altered but regardless, using the current structure what I want is the count of the number of requests by a particular user where the most recent event has a particular description.

I initially thought something like the following would give me the value I am looking for

value = _db.Requests.Where(r => r.User.UserName.Equals("username") &&  r.Events.OrderByDescending(e => e.WhenDone).First().Description.Equals( "description")).Count();

However on running this I get an error 'First' can only be used as a final query operation changing it I get other errors such as unsupported method etc.

I'm guessing I ought to use sub queries but haven't been able to get the syntax right.

Any pointers to useful sources or suggested solutions would be very much appreciated

Upvotes: 0

Views: 95

Answers (1)

Phil
Phil

Reputation: 43011

Try something like this:

int count = _db.Requests.Where(r => r.User.UserName == "username")
               .Select(r => 
                   r.Events.OrderByDescending(e => e.WhenDone).FirstOrDefault())
               .Count(r => r != null && r.Description == "description");

Upvotes: 1

Related Questions