using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
using Microsoft.Data.SqlClient;
public static void Main()
using (var context = new EntityContext())
context.Database.EnsureCreated();
using (var context = new EntityContext())
context.Customers.Add(new Customer() { Name ="Customer_A", Status = CustomerStatus.IsActive, LastLogon = DateTime.Now.AddDays(-10) });
context.Customers.Add(new Customer() { Name ="Customer_B", Status = CustomerStatus.IsActive, LastLogon = DateTime.Now.AddDays(-50) });
context.Customers.Add(new Customer() { Name ="Customer_C", Status = CustomerStatus.IsDeleted, LastLogon = DateTime.Now.AddDays(-10) });
context.Customers.Add(new Customer() { Name ="Customer_D", Status = CustomerStatus.IsActive, LastLogon = DateTime.Now.AddDays(-15) });
using (var context = new EntityContext())
var customers = context.Customers.WhereDynamic(x => "x.Status == IsActive && x.LastLogon >= LastMonth", new {
IsActive = CustomerStatus.IsActive,
IsDeleted = CustomerStatus.IsDeleted,
IsSuspended = CustomerStatus.IsSuspended,
LastWeek = DateTime.Now.AddDays(-7),
LastMonth = DateTime.Now.AddMonths(-1),
LastYear = DateTime.Now.AddYears(-1)
FiddleHelper.WriteTable(customers);
var environmentVariables = new Dictionary<string, object>();
environmentVariables.Add("IsActive", CustomerStatus.IsActive);
environmentVariables.Add("IsDeleted", CustomerStatus.IsDeleted);
environmentVariables.Add("IsSuspended", CustomerStatus.IsSuspended);
environmentVariables.Add("LastWeek", DateTime.Now.AddDays(-7));
environmentVariables.Add("LastMonth", DateTime.Now.AddMonths(-1));
environmentVariables.Add("LastYear", DateTime.Now.AddYears(-1));
var customers = context.Customers.WhereDynamic(x => "x.Status == IsActive && x.LastLogon >= LastMonth", environmentVariables).ToList();
FiddleHelper.WriteTable(customers);
dynamic expandoObject = new ExpandoObject();
expandoObject.IsActive = CustomerStatus.IsActive;
expandoObject.IsDeleted = CustomerStatus.IsDeleted;
expandoObject.IsSuspended = CustomerStatus.IsSuspended;
expandoObject.LastWeek = DateTime.Now.AddDays(-7);
expandoObject.LastMonth = DateTime.Now.AddMonths(-1);
expandoObject.LastYear = DateTime.Now.AddYears(-1);
var customers = context.Customers.WhereDynamic(x => "x.Status == IsActive && x.LastLogon >= LastMonth", (object)expandoObject).ToList();
FiddleHelper.WriteTable(customers);
var customers = context.Customers.WhereDynamic(x => "x.Status == IsActive && x.LastLogon >= LastMonth", new PredefinedVariables()).ToList();
FiddleHelper.WriteTable(customers);
public class EntityContext : DbContext
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
optionsBuilder.UseSqlServer(new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()));
base.OnConfiguring(optionsBuilder);
public DbSet<Customer> Customers { get; set; }
public int CustomerID { get; set; }
public string Name { get; set; }
public CustomerStatus Status { get; set; }
public DateTime LastLogon { get; set; }
public enum CustomerStatus
public class PredefinedVariables
public CustomerStatus IsActive = CustomerStatus.IsActive;
public CustomerStatus IsDeleted = CustomerStatus.IsDeleted;
public CustomerStatus IsSuspended = CustomerStatus.IsSuspended;
public DateTime LastWeek = DateTime.Now.AddDays(-7);
public DateTime LastMonth = DateTime.Now.AddMonths(-1);
public DateTime LastYear = DateTime.Now.AddYears(-1);