Converting SQL code with Row_Number() into LINQ C# code

Question

Basically I am listing all servers named 'ServerName' in DESC created order. If for instance I have 50 of them, I need to obtain a list of the 40 serverIds so that they can be deleted; that way I only keep the latest 10 records (Servers) created. Here is the simple SQL code:

  Delete ContosoServers Where serverId In
  (
    Select
    serverId
    From
    (
      Select 
        serverId
        ,row_number() Over(Order By created desc) as recordNumber
      From 
        UDS.ContosoServers
      Where 
        name = 'ServerName'
    ) ServerRecords
    Where recordNumber > 10
  )

I think I would need to create a List of some anonymous type (serverId, recordNumber). Once I obtain that I could just loop through the list from 11 to 50 and delete all servers records, keep 1 to 10 which are the latest.

I came up with this solution but I think is way too much code. In SQL is very simple but in LINQ it looks like it requires more work. I am just trying to avoid all these loops, here it is:

    private static void DeleteOldRecords(string serverName)
    {
        const int numberOfRecordsToKeep = 10;

        using (var context = new MyContext())
        {
            var servers = context.ContosoServers
                                .Where(n => n.name == serverName)
                                .OrderByDescending(o => o.created)
                                .Select(s => new { s.serverId }).ToList();

            //skip the first 10 rows and delete the rest 11,20...
            int i = 0;
            foreach (var s in servers)
            {
                if (i > numberOfRecordsToKeep - 1)
                {
                    //delete row
                    var entityToDelete = context.ContosoServers.Find(s.serverId);
                    if (context.Entry(entityToDelete).State == EntityState.Detached)
                    {
                        context.ContosoServers.Attach(entityToDelete);
                    }
                    context.ContosoServers.Remove(entityToDelete);
                }
                i++;
            }
            context.SaveChanges();
        }

Any ideas of how to improve this? this does not seem "elegant" :-)

Thank you

This may help - from the related list: http://stackoverflow.com/a/1183599/745969 — Tim, Jan 08 '14 at 06:06

score 1 · Accepted Answer · answered Jan 08 '14 at 06:42

1

Assuming that you are using LINQ to SQ, try the code below

DataClasses1DataContext context = new DataClasses1DataContext();

        var d = (from s in context.ContosoServers
                 orderby s.created descending 
                 select s).Take(10);

        context.samples.DeleteAllOnSubmit(d);
        context.SubmitChanges();

answered Jan 08 '14 at 06:42

CSharped

1,247
4
20
49

that would actually delete the 10 latest rows which are the ones I want to keep. – Max Jan 08 '14 at 15:48
`orderby s.created` (not descending) and `Skip(10)`. – Gert Arnold Jan 08 '14 at 21:46
I think order by DESC and skip(10) would be the answer, don't you think? I want to keep the 10 latest rows and delete everything else – Max Jan 09 '14 at 16:59

Converting SQL code with Row_Number() into LINQ C# code

1 Answers1