How to use where in relation with EntityFramework?

Question

I have two models that are related. Cars and announcements. Announcement and car ha one-to-one relationship.

How can I include a where in the relation?

updated: the name of the property is announcement not announcements and it is not a collection.

this.context.Cars.Include(a => a.announcement); // stuck here, I want to find the
                                                // announcements that are active.

It is unclear what you want. Do you want to (a) load all cars and their active announcements, (b) cars which have an active announcement, (c) all cars and all announcements of cars which have at least one active announcement, ...? It's hard to answer a question when you don't explain what it is you want. — Flater, Mar 21 '20 at 14:35
My original problem is only select the cars that have an active annoucement. In another worlds only car data is needed. — Diego Alves, Mar 21 '20 at 16:16

score 1 · Accepted Answer · answered Mar 21 '20 at 14:33

1

For a Where() on the Car:

this.context.Cars.Include(c => c.announcements).Where(c => c.Value == value);

Note, I've change your 'a' to a 'c' on the Include() because it represents a car, not an announcement.

For a Where() on the Announcement:

this.context.Cars.Where(c => c.announcements.Value == value);

Note, there is no need to Include() announcements in order to look at it in the Where(). You only need Include() if you're going to read announcements data in the programme once the query is executed.

answered Mar 21 '20 at 14:33

Jasper Kent

3,546
15
21

` c.announcements.Value` does not work, since `announcements` is a collection (even if it contains at most one element). – Olivier Jacot-Descombes Mar 21 '20 at 15:03
I was puzzled by this, since we're told it's a one-to-one relationship between Announcement and Car. It doesn't matter either way. Car equally does not have a property called Value. I'm simply putting something in to fill out the Where(). – Jasper Kent Mar 21 '20 at 15:10
Maybe you are right and `announcements` is just a reference to an announcement object, but the plural form of the name suggests it's a collection. Since the OP has not shown his classes, this is unclear. – Olivier Jacot-Descombes Mar 21 '20 at 15:18
Sorry, announcement is not a collection. It's a one-to-one relationship. I've translated my code and I did a mistake. – Diego Alves Mar 21 '20 at 16:07

Olivier Jacot-Descombes · Answer 2 · 2020-03-21T17:58:06.947

Note: since you have use the plural form for the announcements property, I deduced it's a collection. My answer is based on this assumption. Btw., the rule is to use PascalCase for properties.

Include always includes all the related records. You have to make the test afterwards:

IEnumerable<Announcement> activeAnnouncements = context.Cars
    .Include(c => c.announcements)
    .SelectMany(c => c.announcements)
    .Where(a => a.IsActive);

Note that SelectMany flattens nested sequences. Here, it produces a sequence of all announcements in all the cars.

If you had a one-to-many relationship and needed the cars together with the active announcements, you could combine the two in a ValueTuple:

IEnumerable<(Car c, Announcement a)> carsAndActiveAnnouncements = context.Cars
    .Include(c => c.announcements)
    .SelectMany(c => c.announcements, (c, a) => (c, a)) // 1st (c, a) are lambda parameters,
                                                        // 2nd creates tuple.
    .Where(ca => ca.a.IsActive);

Get all the cars with active announcements, but including all the announcements (in a one-to-one relationship, this is always a single active announcement):

IEnumerable<Car> carsHavingActiveAnnouncements = context.Cars
    .Include(c => c.announcements)
    .Where(c => c.announcements.Any(a => a.IsActive));

And finally, you can add this property to the Car class (expression bodied syntax):

public IEnumerable<Announcement> ActiveAnnouncements =>
    announcements.Where(a => a.IsActive);

Same as (full syntax):

public IEnumerable<Announcement> ActiveAnnouncements
{
    get {
        return announcements.Where(a => a.IsActive);
    }
}

It lets you retrieve the active announcements easily at any time. The result is accurate even after edits.

But since, in this case, you have a one-to-one relationship, this does not help much.

Update: Since, according to your update, announcement is not a collection and since you want to select the cars having an active announcement, here is my new solution:

IEnumerable<Car> carsHavingAnActiveAnnouncement = context.Cars
    .Include(c => c.announcement)
    .Where(c => c.announcement.IsActive);

Note that the Include is not required for the Where clause to work, as it will be translated to SQL and does not rely on an object reference. But it is of course legitimate, if you want the announcements to be loaded.

Note that this get all cars which have at least one active announcement, and loads all of those cars' announcements, including the inactive ones. It's only going to omit cars without active announcements. I infer (but it is just an educated guess) from OP's question that they only want to retrieve only active announcements, which this answer doesn't do. — Flater, Mar 21 '20 at 14:34

Flater · Answer 3 · 2020-03-21T18:06:26.973

You elaborated on the purpose of the query in a comment:

My original problem is only select the cars that have an active annoucement. In another worlds only car data is needed.

If you don't need to load the announcement data, then you don't need to use Include.

I suspect this may be a confusion between EF/LINQ and SQL. In SQL, you have to join your data (= "include" it in the data set) before you can use it, regardless of whether you intend to use it in a SELECT, WHERE, or other.
But with EF/LINQ, you only need to use Include in a very specific circumstance: when you want to load this data in the result set, and when you don't already use a Select() to return a custom type (because this overrides the include behavior anyway).

What you want is to filter the data, and that doesn't require you to include it. You can simply call Where() to filter the data appropriately:

var carsWithAnActiveAnnouncement = db.Cars
                                     .Where(c => c.announcement.IsActive)
                                     .ToList();

Note: I assumed the property is called IsActive for the sake of example. You can correct this if needed.

This gives you the correct filtered list of cars without actually loading the announcements data, since you said you don't need it.

How to use where in relation with EntityFramework?

3 Answers3