Retrieve user defined 'Combination of Columns' from database with Entity Framework

Question

I need to retrieve user defined columns from database with Entity Framework. I need to create column Projection based on passed collection, a List of strings, where each element contains column names, with Entity Framework

I have a list of string which contains strings like this;

   List<string> columnsToSelect = new List<string>();
   columnsToSelect.Add("col1 + col2");
   columnsToSelect.Add("col2");
   columnsToSelect.Add("col3 + col4");

I have a table called 'RawData' with 6 columns like this: Id EmpId col1 col2 col3 col4

Now, if i were to query simple

var rawDatacolums = Context.RawData.where(a => a.EmpId = @EmpId)

this will generate SQL statement like this,

Select Id,EmpId,col1,col2,col3,col4 from  RawData
where EmpId = @EmpId

Here i want to pass columnsToSelect as argument and my result should be based on column selector i am passing in List

What i want to do is,

var rawDatacolums = Context.RawData.select(columnsToSelect).where(a => 
a.EmpId = @EmpId)

Which should generate SQL like;

Select col1 + col2 as Col1Col2, col2 as Col2, col3 + col4 as Col3Col4 
from  RawData where EmpId = @EmpId

I have tried to use "SelectProperties" from this article here:

https://byalexblog.net/entity-framework-dynamic-columns https://github.com/lAnubisl/entity-framework-dynamic-queries/blob/master/entity-framework-dynamic-queries/SelectiveQuery.cs

var rawDatacolums = Context.RawData.SelectProperties(columnsToSelect) 

if a pass exact columns like col1, col2 as list it works but it doesn't work the way i want for example Sum of Two columns

My Requirement is i need to project addition of columns like 'col1 + col2' & 'col3 + col4'

Updated Answer

Based on couple of suggestions, i played more with Dynamic LINQ and i made it work, i was able to apply various math conditions on my projection and was able to create Dynamic Class out of it

The original github reference is as below: https://github.com/kahanu/System.Linq.Dynamic

but i found explanation here more useful please take a look here: http://ak-dynamic-linq.azurewebsites.net/GettingStarted#conversions

Some other material i referred and had to use - which maybe helpful to someone is here - http://www.albahari.com/nutshell/predicatebuilder.aspx

Sample working code would look like this;

            var sampleQuery = "New(Col1+Col2 as Stage1Count)";
            IEnumerable queryResult= Context.RawData.AsQueryable().Select(sampleQuery );


            System.Diagnostics.Debug.WriteLine("Debug Sample Query: " + queryResult.ToString());
            foreach (var cust in queryResult)
            {
                System.Diagnostics.Debug.WriteLine("Debug Sample StageCount : " + cust.ToString());
            }

Thanks all for your comments and suggestions! Cheers!

Answer 1

It is obviously possible to create classes at runtime, or even new anonymous types, but they are extremely limited in how you can use them in your code.

If you prefer to work within the modern generic Queryable framework, and avoid creating classes at runtime that have limited compile time access, you can roll your own expression parser and build Expression trees. The trick is to use an Array type as the return from the Select to make the members accessible. This does mean all the expressions must return the same type, but this implementation will convert all the expressions to one type if necessary.

Here is a sample implementation:

public static class IQueryableExt {
    public static Expression<Func<TRec, TVal?[]>> SelectExpr<TRec, TVal>(this IEnumerable<string> strExprs) where TVal : struct {
        var p = Expression.Parameter(typeof(TRec), "p");
        var exprs = strExprs.Select(se => {
            var e = se.ParseExpression(p);
            return e.Type.IsNullableType() && e.Type.GetGenericArguments()[0] == typeof(TVal) ? e : Expression.Convert(e, typeof(TVal?));
        }).ToArray();

        return Expression.Lambda<Func<TRec, TVal?[]>>(Expression.NewArrayInit(typeof(TVal?), exprs), p);
    }

    static char[] operators = { '+', '-', '*', '/' };
    static Regex tokenRE = new Regex($@"(?=[-+*/()])|(?<=[-+*/()])", RegexOptions.Compiled);
    static HashSet<char> hsOperators = operators.ToHashSet();
    static Dictionary<char, ExpressionType> opType = new Dictionary<char, ExpressionType>() {
        { '*', ExpressionType.Multiply },
        { '/', ExpressionType.Divide },
        { '+', ExpressionType.Add },
        { '-', ExpressionType.Subtract }
    };

    static int opPriority(char op) => hsOperators.Contains(op) ? Array.IndexOf(operators, op) >> 1 : (op == ')' ? -1 : -2);

    public static Expression ParseExpression(this string expr, ParameterExpression dbParam) {
        var opStack = new Stack<char>();
        opStack.Push('(');
        var operandStack = new Stack<Expression>();

        foreach (var t in tokenRE.Split(expr).Where(t => !String.IsNullOrEmpty(t)).Append(")")) {
            if (t.Length > 1) // process column name
                operandStack.Push(Expression.PropertyOrField(dbParam, t));
            else {
                while (t[0] != '(' && opPriority(opStack.Peek()) >= opPriority(t[0])) {
                    var curOp = opStack.Pop();
                    var right = operandStack.Pop();
                    var left = operandStack.Pop();
                    if (right.Type != left.Type) {
                        if (right.Type.IsNullableType())
                            left = Expression.Convert(left, right.Type);
                        else if (left.Type.IsNullableType())
                            right = Expression.Convert(right, left.Type);
                        else
                            throw new Exception($"Incompatible types for operator{curOp}: {left.Type.Name}, {right.Type.Name}");
                    }
                    operandStack.Push(Expression.MakeBinary(opType[curOp], left, right));
                }
                if (t[0] != ')')
                    opStack.Push(t[0]);
                else
                    opStack.Pop(); // pop (
            }
        }
        return operandStack.Pop();
    }

    public static bool IsNullableType(this Type nullableType) =>
    // instantiated generic type only                
        nullableType.IsGenericType &&
        !nullableType.IsGenericTypeDefinition &&
        Object.ReferenceEquals(nullableType.GetGenericTypeDefinition(), typeof(Nullable<>));
}

Unfortunately type inference can't easily get the answer type, so you have to manually pass in the record type and answer type. Note there's special code in the parser to handle conversion to (common in SQL) nullable types when mixing nullable and non-nullable.

Given the columnsToSelect you provided as an example:

List<string> columnsToSelect = new List<string>();
columnsToSelect.Add("col1 + col2");
columnsToSelect.Add("col2");
columnsToSelect.Add("col3 + col4");

You can query the database like so:

var queryResult= Context.RawData.Select(columnsToSelect.SelectExpr<TRawData, int>());

And queryResult would be of type IQueryable<int[]> or IQueryable<int?[]> depending on the SQL column types.