c# - Include() and ThenInclude() with nullable navigation properties - Stack Overflow

I have the following code to fetch data.

Note how I have used the null coalescing operator ?? to substitute a dummy clinic if childClinic.Clinic is null so I can still use ThenInclude() on the Clinic nav properties.

I don't like this and I'm not sure it will work.

My questions are:

1. Is using new() inside an Include() a good/safe practice for now and future EF revisions?
2. Is using new() inside a ThenInclude() a good/safe practice for now and future EF revisions?
3. Is there a better way to write this, which still would not require splitting up into multiple queries or lazy loading clinic!.Base and clinic!.

NB: Entities returned will not have change tracking. They will be read only. I have not included the code for CreateBaseQueryable method here as it only returns the DbContext as IQueryable.

Thanks in advance.

    List<Child> children = await CreateBaseQueryable(context, healthBoardId)
        .Include(child => child.ChildStatuses)
        .Include(child => child.ChildClinics)
            .ThenInclude(childClinic => childClinic.Clinic ?? new Clinic())
                .ThenInclude(clinic => clinic!.Base)
        .Include(child => child.ChildClinics)
            .ThenInclude(childClinic => childClinic.Clinic ?? new Clinic())
                .ThenInclude(clinic => clinic!.Board)
        .Where(child => child.ChildStatuses.Any(status => status.ACounter >= 2))
        .Where(child => child.ChildClinics.Any(
            clinic => !clinic.IsDeleted &&
            clinic.Clinic != null &&
            (
              clinic.Clinic.ClinicType == (int)ClinicType.Type1||
              clinic.Clinic.ClinicType == (int)ClinicType.Type2)
            )
         )
         .ToListAsync();

I have the following code to fetch data.

Note how I have used the null coalescing operator ?? to substitute a dummy clinic if childClinic.Clinic is null so I can still use ThenInclude() on the Clinic nav properties.

I don't like this and I'm not sure it will work.

My questions are:

1. Is using new() inside an Include() a good/safe practice for now and future EF revisions?
2. Is using new() inside a ThenInclude() a good/safe practice for now and future EF revisions?
3. Is there a better way to write this, which still would not require splitting up into multiple queries or lazy loading clinic!.Base and clinic!.

NB: Entities returned will not have change tracking. They will be read only. I have not included the code for CreateBaseQueryable method here as it only returns the DbContext as IQueryable.

Thanks in advance.

    List<Child> children = await CreateBaseQueryable(context, healthBoardId)
        .Include(child => child.ChildStatuses)
        .Include(child => child.ChildClinics)
            .ThenInclude(childClinic => childClinic.Clinic ?? new Clinic())
                .ThenInclude(clinic => clinic!.Base)
        .Include(child => child.ChildClinics)
            .ThenInclude(childClinic => childClinic.Clinic ?? new Clinic())
                .ThenInclude(clinic => clinic!.Board)
        .Where(child => child.ChildStatuses.Any(status => status.ACounter >= 2))
        .Where(child => child.ChildClinics.Any(
            clinic => !clinic.IsDeleted &&
            clinic.Clinic != null &&
            (
              clinic.Clinic.ClinicType == (int)ClinicType.Type1||
              clinic.Clinic.ClinicType == (int)ClinicType.Type2)
            )
         )
         .ToListAsync();

• c#
• entity-framework-core
• eager-loading

Share Improve this question Follow edited Jan 29 at 9:39 Scott asked Jan 29 at 9:05 ScottScott 96933 gold badges99 silver badges3030 bronze badges 5

• Nullability isn't an issue. Using ?? causes issues. LINQ queries don't get executed themselves. The LINQ query gets translated to SQL, where NULL behaves very differently from programming languages. – Panagiotis Kanavos Commented Jan 29 at 9:36
• It is even translatable? – Svyatoslav Danyliv Commented Jan 29 at 9:56
• You could have answered the first two questions yourself because the query doesn't even run. – Gert Arnold Commented Jan 29 at 11:21
• @GertArnold I haven't run it myself, its what was suggested by my linter and it looked suspicious. I did say in the OP I don't like it, and it doesn't look like it will work. I was curious to see if there was a way of achieving the same thing without using new() to compensate for nullable navigation properties. – Scott Commented Jan 30 at 11:57
• In fact, nobody answered the question how (or even whether) to compensate for null navigation properties. To me, it remains a question if you should want to replace null Clinics by new Clinic(). Personally, I'd prefer not to. – Gert Arnold Commented Jan 30 at 15:40

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

Include and ThenInclude don't care if the properties are null/nullable or not, just for the sake of issuing an INNER vs an LEFT JOIN. It's not the same as LINQ to Objects, or when you access a null property directly.

To expand on Ricardo's answer: When you use Linq to build a query, the use of Include, ThenInclude, and even Select, is not limited to whether a related entity or property is available or not. These functions ultimately are used to produce an SQL statement that will result in data coming back. So you just need:

.Include(child => child.ChildClinics)
    .ThenInclude(childClinic => childClinic.Clinic)
        .ThenInclude(clinic => clinic.Base)
.Include(child => child.ChildClinics)
    .ThenInclude(childClinic => childClinic.Clinic)
        .ThenInclude(clinic => clinic.Board)

When you go through the list of resulting Child entities then you need to account for the possibility of a null child clinic and/or Base/Board. The same goes for using Select when projecting data. If you wanted a Clinic.Base.Name and Clinic.Board.Name:

.Select(child => new ChildDto
{
    ChildId = child.Id,
    // ...
    ClinicBaseName = child.ChildClinic.Base.Name,
    ClinicBoardName = child.ChildClinic.Board.Name
}).ToList();

Attempting to use child?.ChildClinic?.Base?.Name won't translate down to SQL. EF will populate a default value if any of the navigation properties are #null.