How to transform sql query to linq query

Question

delete  from detail where autoid not in (select min(autoid) from detail
where CATEGORY<>'Accepted'
group by ATM_Id, Date, Card ,Transit ,Ck)
and CATEGORY<>'Accepted'

what I was tried

     var Query2 = from line in
     (from line in source let fields = line.Split(',')
     select new {  autoid = fields[0],
          ATMID = fields[4],
          DATE = fields[2],
          CARDNo = fields[5],
          TRANSIT = fields[8],
          CheckNo = fields[9],
          CATEGORY = fields[10],
      })
      orderby line.ATMID, 
              line.DATE, 
              line.CARDNo, 
              line.TRANSIT, 
              line.CheckNo ascending
      where   line.CATEGORY != "Accepted"
      group line by new {   line.ATMID,
                            line.DATE, 
                            line.CARDNo, 
                            line.TRANSIT, 
                            line.CheckNo } 
      into gruoped
      where gruoped.Count() > 1

                 //select autoid = gruoped.Key;
      select new
      {
                     //ATMID = gruoped.Key,
                     //DATE = gruoped.Key,
                     //CARDNo = gruoped.Key,
                     //TRANSIT = gruoped.Key,
                     //CheckNo = gruoped.Key,
                     autoid = gruoped.Key,
                     //CATEGORY = gruoped.Key
      };

Answer 1

Here is a sample how it should work check this out

var remove = (from aremove in db.logins 
             where aremove.username == userNameString 
                && aremove.Password == pwdString 
             select aremove).FirstOrDefault(); 

if(remove != null)
{
    db.logins.DeleteOnSubmit(remove);
}

If you have any question fell free to ask !!

Answer 2

You can try this.

var ids = (from d in detail
where d.Category != "Accepted"
group d by new {d.ATM_Id, d.Date, d.Card ,d.Transit ,d.Ck} into grp
select  grp.Min(x => x.autoId));


var toDelete = (from d in detail
            where !ids.Contains(d.AutoId) && d.Category != "Accepted"
            select d);

context.DeleteOnSubmit(toDelete);   

You can just search for the ones you want and then recreate your new list of comma separated strings to store back to a file.

EG:

void Main()
{

    var fields = new List<Field>{
        new Field{Id = 1, Name = "A"},
        new Field{Id = 1, Name = "A"},
        new Field{Id = 2, Name = "B"},
        new Field{Id = 1, Name = "A"},
        new Field{Id = 3, Name = "C"},
        new Field{Id = 2, Name = "B"},
    };

    // Grouped 
    var grouped = (from f in fields
                group f by new {f.Id, f.Name} into grp
                select new Field
                {
                        Id = grp.Select(x => x.Id).FirstOrDefault(),
                        Name = grp.Select(x => x.Name).FirstOrDefault()
                }
                );

    // Makes a list of the distinct Fields             
    var list_Of_CSV_Items = grouped.Select(x => string.Join(",", x.Id,x.Name));

    System.IO.File.WriteAllLines(@"C:\Where_your_folder_is.txt", list_Of_CSV_Items);
}

public class Field
{
    public int Id {get;set;}
    public string Name {get; set;}
}