Getting all dates between two dates using datepickers and Entity Framework 6

Question

I have two datetime pickers on my form. I want a function that will return all datetimes from a specific table (which are values of a specific column) between those two dates.

My method looks like this:

public DateTime[] GetAllArchiveDates(string username = null)
{
    var result = new DateTime[0];

    if (username != null)
    {
        result = this._context.archive.OrderBy(s => s.IssuingDate).Where(s => s.insertedBy == username).Select(s => s.issuing_date).Distinct().ToArray();
    }
    else
    {
        result = this._context.archive.OrderBy(s => s.IssuingDate).Select(s => s.issuing_date).Distinct().ToArray();
    }

    return result;
}

But I am getting this error:

System.NotSupportedException: 'The specified type member 'IssuingDate' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.'

How to do this?

Answer 1

The cause of your error message

You should be aware about the differences between IEnumerable and IQueryable.

An object of a class that implements IEnumerable holds everything to enumerate over the sequence of items it represents. You can ask for the first item of the sequence, and once you've got one, you can ask for the next item, until there are no more items.

On the other hand, an object of a class that implements IQueryable holds everything to ask another process to provide data to create an IEnumerable sequence. To do this, it holds an Expression and a Provider.

The Expression is a generic representation of what kind of IEnumerable must be created once you start enumerating the IQueryable.

The Provider knows who must execute the query, and it knows how to translate the Expression into a format that the executor understands, for instance SQL.

There are two kinds of LINQ statements. Those that use deferred execution, and those that don't. The deferred functions can be recognized, because they return IQueryable<TResult> (or IEnumerable). Examples are Where, Select, GroupBy, etc.

The non-deferred functions return a TResult: ToList, ToDictionary, FirstOrDefault, Max.

As long as you concatenate deferred LINQ functions, the query is not executed, only the Expression is changed. Once you start enumerating, either explicitly using GetEnumerator and MoveNext, or implicitly using foreach, ToList, Max, etc, the Expression is sent to the Provider who will translate it to SQL and execute the query. The result is represented as an IEnumerable, on which the GetEnumerator is performed.

What has this to do with my question?

Because the Expression must be translated into SQL, it can't hold anything that you invented. After all, SQL doesn't know your functions. In fact, there are a lot of standard functions that can't be used in an IQueryable. See Supported and unsupported LINQ functions

Alas you forgot to give us the archive class definition, but I think that it is not a POCO: It contains functions and properties that do more than just get / set. I think that IssuingDate is not just get / set.

For IQueryables you should keep your classes simple: use only {get; set;} during your query, nothing more. Other functions can be called after you've materialized your IQueryable into something IEnumerable which is to be executed within your local process

Back to your question

So you have a database with a table Archive with at least columns IssuingDate and InsertedBy. It seems that InsertedBy is just a string. It could be a foreign key to a table with users. This won't influence the answer very much.

Following the entity framework code first conventions this leads to the following classes

class Archive
{
    public int Id {get; set;}
    public DateTime IssuingDate {get; set;}
    public string InsertedBy {get; set;}
    ...
}

public class MyDbContext : DbContext
{
     public DbSet<Archive> Archives {get; set;}
}

By the way, is there a proper reason you deviate so often from Microsoft standards about naming identifiers, especially pluralization and camel casing?

Anyway, your requirement

I have two datetime pickers on my form. I want a function that will return all datetimes from a specific table (which are values of a specific column) between those two dates.

Your code seems to do a lot more, but let's first write an extension function that meets your requirement. I'll write it as an extension method of your archive class. This will keep your archive class simple (only {get; set;}), yet it adds functionality to the class. Writing it as an extension function also enables you to use these functions as if they were any other LINQ function. See Extension methods demystified

public static IQueryable<Archive> BetweenDates(this IQueryable<Archive> archives,
    DateTime startDate,
    DateTime endDate)
{
     return archives.Where(archive => startDate <= archive.IssuingDate
                                   && archive.IssuingDate <= endDate);
}

If I look at your code, you don't do anything of selecting archives between dates. You do something with a userName, ordering, select distinct... It is a bit strange that you first Order all your million archives, and then decide to keep only the ten archives that belong to userName, and if you have several same issuing dates you decide to remove the duplicates. Wouldn't it be more efficient to first limit the number of issuing dates before you start ordering them?

public static IQueryable<archive> ToIssuingDatesOfUser(this IQueryable<archive> archives,
    string userName)
{
    // first limit the number of archives, depdning on userName,
    // then select the IssuingDate, remove duplicates, and finally Order
    var archivesOfUser = (userName == null) ? archives :
        archives.Where(archive => archive.InsertedBy == userName);

   return archivesOfUser.Select(archive => archive.IssuingDate)
                        .Distinct()
                        .OrderBy(issuingDate => issuingDate);
}

Note: until now, I only created IQueryables. So only the Expression is changed, which is fairly efficient. The database is not communicated yet.

Example of usage:

Requirement: given a userName, a startDate and an endDate, give me the unique issuingDates of all archives that are issued by this user, in ascending order

public ICollection<string> GetIssuingDatesOfUserBetweenDates(string userName, 
    DateTime startDate,
    DateTime endDate)
{
    using (var dbContext = new MyDbContext(...))
    {
        return dbContext.Archives
               .BetweenDates(startDate, endDate)
               .ToIssuingDatesOfUser(userName)
               .ToList();
    }
}