Windows Mobile 7 - C# Converting SQL to Linq, deleting all but top 10 in table

Question

I'm trying to create a query in c# for Windows Mobile 7 using linq that will delete all but the top 10 highscores in a table. The table, Scores, is simple, and contains highScore_ID (int), highScore (int), and playerName (string).

SQL:

DELETE FROM Scores
WHERE highscore_ID NOT IN (SELECT TOP 10 highScore FROM HighScore)

LINQ:

from c in context.Scores where !((from o in context.Scores select   
o.highScore).Take(10)).Contains(c.highscore_ID) select c;

I seem to be getting errors with this linq query, any suggestions would be very much appreciated.

Also, what is the table called? You've called it both `Scores` and `Highscores`? Or is that two separate tables? — Mark Byers, Apr 29 '12 at 21:08
Thanks Mark, The table is called Scores. I was going to try using ExecuteQuery, however, this app is for Windows Mobile 7 and I'm not sure it is supported.. either that or I'm doing it wrong. — Euthyphro, Apr 29 '12 at 21:21

Mark Byers · Accepted Answer · 2012-05-06T16:24:56.697

3

Well, for a start your SQL is wrong. It should be this:

DELETE FROM HighScore
WHERE highscore_ID NOT IN (
    SELECT TOP 10 highScore_ID
    FROM HighScore
    ORDER BY score DESC
)

You can do it in LINQ by first selecting the appropriate rows then calling DeleteObject for each object:

var query = context.Scores.OrderByDescending(x => x.HighScore).Skip(10);
foreach (var score in query) {
    context.Scores.DeleteObject(score);
}
context.SaveChanges();

edited May 06 '12 at 16:24

answered Apr 29 '12 at 21:04

Mark Byers

811,555
193
1,581
1,452

1

While not everything is best converted to LINQ, in this case he's on Windows Phone, so he's got no choice in the matter. He *must* use LINQ. – ctacke May 06 '12 at 14:07

score 1 · Answer 2 · edited May 23 '17 at 12:11

If you work with EF normally it doesn't supports bulk delete, but if you see Alex James answer in similar question, you can do:

var query = Scores.OrderBy(x=>x.HighScore).Skip(10);
query.Delete();

As you can see in his answer you should write delete extension method yourself, but he provided all other methods (like factory methods, GetQueryInfo,...). Only part is Delete method which can be written like Update method, In this part you should write your own GetDeleteCommand which should return string like update one with a little variation:

       command.CommandText = string.Format(
       "Delete From {0} WHERE {1} IN ({2})",
       info.TableName,
       Context.Keys[0],
       info.RestrictingSQL
       );

See here for extension methods containing delete,...

Also as Mark said you can do all of this with simple stored procedure :)

P.S: read parts 1,2,3,4 of Alex's article.

Windows Mobile 7 - C# Converting SQL to Linq, deleting all but top 10 in table

2 Answers2