Entity Framework unable to general SQL for interfaced entities / generics

Question

When using Entity Framework, it seems that it is not properly generating SQL if I use interfaces (or generics) in lambda functions.

Interface:

interface ILogEntry
{
   DateTime StartTime { get; set; }
}

Entity that implements this interface:

class ReceiverLimitCache : ILogEntry
{
  pbulic DateTime StartTime { get; set; }
  ...
}

Utility class for handling these logs:

public class DBLog<T> : ILog<T>
    where T : ILogEntry
{
    private IEnumerable<T> _source;

    public DBLog(IEnumerable<T> source)
    {
        _source = source;
    }

    public virtual T GetAtTime(DateTime time)
    {
        return _source
            .Where(t => t.StartTime <= time)
            .OrderByDescending(t => t.StartTime)
            .FirstOrDefault();
    }
}

The log is instantiated with the relevant collection from the DB Context.

However, when I call this function GetAtTime(...), EF is executing the following SQL:

SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[StartTime] AS [StartTime], 
    [Extent1].[ReceiverID] AS [ReceiverID], 
    [Extent1].[Limit] AS [Limit]
    FROM [Results].[ReceiverLimitCache] AS [Extent1]

Which does not seem to include the time condition, ignores the index on the table and slows my program down to a crawl. (I can only assume EF is doing the sorting and selecting and everything else locally).

So, I suspect that the function is calling ((ILogEntry)t).StartTime, rather than straight t.StartTime, which EF6 doesn't recognise as a property it can pass to the DB engine. — Barry Smith, Jul 23 '15 at 08:43
Actually it turns out the problem still exists even without the generic class manipulating it... (i.e. the same query against ReceiverLimitCache instead of ILogEntry) — Barry Smith, Jul 23 '15 at 09:02
Yes, I should, and I am... another mistake transcribing it. Sorry about that. But I think I may have found the problem: I am casting it to IEnumerable, then when I call the .Where function, I am calling IEnumerable.Where() rather than IQueryable.Where(). So now I need to figure out how to cast it back to IQueryable. — Barry Smith, Jul 23 '15 at 09:22

score 0 · Answer 1 · answered Jul 23 '15 at 08:55

0

Did you try to set your StartTime as public ? Something like this :

class ReceiverLimitCache : ILogEntry
{
    public Nullable<System.DateTime> StartTime { get; set; }
}

answered Jul 23 '15 at 08:55

yannick

121
5

Ah yes, sorry, that was just a mistake transcribing my code. I will update the question to reflect that. Also, it's not a nullable property – Barry Smith Jul 23 '15 at 08:58

score 0 · Answer 2 · edited May 23 '17 at 11:44

Ok, having investigated further I found the real cause here was that I was using IEnumerable.Where rather than IQueryable.Where.

Changing the function to the following:

    public virtual T GetAtTime(DateTime time)
    {
        if (_source is IQueryable<T>)
            return ((IQueryable<T>)_source)
                .Where(t => t.StartTime <= time)
                .OrderByDescending(t => t.StartTime)
                .FirstOrDefault();
        else
            return _source
                .Where(t => t.StartTime <= time)
                .OrderByDescending(t => t.StartTime)
                .FirstOrDefault();
    }

Produced an error message complaining "... LINQ to Entities only supports casting EDM primitive.."

I found this question after googling the error: LINQ to Entities only supports casting EDM primitive or enumeration types with IEntity interface

So I then changed the class definition to:

public class DBLog<T> : ILog<T>
    where T : class, ILogEntity

And everything is peachy!

I'm not sure on the etiquette here, someone else may stumble upon my question and it may be useful. Should I simply delete it, or leave this answer here?

Entity Framework unable to general SQL for interfaced entities / generics

2 Answers2