Dynamic cast in 'where' clause with Entity Framework Core 2.1

Question

Using EF Core 2.1.2. For the simplest possible example, let's say I have a class called Foo:

public class Foo
{
    public int Id { get; set; }

    public string Name { get; set; }

    [ForeignKey("EntityId")]
    public virtual ICollection<FooPropertyValue> PropertyValues { get; set; }
}

a class called FooPropertyValue:

public class FooPropertyValue
{
    public int PropertyId { get; set; }

    [ForeignKey("PropertyId")]
    public virtual FooProperty Property { get; set; }

    public int EntityId { get; set; }

    public string Value { get; set; }
}

and a class called FooProperty:

public class FooProperty
{
    public int Id { get; set; }

    public string Name { get; set; }

    public string CSharpType { get; set; } // System.Date, System.String, System.Int32 etc.
}

So it's a simple EAV model.

Now is it possible to utilize that CSharpType property when querying this model?

Let's consider this query as an example:

// Let's assume:
//   the 'table' variable is IQueryable<Foo>
//   the 'propertyId' is passed as a parameter
//   the 'value' is passed as a parameter, and is of type System.String
//   the 'CSharpType' for this property is 'System.DateTime'
//   the requested property value exists, i.e. 'FirstOrDefault' will not return null
var result = table.Where(x => x.PropertyValues.FirstOrDefault(p => p.PropertyId == propertyId)).Value == value)

This query will go though, but it will return something only if the stored value is identical to the passed value and as a plus, it will be slow.

Is it possible to convert the value to DateTime before passing it to the query method, build the Where expression dynamically to treat the Value property of FooPropertyValue as DateTime (although I don't know how to do this step, I think Reflection will not allow to call Expression.Equal where the left-side Expression.Property has string type and right-side Expression.Constant has DateTime type), pass that built expression to ´Where´ and expect the call to go through?

I ask because, when you store dynamic values in a nvarchar column in SQL Server, it's possible (when the type is known at runtime) to use CAST(Value as {VALUETYPE}) in ´WHERE´ clause and ~~make the query somewhat faster~~ (but also more usable when dealing with dates, for example). Is this possible using EF Core?

EDIT: Actually the query can't be any faster when using CAST, but still the question remains, as it'd be a pain in the ass to query some data types such as decimal, DateTime etc. using string values.

Are you sure that `WHERE CAST(Value ...` makes the query faster? That contradicts all [sargability](https://stackoverflow.com/q/799584/861716) rules. — Gert Arnold, Sep 09 '18 at 18:51
@GertArnold Just tried two queries: `select * from Table where CAST(NvarcharColumn as bigint) between 1101001260011 and 1101001260013` and `select * from Table where NvarcharColumn between '1101001260011' and '1101001260013'` and they both seemed to perform the same, actually. So no, you made a good point, thanks. But this is actually cool as far as dates are concerned, right? Because it means I can store dates in a sortable format, and on the application side also make sure the passed format is sortable format... and comparing those as strings would work. But what about things as `decimal`s? — Dejan Janjušević, Sep 09 '18 at 19:08
Sooner or later you'll conclude that the value table should store values in there original types. Which makes EAV an even greater pain in the neck than it already is. — Gert Arnold, Sep 09 '18 at 19:12
@GertArnold some things just can't be solved without using some form of EAV model. But your comment about storing values in their original types might actually make another good point, while still using EAV approach :) — Dejan Janjušević, Sep 09 '18 at 19:21

Dynamic cast in 'where' clause with Entity Framework Core 2.1

0 Answers0