LINQ to SQL: Data from two unrelated tables, sorting on date

Question

I have two tables tblActionLog and tblLoginLog.

This is tblActionLog:

tblActionLog
------------
ID (int)
Happened (DateTime)
...etc.

This is tblLoginLog:

tblLoginLog
-----------
ID (int)
LoginDate (DateTime)
...etc.

Now I want to have a GridView with information from both of these tables interleaved in eachother so that their separate date informations are sorted, e.g.:

LOG OF STUFF
-------------------------------
DATE        |  WHAT     
2009-09-09  | Anderson logged in.
2009-09-08  | Smith made an action.
2009-09-08  | Smith logged in.
2009-09-06  | Anna made an action.
2009-09-04  | Smith made an action.

I hope this makes it clear enough. I want to get all of this information in a single LINQ query (not necessarily one SQL query). Any help appreciated.

Answer 1

You probably want to use Select() and Union() to collate the results.

var query = context.Logins.Where( l => l.UserID == userID )
                          .Select( l => new { Date = l.Date, What = "logged in" } )
                  .Union( context.Actions.Where( a => a.UserID == userID )
                                          .Select( a => new { Date = a.Date, What = a.Action  } ))
                  .OrderBy( r => r.Date );

Note: you may need to materialize each subquery (use ToList() or AsEnumerable()), but I think LINQ2SQL should be able to construct the UNION directly. Also, this is completely untested - typed straight into the answer editor and I didn't check the signature of the Union method so you may need to adjust.

Answer 2

Here is another way of doing it....

(from a in dataContext.tblActionLog
 select new
 {
    Date = a.Date,
    What = a.WhatHappened
 }).Union
      (from l in dataContext.tblLoginLog
       select new 
       {
           Date = l.Date,
           What = l.WhatHappened
       }).OrderByDescending(c => c.Date);

Of course, you would substitute the actual name of your DataContext object, and the actual name of the column to show what happened(since that wasn't listed in your table design).