My context model has a couple of related tables:
CREATE TABLE "CarSystem"."Reads" (
"ReadId" UUID PRIMARY KEY,
. . .
);
CREATE TABLE "CarSystem"."Alarms" (
"AlarmId" UUID PRIMARY KEY DEFAULT UUID_GENERATE_V4(),
"ReadId" UUID NOT NULL REFERENCES "CarSystem"."Reads" ( "ReadId" ),
. . .
);
There are other columns, but they're not important. There is a zero to many relationship between the Reads table and the Alarms table. There can be any number of rows in the Alarms table for each row in the Reads table, from zero on up.
I've got an Entity Framework model for this database structure. I've also got a ViewModel object for the rows in the Reads table. I want the ViewModel object to have a boolean property called HasAlarms. This property is to be set to true if there is at least one row in the Alarms table for the row in the Reads table.
I've got a function in my data access layer which is supposed to return an array of Read ViewModel objects that all match a set of criteria. I'm not sure how to construct the query to set the HasAlarms property. I only want to go to the database once and I want one entry in the array for each row in the Reads table.
Right now I'm making two database queries, one to retrieve all of the Reads and another to retrieve all of the Alarms:
IQueryable<Read> query = from read in context.Reads
where SomeCondition
select read;
Alarm[] alarms = ( from read in query
join alarm in context.Alarms on read.Readid equals alarm.ReadId
select alarm ).ToArray();
ReadViewModel[] result = ( from read in query
select new ReadViewModel {
ReadId = read.ReadId,
. . .
HasAlarms = alarms.Where( a => a.ReadId == read.ReadId ).Any(),
. . .
} ).ToArray();
This works, but it's inefficient because I'm hitting the database twice, once to retrieve the rows in the Reads table and once to get the Alarms.
Is there a way to build this query so it only hits the database once?
Tony
@Craig Stuntz:
What I'm trying to do is to speed up the rate at which data is loaded in my application in response to a report request. This is a WPF application and I need the data to load as quickly as possible to improve the user experience. It's important to make as few trips to the database as possible or the application seems to crawl.
The ReadViewModel object consists of properties that map to columns in the table, plus a couple of nested objects that are either stored as columns in the same table or as rows in a couple of related tables. It is a complicated structure and Entity Framework complains if I try to create the View Model objets with one IQueryable. That is, something like:
ReadViewModel[] reads = ( from read in context.Reads
join alarm in context.Alarms on read.ReadId equals alarm.ReadId
where SomeCondition
select new ReadViewModel { ... } ).ToArray();
throws an exception that says Entity Framework can't create a constant of one of the nested types.
If I retrieve the data into arrays of Entity Objects and then create the View Model objects using Linq, everything works. But then I have to make more than one trip to the database to get everything. If I retrieve all of the data with those queries, though, it's a lot faster than letting Entity Framework go back and query for the extra data for each row, which is what happens when I do it the way you recommend. There's also the complication of having to do a left outer join with the Alarms table to mess things up.
I've found that if I retrieve the arrays of Entity Objects first, instead of making one trip for each row in addition to the initial trip, I can make 2 or 3 trips total. It's much faster than making n + 1 or n + 2 trips. I was just trying to see if there was a way I could get Entity Framework to do it all in one database call, which I could easily do if I were writing the SQL myself. I'd just have to add a column that returns 0 or 1 for HasAlarms if there was at least one row in the Alarms table for the Read.
