The Include property lambda expression [...] is invalid. The expression should represent a property access

Question

I'm trying to get all the Hotfix and include all the details (associated with it) where the property Available is 1. This is my code:

public static IList<HotFix> GetAllHotFix()
{
    using (Context context = new Context())
    {
        return context.HotFix
            .Include(h => h.AssociatedPRs)
            .Include(h => h.Detail.Where(d => d.Available = 1))
            .ToList();
    }
}

And I'm getting that error. I tried using .ThenInclude but couldn't solve it.

Inside HotFix I have:

[Required]
public virtual List<HotFixDetail> Detail { get; set; }

Please post the code for `HotFix` and also for `HotFixDetail` — Darren Ruane, Aug 13 '19 at 14:19
Can't share much but I added to my post the property with the List. If it's worth something, if I remove the `where` it works fine (but brings all the details) — nick, Aug 13 '19 at 14:22
Do you need the `HotFixDetail` items or are you just using them to filter? — DavidG, Aug 13 '19 at 14:24
@DavidG, I need them, I'm using them later to display some info — nick, Aug 13 '19 at 14:25
Then you probably need to start with `HotFixDetail` and work back to `HotFix`. So, something like `context.HotFixDetail.Where(d => d.Available == 1)....` — DavidG, Aug 13 '19 at 14:26
d.Available = 1 Are you sure? Shouldn't it read d.Available == 1 ? — Daniel Schmid, Aug 13 '19 at 14:29
My guess is your options are: (1) Include all HotFixDetails in your query and filter them from the results (2) Don't include HotFixDetails in your query and retrieve them on demand (if this is an option given your UI) (3) Query all Hotfixes without details, query all Available details, match up the two sets. — JLRishe, Aug 13 '19 at 14:38
You can also consider having a property `AvailableDetail` on your `HotFix` model that handles the filtering and makes this available elsewhere, for example: `public List AvailableDetails { get Detail.Where(d => d.Available == 1);}` — Canica, Aug 13 '19 at 14:41
Possible duplicate of [Filtering on Include in EF Core](https://stackoverflow.com/questions/43618096/filtering-on-include-in-ef-core) — NetMage, Aug 13 '19 at 20:52

score 5 · Accepted Answer · answered Aug 15 '19 at 10:52

Although you forgot to write your class definitions, it seems that you have a HotFix class. Every HotFix has a sequence of zero or more AssociatedPRs and a sequence of zero or more Details.

Ever Detail has at least one numeric property Available.

You want all HotFixes, each with all its AssociatedPRs, and all Details that have a property Available value equal to 1 (didn't you mean that available is a Boolean?)

When using entity framework, people tend to use include to get an item with its sub-items. This is not always the most efficient method, as it gets the complete row of a table, inclusive all the properties that you do not plan to use.

For instance, if you have a one-to-many relationship, Schools with their Students, then each Student will have a foreign key to the School that this `Student attends.

So if School [10] has 1000 Students, then every Student will have a foreign key to the School with a value 10. If you use Include to fetch School [10] with its Students, then this foreign key value is also selected, and sent a 1000 times. You already know it will equal the Schools primary key value, hence it is a waste of processing power to transport this value 10 a 1001 times.

When querying data, always use Select, and Select only the properties you actually plan to use. Only use Include if you plan to update the fetched data.

Another good advice is to use plurals to describe sequences and singulars to describe one item in your sequence

Your query will be:

var result = context.HotFixes.Select(hotfix => new
{
    // Select only the hotfix properties you actually plan to use:
    Id = hotfix.Id,
    Date = hotfix.Date,
    ...

    AssociatedPRs = hotfix.AssociatedPRs.Select(accociatedPr => new
    {
        // again, select only the associatedPr properties that you plan to use
        Id = associatedPr.Id,
        Name = associatedPr.Name,
        ...

        // foreign key not needed, you already know the value
        // HotFixId = associatedPr.HotFixId
    })
    .ToList(),

    Details = hotfix.Details
        .Where(detail => detail.Available == 1)
        .Select(detail => new
        {
            Id = detail.Id,
            Description = detail.Description,
            ...

            // not needed, you know the value:
            // Available = detail.Available,

            // not needed, you know the value:
            // HotFixId = detail.HotFixId,
        })
        .ToList(),
});

I used anonymous type. You can only use it within the procedure in which the anonymous type is defined. If you need to return the fetched data, you'll need to put the selected data in a class.

return context.HotFixes.Select(hotfix => new HotFix()
{
    Id = hotfix.Id,
    Date = hotfix.Date,
    ...

    AssociatedPRs = hotfix.AssociatedPRs.Select(accociatedPr => new AssociatedPr()
    {
       ... // etc

Note: you still don't have to fill all the fields, unless your function requirement specifically states this.

It might be confusing for users of your function to not know which fields will actually be filled and which ones will not. On the other hand: when adding items to your database they are already accustomed not to fill in all fields, for instance the primary and foreign keys.

As a solution for that not all fields are filled, some developers design an extra layer: the repository layer (using the repository pattern). For this they create classes that represent the data that people want to put into storage and want to save into the storage. Usually those people are not interested in that the data is saved in a relational database, with foreign keys and stuff. So the repository classes won't have the foreign keys

The advantage of the repository pattern is, that the repository layer hides the actual structure of your storage system. It even hides that it is a relational database. It might also be in a JSON-file. If the database changes, users of the repository layer don't have to know about this, and probably don't need to change as well.

A repository pattern also makes it easier to mock the database for unit testing: since users don't know that the data is in a relational database, for the unit test you can save the date in a JSON file, or a CSV-file or whatever.

The disadvantage is that you need to write extra classes that holds the data that is to be put into the repository, or fetched from the repository.

Whether it is wise to add this extra layer or not, depends on how often you expect your database to change layout in the future, and how good your unit tests need to be.

The Include property lambda expression [...] is invalid. The expression should represent a property access

1 Answers1