CODEWITHSUNDEEP
c#entity-frameworklinq
Kevin
Kevin

Reputation: 4848

Querying across relationships with LINQ to Entity Framework

Here is what my model looks like: enter image description here

I'm trying to get the count of distinct Assesors by a certain EventId.

Here's the code I'm trying to use:

var x = db.Assessors.Select(a => (a.Assessments.Select(y => y.EventFacility.EventId == 138))).Count();

Unfortunately, I must be coding this wrong because instead of getting the expected result (a count of 9, in this case) I'm getting the wrong result: 35.

I'm wondering if someone can take a look at my LINQ statement and tell me what I'm doing wrong?

Upvotes: 0

Views: 49

Answers (2)

Ronan Thibaudau
Ronan Thibaudau

Reputation: 3603

You're going about this backward, start from what you know (the event since you have it's ID) and navigate to what you want through the navigation properties.

It's impossible to tell from your schema as it includes neither the properties nor the mapping type 1:1? 1:N? Can't know from simple lines

It would probably look Something like this

var x = db.Events
           .Where(ev=>ev.Id == 138)
           .SelectMany(ev=>ev.EventFacilities) //(i'm assumine possibly multiple and not 1 per event, once again schema doesn't show it, if it's not the case change SelectMany to Select)
           .SelectMany(ef=>ef.Assesments) // Same assumption as above
           .Select(as=>as.Assessor) // Asuming otherwise here, if wrong change Select to SelectMany
           .Distinct(); // Ignore duplicate assessors

Note that your question is impossible to answer as is, this is a best effort but if you want help you should give "all" the information required, not strip out what doesn't immediately seem relevant, it would've been much easier if you took an actual screenshot of your entity diagram instead of what you made up.

Upvotes: 0

Yacoub Massad
Yacoub Massad

Reputation: 27861

You need to use Where and Any like this:

var result = db.Assessors
            .Where(a => a.Assessments.Any(y => y.EventFacility.EventId == 138));

What this is saying is that you want all Assessors that are parents of any Assessment that is related to that particular Event.

Upvotes: 2

Related Questions