C#•9mo ago

Entity Framework IQueryable question

I'm trying to get a list of prescriptions from my Patients entity:

        var kk = await _context.Patients.Where(p => p.Id == id)
            .Include(p => p.Prescriptions)
            .ThenInclude(p => p.Medicine)
            .Include(p => p.Prescriptions)
            .ThenInclude(p => p.Doctor)
            .ToListAsync();

        var gg = await _context.Patients.Where(p => p.Id == id)
            .Include(p => p.Prescriptions)
            .ThenInclude(p => p.Medicine)
            .Include(p => p.Prescriptions)
            .ThenInclude(p => p.Doctor)
            .Select(p => new
            {
                Prescription = p.Prescriptions.Select(p => p.ToDto())
            })
            .ToListAsync();

kk works, but gg returns an empty list;. Any ideas why this might be happening? I know it's a short snippet, but I figured there would be something obvious I am doing wrong... Thanks!

19 Replies

Angius•9mo ago

If you use a select you don't need includes btw

CyclomaticOP•9mo ago

hmm. But why is p.Prescriptions empty? In the case of gg that is. gg is of Count = 1. kk is also of count one, but kk has a non empty Prescriptions

Angius•9mo ago

It would be empty if the patient with a given ID has no prescriptions Should be .FirstOrDefaultAsync() too, btw

CyclomaticOP•9mo ago

It's like the Include isn't applying to the Select. FYI I'm trying to use the Select with the anonymous type because (when I figure it out) I want to return null if the patient isn't found, but one problem at a time

Angius•9mo ago

Seeing how, I assume, there's only one patient with the ID

CyclomaticOP•9mo ago

yeah, but kk works, for the same id... To me, that's saying the Include isn't applying in the linq Select

Angius•9mo ago

Includes are useless with a select

        var gg = await _context.Patients
            .Where(p => p.Id == id)
            .Select(p => new
            {
                Prescription = p.Prescriptions.Select(p => p.ToDto())
            })
            .FirstOrDefaultAsync();

        var gg = await _context.Patients
            .Where(p => p.Id == id)
            .Select(p => new
            {
                Prescription = p.Prescriptions.Select(p => p.ToDto())
            })
            .FirstOrDefaultAsync();

this should be all

CyclomaticOP•9mo ago

hmm ok. Now I'm wondering why I went down the Include route 😄 Now I remember My PrescriptionEntity has a MedicineEntity and it is always null. Include was solving that problem

Angius•9mo ago

Perhaps your ToDto() method doesn't map that property

CyclomaticOP•9mo ago

Sure, that's where the exception occurs. Because PrescriptionEntity.Medicine is null Isn't it true that when I call _context.Patients.Where(p => p.Id == id) it finds Patients but it doesn't look up it's dependent properties?

Angius•9mo ago

What you have in the .Select() will look up those properties, though

CyclomaticOP•9mo ago

But it doesn't 😄 For example, this works:

        var kk = _context.Patients
            .Where(p => p.Id == id)
            .Include(p => p.Prescriptions)
            .ThenInclude(p => p.Medicine).ToList();


        var prescriptions    = kk.Select(p => new
            {
                Prescription = p.Prescriptions.Select(p => p.ToDto())
            })
            .FirstOrDefault();

Only if I add the Includes. If I don't then Medicine is null

Angius•9mo ago

What's your ToDto?

CyclomaticOP•9mo ago

https://github.com/billymaat/MedTrackDash/blob/zzz/MedTrackDash/Services/PatientDatabaseService.cs#L180 I've branched out if you have time to take a peek/give it a whirl?

GitHub

MedTrackDash/MedTrackDash/Services/PatientDatabaseService.cs at zzz...

Contribute to billymaat/MedTrackDash development by creating an account on GitHub.

CyclomaticOP•9mo ago

For the ToDto(): https://github.com/billymaat/MedTrackDash/blob/zzz/MedTrackDash/Extensions/PrescriptionMapper.cs#L19

GitHub

MedTrackDash/MedTrackDash/Extensions/PrescriptionMapper.cs at zzz ·...

Contribute to billymaat/MedTrackDash development by creating an account on GitHub.

Angius•9mo ago

Huh, I wonder... It's probably because EF can't understand those methods for mapping, since they're not expressions So it fetches everything and maps on the client-side That's why includes are needed, to actually load everything I'd check what SQL does it actually output

CyclomaticOP•9mo ago

Thanks for checking 🙂 I'll take a look at the SQL tomorrow, I'll need to add some proper logging. Glad it's not just immediately obvious.

CyclomaticOP•9mo ago

Just to add a few relevant links that also solve the problem: https://stackoverflow.com/questions/35316939/ef7-projection-doesnt-eager-load-collections https://owenashurst.com/post/how-to-use-include-with-select-in-ef-core-net-core

Stack Overflow

EF7 projection doesnt eager load collections

When selecting entities with "include" all my items gets fetched with a single SQL join statement. But when i project it to some other form with its children, the join is no longer executed, instea...

CyclomaticOP•9mo ago

So, it seems I need AsEnumerable() when I am using the InMemoryDatabase(), and it seems that when I use an SQL database I no longer need AsEnumerable(). Secondly, just using Select(...) without any Include doesn't work at all, which I think makes sense? Without the Include and ThenInclude:

SELECT EXISTS (
    SELECT 1
    FROM

Patients AS p

)
[00:36:21 INF] Executed DbCommand (1ms) [Parameters=[@__id_0='10'], CommandType='"Text"', CommandTimeout='30']
SELECT

t.Id, p0.Id, p0.DoctorId, p0.EndDate, p0.IssueDate, p0.PatientId, p0.StartDate


FROM (
    SELECT

p.Id


    FROM

Patients AS p


    WHERE

p.Id

 = @__id_0
    LIMIT 1
) AS


LEFT JOIN

Prescriptions AS p0 ON t.Id = p0.PatientId


ORDER BY

t.Id`` So it only performs a LEFT JOIN on Prescriptions, not on the children of Prescriptions

Gaming

Programming

Entity Framework IQueryable question