-1

I'm creating a web application using MVC that allows users to edit existing records, and these are stored in the database as a new record that references the very first instance of said record.

There's a column called "OriginalID" so when a video is edited, the edit has a higher ID but can always be linked back using the "OriginalID" column and the below SQL statement will return the latest data for each Video.

I want to achieve something like the below but using LINQ:

SELECT Title, Genre, Price, PegiCert, Release, Desc
FROM   video vid 
WHERE  vid.id = ( SELECT TOP 1 vid2.id 
                  FROM   video vid2
                  WHERE  ISNULL(vid2.originalid, vid2.id) = ISNULL(vid.originalid, vid.id) 
                  ORDER BY id DESC )

I want to return a list of Video but only the latest edits. 

IQueryable<Video> _videos;

_videos = // LINQ statement
return _videos.ToList<Video>();

Is this possible?

user3047489
  • 35
  • 8
  • Can I ask whether you are designing the tables or is this a project that you are just working on. My reason for asking, is I think it is a bit of long term problem having your edits or changes in the same table. Could you not create a history table that looks exactly like the video table or whatever else it may be called? On the history table you just use a different primary key than in the original table, this will allow you to store multiple edits in the history table per video record. – Louis Lewis Nov 09 '14 at 01:04
  • Also if you expect that users of the system would only be updating the price on some regular basis, it may help for you to use a separate table to store prices for video, duplicating all the fields that video has, just because the user changed the price, seems like a heavy price to pay long term, as performance will gradually get worse over time when performing operations and lookups on videos. – Louis Lewis Nov 09 '14 at 01:06
  • Hi, I am designing the tables. It's only a small project and would never have enough data to cause performance issues. – user3047489 Nov 09 '14 at 01:25

1 Answers1

0
var lastEditedVideo = _videos.GroupBy(x => x.originalid ?? x.id)
                             .Select(g => g.OrderByDescending(m => m.id).FirstOrDefault())
                             .ToList();
Raphaël Althaus
  • 59,727
  • 6
  • 96
  • 122