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", "[email protected]');

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

Can you give an example of dynamically selects properties from the collection based on the unique constraint columns from the table that the query referenced and I have that in a separate List, where the string values match the dynamic property names on the dynamic class (specifically, show us the API that allows you to 'dynamically select properties based on constraint columns' and 'the list with string values') — zaitsman
– zaitsman, Commented Sep 15, 2020 at 14:20
It sounds like you should be doing this in two steps. First, get your dynamic query results, then create a new dynamic object with only the columns the caller requires. Or alternatively turn the resultset into a List<Dictionary<string,object>> or something and remove the items from each Dictionary that the caller didn't ask for. — Captain Kenpachi
– Captain Kenpachi, Commented Sep 15, 2020 at 14:24
@zaitsman - i added a start to finish example, hopefully that is clarifying. — Thrifty2100
– Thrifty2100, Commented Sep 15, 2020 at 17:11
Accessing data via http API calls is what a lot of people do. That can't be the sole reason why somebody decided that this architecture was the best option. I'm just wondering what is it that prevents your application (not that you can change it now) from using more standard approaches to retrieve and change data (not even using Entity Framework). Maybe there is a special use case that forces you to discover everything at runtime, I'm just curious. — insane_developer
– insane_developer, Commented Sep 15, 2020 at 18:07

zaitsman · Accepted Answer · 2020-09-16 22:59:51Z

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

edited Sep 16, 2020 at 22:59

answered Sep 16, 2020 at 2:12

zaitsman

9,5908 gold badges53 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Thrifty2100 Over a year ago

this solution is simple and effective. But where I believe it fails is: if the primary key is more than one column. Also, while the primary key is by default a unique constraint, and perhaps that is only one column, you can have other unique constraints that include multiple columns.

zaitsman Over a year ago

@Thrifty2100 you can easily modify it for composite PK by defining a method group, will update my answer. For composite unique constraints, what is your code’s mode of describing those? If you add that I can include an update for this, too

Thrifty2100 Over a year ago

looks like var violatersreturns IEnumerable<bool>, so i changed it from DefaultCollection.Select() to DefaultCollection.Where(). Also each item is checked against itself, so it will always come back as a violation. So I added if (left.Equals(right)) return false; in the original equalConstraint Func.

NetMage · Accepted Answer · 2020-09-16 00:43:16Z

1

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.

edited Sep 16, 2020 at 0:43

answered Sep 16, 2020 at 0:11

NetMage

27k3 gold badges39 silver badges57 bronze badges

3 Comments

Thrifty2100 Over a year ago

I'm going to try and incorporate this and see if it works. i may need to use linq to query my Constraint List to dynamically populate the # of parameters in the newExpr. In your example it is hard coded for 2. Not sure if that makes sense? But the challenge is that 1. which properties we want to select against and 2. how many properties we are selecting against - these are both variable and uknown at compile time.

NetMage Over a year ago

@Thrifty2100 You can just pass in an array of values instead of discrete values to a params parameter and the code will work.

NetMage Over a year ago

@Thrifty2100 However, creating an anonymous object at runtime with varying number of fields for the result is of questionable value. Perhaps you could plan to use a Dictionary or List<(string,object)> or even just List<object> or object[] instead? Something designed to be varied at runtime.

Collectives™ on Stack Overflow

Dynamic Linq Expression in .NET Core

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related