Dynamic Linq Expression in .NET Core

Question

First

I cannot use Entity Framework for this because this is a Desktop application in which all database calls are first routed through a Web API End Point. A requirement of this application is that all traffic is HTTP traffic.

Setup

Lets say I have an IEnumerable<BaseItem> where the BaseItem is a base model class that all models inherit from.

We also dynamically instantiate and populate our collection with a dynamic class and dynamic properties that match the column names and appropriate data types of the System.DataTable that is returned from the query.

These dynamic classes also inherit from BaseItem which exposes two functions:
void SetValue(string PropertyName, object Value)
object GetValue(string PropertyName).

Issue

I need to be able to write a linq query that dynamically selects properties from the collection based on the unique constraint columns from the table that the query referenced. I have that in a separate List, where the string values match the dynamic property names on the dynamic class.

I know the solution is probably to use an Expression Tree, but all the examples I find use actual property references, and in this case, the property can only be accessed and set through the GetValue, and SetValue functions. I'm having a hard time wrapping my head around how to achieve this. Below is an example of the linq query i want, if I KNEW the fields I wanted to return.

var Result = DefaultCollection.Select(x => new { BUSINESS_UNIT = x.GetValue("BUSINESS_UNIT"), FISCAL_YEAR = x.GetValue("FISCAL_YEAR") }).Distinct();

Again, I can't write this because I don't know which fields i need to include in the select list until runtime when I can reference the UniqueConstraint List.

How can I achieve this dynamically with Linq Expression?

Start To Finish Example

You've populated an IEnumerable<BaseItem> from a System.Data.DataTable and lets say the query that you ran was SELECT FIRST_NAME, LAST_NAME, EMAIL ADDRESS FROM TABLEA.

The IEnumerable is populated from automation in which a dynamic class is generated to represent a row in the table and dynamic properties are added to the class to represent each column in the table.

Accessing the properties is via methods in BaseItem string result = item.GetValue("FIRST_NAME") and item.SetValue("EMAIL_ADDRESS", "MyEmail@Email.com');

You also queried INFORMATION_SCHEMA to get unique constraints on TABLEA and you store it in a public class ConstraintModel object that has a public List<string> Columns representing the columns that make up the unique constraint. In this case, only one column "EMAIL_ADDRESS" is in the constraint.

If the user makes changes to the IEnumerable<BaseItem> and more than one item has the same EMAIL_ADDRESS, we know that the UPDATE statement back to SQL will fail. We want to check that the unique constraint hasn't been violated.

At the point the user saves, we want to run a "Validation Check" on the in memory IEnumerable object. Ideally we would write a linq query that essentailly did a SELECT SUM(1), EMAIL_ADDRESS GROUP BY EMAIL_ADDRESS, and if any of the results had more than 1 value, we know a duplicate exists. Obviously the above syntax is SQL not Linq, but you get the idea. I can't know at compile time what fields on the BaseItem i need to select and group by, so I need to leverage the Constraint.Columns object and use it to write a dynamic linq expression. Additionally, the properties on the Dynamic Class are only exposed through the methods that they inherit from BaseItem ... GetValue(string PropertyName) and SetValue(string PropertyName, object Value);

Answer 1

After reading your full example it sounds very much like you do not need dynamic linq at all to do what you're trying to do, but you do need to know what the Primary Key is, why not try something like:

Update I have replaced a simple pk lookup with a func PK lookup so that composite Primary Keys can be used.

List<string> primaryKeyColumns = new List<string> {"Id"}; // or whatever else
List<string> uniques = /// ConstraintModel.Columns;
List<BaseItem> baseItems = // your baseItems

// Now, wit
Func<BaseItem, BaseItem, bool> equalPrimaryKey = (left, right) => {
   foreach(var pk in primaryKeyColumns) {
     if(left.GetValue(pk) != right.GetValue(pk)) {
        return false;
     }
   }

   return true;
}; // or whatever else

var violators = baseItems.Select(x => {
   return baseItems.Any(z => {
      foreach(var unique in uniques) {
         if (z.GetValue(unique) == x.GetValue(unique) 
            && !equalPrimaryKey(x, z)) {
            return true;
         }
      }

     return false;
   });
}).ToArray();

This will give you an array of base items that are duplicated by any unique column

Answer 2

Using a minified version of my EnumerableExpressionExt class for helping build Expression trees for Enumerable expressions, and my ExpressionExt helper class:

public static class EnumerableExpressionExt {
    private static Type TEnumerable = typeof(Enumerable);
    private static Type TypeGenArg(this Expression e, int n) => e.Type.GetGenericArguments()[n];

    public static MethodCallExpression Distinct(this Expression src) => Expression.Call(TEnumerable, "Distinct", new[] { src.TypeGenArg(0) }, src);

    public static MethodCallExpression Select(this Expression src, LambdaExpression resultFne) => Expression.Call(TEnumerable, "Select", new[] { src.TypeGenArg(0), resultFne.ReturnType }, src, resultFne);
}

public static class ExpressionExt {
    public static ConstantExpression AsConst<T>(this T obj) => Expression.Constant(obj, typeof(T));

    public static LambdaExpression AsLambda(this Expression body) => Expression.Lambda(body);
    public static LambdaExpression Lambda(this ParameterExpression p1, Expression body) => Expression.Lambda(body, p1);

    public static NewExpression New(this Type t, params Expression[] vals) => Expression.New(t.GetConstructors()[0], vals, t.GetProperties());    

    public static ParameterExpression Param(this Type t, string pName) => Expression.Parameter(t, pName);
}

You can create custom Expression extensions to translate your specific methods:

public static class MyExpressionExt {
    public static MethodCallExpression GetValue(this Expression obj, string propName) =>
        Expression.Call(obj, "GetValue", null, propName.AsConst());
}

You can build your Expression tree as follows:

// BaseItem x
var xParm = typeof(BaseItem).Param("x");
// typeof(new { object BUSINESS_UNIT, object FISCAL_YEAR })
var anonType = (new { BUSINESS_UNIT = default(object), FISCAL_YEAR = default(object) }).GetType();
// new { BUSINESS_UNIT = x.GetValue("BUSINESS_UNIT"), FISCAL_YEAR = x.GetValue("FISCAL_YEAR") }
var newExpr = anonType.New(xParm.GetValue("BUSINESS_UNIT"), xParm.GetValue("FISCAL_YEAR"));
// DefaultCollection.Select(x => newExpr)
var selExpr = DefaultCollection.AsConst().Select(xParm.Lambda(newExpr));
// DefaultCollection.Select(x => newExpr).Distinct()
var distExpr = selExpr.Distinct();

And you can test it (using LINQPad's Dump method) like this:

// () => DefaultCollection.Select(x => newExpr).Distinct()
var f = distExpr.AsLambda();
var fc = f.Compile();    
fc.DynamicInvoke().Dump();

LINQPad is also very helpful for outputting compiler built Expression trees and seeing how they were built, or IQueryable<T> queries Expression member trees.

NOTE: I like not having to call AsConst() so I have additional helpers to make that easier as well.