Add new lambda expressions using Expression Tree

Question

I have been looking at many posts here and on the web but none of them seem to be helping.

I have a table with about 2 million records, it has over 200 columns.

A simple web service allow the user to pull a specific number of columns out of the table, the user has the option to choose which column to pull.

The result needs to be string of comma separated values, so my query needs to pull the requested columns and return a concatenate string.

I have done this using ADO.NET and pure SQL queries it works fine however I was asked to do it in Entity Framework.

Here is what I have and have done already.

I get the requested columns list as an array of strings.

The following is my query, not sure if it's the best solution or idea hence I'm asking for help here.

var valueList2 = ctx.mytable.Where(x => x.pcds == comValue).Select(x => new{temp = x.column1 +", "+ x.column2}).Select(x => x.temp).ToList();

The above gives me string of two columns separated by commas, I just need to somehow push my array of column names into the lambda part of it.

I did the following but then realised that it only works with a specific type of a class not anonymous, also I can't figure out how I can use it for a multiple columns and not make it so complex.

var createdType = typeof(mytable);
var Param = Expression.Parameter(typeof(string), "pr");
var obj = Expression.New(createdType);
var ValueProperty = createdType.GetProperty("long");
var ValueAssignment = Expression.Bind(ValueProperty, Param);
var memberInit = Expression.MemberInit(obj, ValueAssignment);
var lm = Expression.Lambda<Func<string, mytable>>(memberInit, Param);

Thank you

Answer 1

This is a short and easy answer for whoever needs a different answer, but per our discussion with @xanatos, it's not the best as it also returns all the columns which need to be cut off before adding to a list of strings.

List<string> valueList = new List<string>();
using (var ctx = new DataEntities1())
{
    var query = ctx.myTable.Where(x => x.pcds == scode).SingleOrDefault();

    foreach (var item in columnsArray)
    {
        valueList.Add(typeof(myTable).GetProperty(onsColumns[Convert.ToInt32(item)]).GetValue(query).ToString());
    }
}

Answer 2

I'm using Dynamic Linq (source code). Sadly there is little documentation about how to use it :-) In a fun boomerang effect, there is an "evolved" version. The boomerang effect is because the code for generating the dynamic class is based on one of my responses :-) The remaining code seems to be very beautiful... And there is a full suit of unit tests with code samples!!! Note that this second library is a superset of the first library, so you can probably apply many examples to the first one! :-)

I'm adding some static methods to translate the result of a a Dynamic Linq query to a IEnumerable<object[]>.... Example code:

using (var ctx = new Model1())
{
    var result = ctx.MyTable
        .Take(100)
        .SimpleSelect(new[] { "ID", "Col1", "Col2" })
        .ToObjectArray();

    foreach (var row in result)
    {
        Console.WriteLine(string.Join(", ", row));
    }
}

More complex example:

var columnsNames = new[] { "SomeNullableInt32", "SomeNonNullableDateTimeColumn" };

// One for each column!
var formatters = new Func<object, string>[]
{
    x => x != null ? x.ToString() : null,
    x => ((DateTime)x).ToShortDateString()
};

var result = ctx.MyTable.Take(100).SimpleSelect(columnsNames).ToObjectArray();

foreach (var row in result)
{
    var stringRow = new string[row.Length];

    for (int i = 0; i < row.Length; i++)
    {
        stringRow[i] = formatters[i](row[i]);
    }

    Console.WriteLine(string.Join(", ", stringRow));
}

And the classes... One (SimpleSelect) produces the Dynamic SQL Select, and "anonymizes" the field names. I do this because for each type of return the Dynamic Linq will generate at runtime a class. This class won't be unloaded until the program ends. By anonymizing the columns (I rename them to Item1, Item2, Item3...) I increase the possibility that the same class will be reused. Note that different type of columns will generate different classes! (int Item1, string Item2 will be a different class from int Item1, DateTime Item2), the other (ToObjectArray) returns a IEnumerable<object[]>, something easier to parse.

public static class DynamicLinqTools
{
    private static ConcurrentDictionary<Type, Func<object, object[]>> Converters = new ConcurrentDictionary<Type, Func<object, object[]>>();

    public static IQueryable SimpleSelect(this IQueryable query, string[] fields)
    {
        // With a little luck, "anonymizing" the field names we should 
        // reduce the number of types created!
        // new (field1 as Item1, field2 as Item2)
        return query.Select(string.Format("new ({0})", string.Join(", ", fields.Select((x, ix) => string.Format("{0} as Item{1}", x, ix + 1)))));
    }

    public static IEnumerable<object[]> ToObjectArray(this IQueryable query)
    {
        Func<object, object[]> converter;

        Converters.TryGetValue(query.ElementType, out converter);

        if (converter == null)
        {
            var row = Expression.Parameter(typeof(object), "row");

            // ElementType row2;
            var row2 = Expression.Variable(query.ElementType, "row2");

            // (ElementType)row;
            var cast = Expression.Convert(row, query.ElementType);

            // row2 = (ElementType)row;
            var assign = Expression.Assign(row2, cast);

            var properties = query.ElementType.GetProperties(BindingFlags.Public | BindingFlags.Instance)
                .Where(x => x.CanRead && x.GetIndexParameters().Length == 0)
                .ToArray();

            // (object)row2.Item1, (object)row2.Item2, ...
            var properties2 = Array.ConvertAll(properties, x => Expression.Convert(Expression.Property(row2, x), typeof(object)));

            // new object[] { row2.Item1, row2.Item2 }
            var array = Expression.NewArrayInit(typeof(object), properties2);

            // row2 = (ElementType)row; (return) new object[] { row2.Item1, row2.Item2 }
            var body = Expression.Block(typeof(object[]), new[] { row2 }, assign, array);

            var exp = Expression.Lambda<Func<object, object[]>>(body, row);
            converter = exp.Compile();

            Converters.TryAdd(query.ElementType, converter);
        }

        foreach (var row in query)
        {
            yield return converter(row);
        }
    }
}