CODEWITHSUNDEEP
c#linqlinq-to-sqlentity-framework-coreef-core-3.1
Torui
Torui

Reputation: 23

Calculated readonly Member as Calculated Column

I Have the following class and want to be able to query it using such a Linq Query dbContext.Example.Where(e=> e.IsBetween);

public class Example {
  public DateTime Start {get; set;}
  public DateTime End {get; set;}
  public bool IsBetween => DateTime.Now >= Start && DateTime.Now <= End ;
}

However this results in an "The LINQ expression [...] could not be translated. Either rewrite [...] or switch to client evaluation" error at runtime.

Is there any way for me to link the readonly Property IsBetween to a CalculatedColumn in the db?

Upvotes: 2

Views: 361

Answers (1)

Guru Stron
Guru Stron

Reputation: 143473

The error says that EF can't transform you custom method (property getter) into SQL. If you have/want to map IsBetween to computed column in db you should do it accordingly. Something like this(have not checked validity of SQL):

public class Example {
  public DateTime Start {get; set;}
  public DateTime End {get; set;}
  public bool IsBetween {get; set;} ;
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // your setup ...
    modelBuilder.Entity<Example>()
        .Property(p => p.IsBetween)
        .HasComputedColumnSql("getdate() >= Start AND getdate() <= END");
}

If you want IsBetween to exist just in code you will need to use expression trees, or example:

public class Example {
  public DateTime Start {get; set;}
  public DateTime End {get; set;}
  public bool IsBetween => _func(this) ; 
  public static readonly Expression<Func<Example, bool>> IsBetweenExpr = e => 
      DateTime.Now >= e.Start && DateTime.Now <= e.End;
  private static readonly Func<Example, bool> _func = _expr.Compile();
}

dbContext.Example.Where(Example.IsBetweenExpr) ....;

Upvotes: 1

Related Questions