0

I have a query which deletes duplicate records from my database table.

Can anyone please assist in converting it to Linq:

DELETE FROM SSRecs
WHERE (SSRecId NOT IN
          (SELECT MAX(SSRecId) AS Expr1
           FROM  SSRecs AS SSRecs_1
           GROUP BY ref, DateTime))

I think I can get the same list of duplicates from this query:

var duplicates = (from check in db.SSRecs 
                  group check by new { check.ref, check.DateTime } into g
                  where g.Count() > 1
                  select g.Key).ToList();

This is where I'm a little stuck - I've got the list of duplicate Keys (SSRecId) in the "duplicates" list - but how do I bulk remove them from the database - this is my pseudo code:

db.SSRecs.Where(x => x.SSRecId ** is in duplicates list **)
         .ToList()
         .ForEach(db.SSRecs.DeleteObject);

db.SaveChanges();

Thanks for any guidance, Mark

Christos
  • 53,228
  • 8
  • 76
  • 108
Mark
  • 7,778
  • 24
  • 89
  • 147
  • 2
    Any reason why you don't keep the original T-SQL statement? LINQ isn't a replacement for SQL, it's a query *only* language. Using an ORM for batch operations is problematic even with advanced ORMs like NHibernate and Entity Framework. L2S has no support for batch operations. In your case, you'll end up loading *all* matching objects to the client before deleting them one by one. – Panagiotis Kanavos Jul 08 '14 at 09:22
  • Hi - only reason is I thought with C#/MVC - it would be clearer to have all queries etc in one format. Thanks, Mark – Mark Jul 08 '14 at 09:49
  • http://stackoverflow.com/questions/11592176/bulk-delete-in-entity-framework – Murdock Jul 08 '14 at 13:23
  • and more recently http://stackoverflow.com/questions/12751258/batch-update-delete-ef5 – Murdock Jul 08 '14 at 13:25

0 Answers0