Merge the contents 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 available documents and populates the access time from the accesslogs table? I changed the Documents class to C # to have an AccessTime (DateTime) property. LINQ query results must be of type List.

(My solution runs 10 queries, there should 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();

Change . 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

, , DBML, , autogenerated .Document.

: , Document, , .

Answer 2

, DOcuments AccessLogs, Linq2SQL Document 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: ( , linq-to-Entities Linq-to-Sql.) ( List<Document>) .
, , 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);