Compare DateTime with milliseconds in LINQ

Admin 1
Viewed 138

I am building a windows service that fetches data from an SQL Database and stores them in a .txt file every 5 minutes. What I am trying to do is every 5 minutes when my service checks the database, it should check the last datetime record on the txt file and find it in the database. If it finds the same datetime, it should fetch all the newer records after that datetime. I am trying to be as precise as possible and not miss a records, so I thought it would be a good idea to compare the milliseconds of each date. This is what I have so far, but I know that x.Datetime, which is of type DateTime, does not fetch the milliseconds along with the date. "lastDate" is the date I fetch from the txt file with milliseconds added to it.

dbData = db.Table.Where(x => x.Datetime < lastDate).ToList();

Also, the column on the database has milliseconds stored with the datetime.

linq c#
1 Answers

So the rows in your table have a property Datetime. You only want to fetch rows that you haven't fetched before and you don't want to miss any rows.

The problem is, that as soon as your query returns, new rows might already be added. So before you process the fetched data at millisecond X, new data might already be added

It would be better to remember the DateTime of the last added row:

private DateTime lastFetchedDate = ...

Call the following procedure every few minutes:

private void FetchAndProcessNewData()
{
     var dbData = db.Table.Where(row => row.Datetime > lastFetchedDate)
                .OrderByDescenting(row => row.DateTime)
                .ToList();
    if (dbData.Any())
    {
        ProcessFetchedData(dbData);
        lastFetchedDate = dbData[0].DateTime;
    }
    // else: no new rows; nothing to process, no new lastFetchedDate needed
}

It is a bit of a waste to order all rows. A small optimization would be to not order the list, but calculate the max DateTime. This way you do'll only have to enumerate the fetched data once

lastFetchedDate = dbData.Select(row => row.DateTime).Max();

There is no Max for DateTimes, but the extension method is fairly easy to create. If you are not familiar with Extension methods, see extension methods demystified

public static DateTime Max(this IEnumerable<DateTime> source)
{
    // TODO: exception if source == null
    var enumerator = source.GetEnumerator();

    // initialize the first DateTime as the largest:
    if (enumerator.MoveNext()
    {
        var max = enumerator.Current;

        // enumerate the rest:
        while (enumerator.MoveNext())
        {
            if (enumerator.Current > max)
                max = enumerator.Current;
        }
        return max;
     }
     // else: empty source. Decide what to do: exception? return DateTime.MinValue;
}

Usage:

var dbData = db.Table.Where(row => row.Datetime > lastFetchedDate).ToList();
if (dbData.Any())
{
    ProcessDbData(dbData);
    this.lastFetchedData = dbData.Select(row => row.DateTime).Max();
}
user_22817900
Related