Merge content of two tables with LINQ-to-SQL

Question

I have two tables in my database

Table:Documents
Id (int), DocName (nvarchar)
----------------------------

Table:AccessLogs
Id (int), DocId (int), AccessTime (DateTime)
----------------------------

How can I write a LINQ query that returns the last 10 accessed documents and fills in the access time from the accesslogs table? I have modified the Documents class in C# to have an AccessTime (DateTime) property. The results from the LINQ query should be of type List.

(My solution runs 10 queries, there must be a more efficient method)

Answer 1

This should work:

var lastTen = (
  from doc in Documents
  join log in AccessLogs on doc.Id equals log.DocId
  order by log.AccessTime desc
  select doc
).Take(10).Reverse().ToList();

Edit - Then use the following:

var lastTen = (
  from doc in Documents
  join log in AccessLogs on doc.Id equals log.DocId
  order by log.AccessTime desc
  select new { 
      DocId = doc.Id, 
      DocName = doc.DocName, 
      LogId = Log.Id, 
      AccessTime = log.AccessTime 
  }
).Take(10).Reverse().ToList();

Edit 2

Assuming that there is a relationship modeled in the DBML then you can also only select the last ten log entries and then access the related document via the autogenerated .Document property.

As for your comment: The statements above cannot return this error as no new Document is constructed, instead an anonymous type is constructed which holds the necessary values.

Answer 2

Assuming that you have a Foreign key relationship set up between DOcuments & AccessLogs, then Linq2SQL has already created the Document property in AccessLog.

var lastTen = 
    (from al in db.AccessLogs
    orderby al.AccessTime desc
    select new Document
    {
      ID = al.DocId,
      DocName = al.Document.DocName,
      AccessTime = al.AccessTime
    }).Take(10);

UPDATE: (I'm going to guess that you are using linq-to-Entities instead of Linq-to-Sql.) Then your basic premise (creating a List<Document>) is impossible.
The best you could do is a List<SomethingLikeADocument>.

class SomethingLikeADocument
{
     public int ID {get; set;}
     public string DocName {get; set;}
     public DateTime AccessTime {get; set;}
}


var lastTen = 
    (from al in db.AccessLogs
    orderby al.AccessTime desc
    select new SomethingLikeADocument
    {
      ID = al.DocId,
      DocName = al.Document.DocName,
      AccessTime = al.AccessTime
    }).Take(10);