// C# Eval Expression

// Doc: https://eval-expression.net/my-first-linq-dynamic

​

// @nuget: Microsoft.EntityFrameworkCore.SqlServer

// @nuget: Z.Expressions.Eval

​

/*

## Use LINQ Dynamic with the Execute method

​

The LINQ Execute method is the most flexible of our LINQ Dynamic method. Not only can the user filter a query dynamically, but he can do whatever he wants in the expression.

​

In this example, we will query customers in the database and:

​

- Filter the customers to returns

- Order the returned customers

- Select columns that we return

​

One major difference with the `Execute` method is that you call the LINQ method directly and not the `dynamic` counterpart. It makes sense as the `Execute` method behavior is really to execute C# code at runtime.

​

See more: https://eval-expression.net/my-first-linq-dynamic

*/

​

using System;

using System.Collections;

using System.Collections.Generic;

using System.Linq;

using Z.Expressions;

using Microsoft.EntityFrameworkCore;

using Microsoft.Data.SqlClient;

​

public class Program

{

    public static void Main()

    {

        using (var context = new EntityContext())

        {

            context.Database.EnsureCreated();

        }

        // GENERATE Data

        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) });

            context.SaveChanges();  

        }

        using (var context = new EntityContext())

        {

            var environmentVariables = new Dictionary<string, object>();

            // ADD Status variables

            environmentVariables.Add("IsActive", CustomerStatus.IsActive);

            environmentVariables.Add("IsDeleted", CustomerStatus.IsDeleted);

            environmentVariables.Add("IsSuspended", CustomerStatus.IsSuspended);

            // ADD DateTime variable

            environmentVariables.Add("LastWeek", DateTime.Now.AddDays(-7));

            environmentVariables.Add("LastMonth", DateTime.Now.AddMonths(-1));

            environmentVariables.Add("LastYear", DateTime.Now.AddYears(-1));            

            // In this example, we will query customers in the database and:

            // - Filter the customers to returns

            // - Order the returned customers

            // - Select columns that we return

            var customers = context.Customers.Execute<IEnumerable>("Where(x => x.Status == IsActive && x.LastLogon >= LastMonth).Select(x => new { x.CustomerID, x.Name }).OrderBy(x => x.CustomerID).ToList()", environmentVariables);

            FiddleHelper.WriteTable(customers);

        }

    }

    public class EntityContext : DbContext

    {

        public EntityContext() 

        {

        }

​

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)

        {

            optionsBuilder.UseSqlServer(new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()));

​

            base.OnConfiguring(optionsBuilder);

        }

        public DbSet<Customer> Customers { get; set; }

    }

    public class Customer

    {

        public int CustomerID { get; set; }

        public string Name { get; set; }

        public CustomerStatus Status { get; set; }

        public DateTime LastLogon { get; set; }

    }

    public enum CustomerStatus

    {

        IsActive = 0,

        IsDeleted = 1,

        IsSuspended = 2

    }

}