Most recent records with 2 tables and take / skip
What I want to do is basically what this question suggests: SQL Server - How to display the most recent records based on dates in two tables .: I am using Linq for sql.
I have tables:
- Appointments
- ForumPosts
They are not very similar, but they both have a "LastUpdated" field. I want to get the most recent merged records. However, I also need the take / skip functions for swapping (and no, I don't have SQL 2012).
I don't want to create a new list (with ToList and AddRange) with all my records, so I know the whole set of records and then I order .. This seems extremely inefficient.
My attempt:
Please don't laugh at my inefficient code .. Okay, a little (both because it's inefficient and ... it doesn't do what I want when skip is greater than 0).
public List<TempContentPlaceholder> LatestReplies(int take, int skip)
{
using (GKDBDataContext db = new GKDBDataContext())
{
var forumPosts = db.dbForumPosts.OrderBy(c => c.LastUpdated).Skip(skip).Take(take).ToList();
var assignMents = db.dbUploadedAssignments.OrderBy(c => c.LastUpdated).Skip(skip).Take(take).ToList();
List<TempContentPlaceholder> fps =
forumPosts.Select(
c =>
new TempContentPlaceholder()
{
Id = c.PostId,
LastUpdated = c.LastUpdated,
Type = ContentShowingType.ForumPost
}).ToList();
List<TempContentPlaceholder> asm =
assignMents.Select(
c =>
new TempContentPlaceholder()
{
Id = c.UploadAssignmentId,
LastUpdated = c.LastUpdated,
Type = ContentShowingType.ForumPost
}).ToList();
fps.AddRange(asm);
return fps.OrderBy(c=>c.LastUpdated).ToList();
}
}
Any awesome Linq to SQl people that can drop me a hint? I'm sure someone can join this path!
source to share
First, you must use OrderByDescending
as later dates have higher values than earlier dates in order to get the most recent updates. Second, I think what you are doing will work for the first page, but you only need to take the top valuestake
from the combined list. That is, if you want the last 20 records from both tables to be merged, take the last 20 records from each, concatenate them, and then take the last 20 records from the combined list. The problem arises when you try to paging because you will need to know how many items from each list took to compose the previous pages. I think your best bet is probably to merge them first and then use skip / take. I know you don't want to hear this, but the other solutions are probably more complicated. Alternatively, you can take the top values skip+take
from each table, then merge, skip the values, skip
and apply take
.
using (GKDBDataContext db = new GKDBDataContext())
{
var fps = db.dbForumPosts.Select(c => new TempContentPlaceholder()
{
Id = c.PostId,
LastUpdated = c.LastUpdated,
Type = ContentShowingType.ForumPost
})
.Concat( db.dbUploadedAssignments.Select(c => new TempContentPlaceholder()
{
Id = c.PostId,
LastUpdated = c.LastUpdated,
Type = ContentShowingType.ForumPost
}))
.OrderByDescending( c => c.LastUpdated )
.Skip(skip)
.Take(take)
.ToList();
return fps;
}
source to share