C
C#Billy

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!
A
Angius12d ago
If you use a select you don't need includes btw
B
Billy12d 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
A
Angius12d ago
It would be empty if the patient with a given ID has no prescriptions Should be .FirstOrDefaultAsync() too, btw
B
Billy12d 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
A
Angius12d ago
Seeing how, I assume, there's only one patient with the ID
B
Billy12d ago
yeah, but kk works, for the same id... To me, that's saying the Include isn't applying in the linq Select
A
Angius12d 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
B
Billy12d 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
A
Angius12d ago
Perhaps your ToDto() method doesn't map that property
B
Billy12d 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?
A
Angius12d ago
What you have in the .Select() will look up those properties, though
B
Billy12d 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
A
Angius12d ago
What's your ToDto?
B
Billy12d ago
GitHub
MedTrackDash/MedTrackDash/Services/PatientDatabaseService.cs at zzz...
Contribute to billymaat/MedTrackDash development by creating an account on GitHub.
B
Billy12d ago
A
Angius12d 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
B
Billy12d 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.
B
Billy12d ago
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...
B
Billy11d 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 t 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
Want results from more Discord servers?
Add your server
More Posts
✅ Should I create single or multiple background servicesI need to fetch the same data in background service. I need to fetch it and store to cache every 5 mHelp With hints on how can i make multipull accounts using like arrays or something similar to arrayi just want an easy way this is my code GIve me hitns onlySystem.InvalidOperationException: Unable to resolve service for type 'MovieApi_backend.database.Contwhen i connect my application to database postgresql and when i hit the post req getting error candata update error in c# sqlI want to change the password of the user in the database. I get the password changed warning on theASP Core 8 - Custom 404 HandlingI have a few scenarios where I need to show a different kind of view and a bit of different logic deAdd suffix to version without it showing up as a prereleaseI'm writing a lib that wraps a bunch of external GRPC APIs and exposes them via the same interface. Help refactor serviceHi! I have data of many `Station` models. Their information updates every ~60 minutes. I need to sto✅ looking for help/contributorsDotmenu is a straightforward console menu library designed to streamline user interactions in consolHow to find relative file path for file inside of project?Dealing with a C# console application, I have a folder inside the project named Files and inside thaPassing parameters to overloaded constructorHow would I pass the texture2D variable to be handled in the second constructor. Obviously return doI am trying to store data in database using EF my code run fine but its not storing dataif (response.IsSuccessStatusCode) { var json = await resUsing ref for large data types.Hi, just looking for a little clarification on the *ref* keyword when passing data into a function. crud operations with blazor serverI have a backend that I built and now I want to use it with api calls. I created same model with bacminor glitch, player twitchingso i have a glitch where the player starts "twitching" when you walk beside a block or just on a blo✅ BackgroundWorker getting progressI have a winform applications and with this I have put a ProgressBar on it. I was manually changing My tech stack, what to focus now?- .NET Core - Entity Framework Core (Code First) - MVC - Web API - Identity and JWT - Mediator & CQRBuild Issue due to $(Platform) valueWe are facing build issue when we try to build .net project in custom project configuration, like DePFX certificate for signing the application packageI am trying to use a certificate from azure key vault, but it says that it is invalid or another reaInheritance best practice: test Type of subclass or use enum?See attched UML. Just wondering what the best practice is? Is it better to test the Type of BaseEffeC# - Oracle DataBase Converting the Date (Oracle) to a c# DateTime PropertyHow can I replace TO_TIMESTAMP in my select query with the format my c# code uses? I have a class wi