Selecting max Date from multiple tables

Question

Is it possible to turn this into an entity framework query that will only query the database once?

  select max(TableADate) latestTableADate, max(TableBDate) latestTableBDate
  from
  (
      select max(a.ModifiedDate) TableADate, null TableBDate
      from TableA a

      union all

      select null, max(b.ModifiedDate)
      from TableB b
  ) data

The intent is to get the latest ModifiedDate from multiple tables with one sql statement so Entity Framework is not doing multiple trips to the database to get the data.

I need to know when the last time that one of the tables was updated.

Update:

I ended using DbContext.Database.SqlQuery doing the following:

  var output = db.Database.SqlQuery<DateTime>
                  ("select data from (" +
                    "select 1 orderBy, max(ModifiedDate) data from TableA" +
                    "union all " +
                    "select 2, max(ModifiedDate) from TableB " +
                  ") temp order by orderby").ToList();


  data.TableADate = output[0];
  data.TableBDate = output[1];

Not sure whether you could do this with EF, but you could definitely do it by creating a `view` in the database — paul, May 30 '13 at 14:08
Yea, I was thinking that, but I wanted to see what my options are first so I can take a look at some of the pros and cons of the different approaches. — SBurris, May 30 '13 at 14:13

score 1 · Accepted Answer · edited May 23 '17 at 12:27

1

You can execute a query similar to the one you've listed using the DbContext.Database.SqlQuery method. You'd need to change it to return the dates in some sort of order or use out parameters.

edited May 23 '17 at 12:27

Community

1
1

answered May 30 '13 at 14:15

qujck

14,388
4
45
74

Selecting max Date from multiple tables

1 Answers1