Deleting oldest record by date using LINQ

esherr01 · August 1st, 2011, 01:02 PM

Hi and TIA:

Using LINQ I would like to find the oldest dated record (just one) from a table. I have a date field when the record was inserted, (that also matches a certain customer value) and delete it.

What I have so far is:

var deleteOldest = from myTable in db.myTables where (myTable.Customer_ID == customer && myTable.Date_Played == MIN(Date_Played) select myTable;

foreach(var detail in deleteOldest)
{
db.myTables.DeleteOnSubmit(detail);
db.SubmnitChanges();
GridView1();
}

I am using the MIN substitution just as a "placeholder" as I know this is wrong but I'm trying to show the intent/logic of what I am trying to accomplish.

Kindest Regards,
Cliff

Imar · August 1st, 2011, 02:23 PM

Hi Cliff,

You can do an OrderByDescending and then take the first item. Here's a quick example using LINQ to objects:

Code:

 
using System;
using System.Collections.Generic;
using System.Linq;
namespace ConsoleApplication1
{
  class Program
  {
    static void Main(string[] args)
    {
      List<Person> people = new List<Person>() { new Person() { Id = 1, Name = "Imar" }, new Person() { Id = 2, Name = "Cliff" }, new Person() { Id = 3, Name = "Joe" } };
      Person oldest = people.OrderByDescending(p => p.Id).First();
    }
  }
  public class Person
  {
    public int Id { get; set; }
    public string Name { get; set; }
  }
}

Hope this helps,

Imar

esherr01 · August 1st, 2011, 03:29 PM

I'm so sorry but relating this to a List and class Person just throws me off.

Perhaps I cannot use the var LINQ that is my only knowledge at this point?

I now have two non-working versions.

1)

Code:

var deleteOldest = from myTable in db.myTables where myTable.Customer_ID == customer orderby myTable.Date_Played descending select myTable.First();

2.

Code:

var deleteOldest = from myTable in db.myTables where myTable.Customer_ID == customer orderby myTable.Date_Played descending select myTable.OrderbyDescending.First();

Both give errors.
Sorry, but I am trying to related this to the var and foreach syntax

Regards,
Cliff

Imar · August 1st, 2011, 04:28 PM

Hi Cliff

Quote:

I'm so sorry but relating this to a List and class Person just throws me off.

Sorry, somehow I thought you were using method syntax (e.g. Where(x => ... etc). I created a separate collection and class so I could provide you with a working example, rather than trying to write a query for your collections that I can't test.

Whether the source is a list or not doesn't really matter. What matters is that you're querying a LINQ compatible collection, so the idea is really the same.

Quote:

Sorry, but I am trying to related this to the var and foreach syntax

Since you're querying a single instance you don't really need var (although you could still use it). First returns an actual type; Person in my example, myTable in yours. The var short cut will still work though.
And since First() returns a single instance, you don't need foreach. So, when you get your example to work you can do this:

Code:

db.myTables.DeleteOnSubmit(detail);
db.SubmnitChanges();
GridView1();

without the foreach.

Here's a revised example showing the concept. The below code filters the people based on the CustomerId, sorts them by the PlayDate (no need for the descending as I said earlier) and then queries the first element which is effectively the oldest. You need to wrap the entire query in a pair of parentheses and then call First() on that query to get a single instance of Person.

Code:

using System;
using System.Collections.Generic;
using System.Linq;
namespace ConsoleApplication1
{
  class Program
  {
    static void Main(string[] args)
    {
      List<Person> people = new List<Person>() 
      { 
        new Person() { CustomerId = 1, Name = "Imar", PlayDate = new DateTime(2010, 1, 1) }, 
        new Person() { CustomerId = 1, Name = "Cliff", PlayDate = new DateTime(2011, 1, 1) }, 
        new Person() { CustomerId = 2, Name = "Joe", PlayDate = new DateTime(2010, 1, 1)  },
        new Person() { CustomerId = 2, Name = "Jane", PlayDate = new DateTime(2011, 1, 1) } 
      };
      var oldest = (from person in people
                    where person.CustomerId == 1
                    orderby person.PlayDate 
                    select person).First();
 
      // No need for a foreach. oldest *is* a person
      DateTime test = oldest.PlayDate;
 
      // The query above is functionally equivalent to:
      Person oldest = (from person in people .... rest of the code here
    }
  }
 
  public class Person
  {
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public DateTime PlayDate { get; set; }
  }
}

Hope this helps. If not, please let me know....

Imar

esherr01 · August 1st, 2011, 05:19 PM

...well, no errors! Yea! I still have to add in more records for the testing but, looks great, thanks.

The only thing I added at at the end was:

people.DeleteOnSubmit(oldest);
people.SubmitChanges();

Imar · August 2nd, 2011, 03:44 AM

Yes, correct. oldest now contains a single item so you can directly delete it from the context without looping over it.

Cheers,

Imar