0

I would like to dynamically build the linq expression used in the following Select statement on a DataTable filled from a DB:

IQueryable iq = myDataTable
    .AsEnumerable()
    .AsQueryable()
    .Select(
        d => new {
            Field1 = d.Field<int>(37),
            Field2 = d.Field<string>(0),
            Field3 = d.Field<DateTime>(1)
            }
    );

The fields are not known before runtime. I will get the list of fields ("Field1", "Field2", "Field3"...), their types (int, string, datetime...) and their indexes (37, 0, 1...) at runtime.

Is there any way to achieve that?

[UPDATE]

I started to write the following code:

Type type = typeof(DataRow);

MethodInfo mi1 = typeof(DataRowExtensions).GetMethod("Field", new Type[] { typeof(DataRow), typeof(int) });
mi1 = mi1.MakeGenericMethod(type);
List<Expression> list1 = new List<Expression>();
ParameterExpression datarow1 = Expression.Parameter(type, "datarow");
ConstantExpression constant1 = Expression.Constant(37, typeof(int));
list1.Add(datarow1);
list1.Add(constant1);
ReadOnlyCollection<Expression> collection1 = new ReadOnlyCollection<Expression>(list1);
MethodCallExpression right1 = Expression.Call(null, mi1, datarow1, constant1);
Expression left1 = Expression.Parameter(type, "Field1");
var expression1 = Expression.Assign(left1, right1);

MethodInfo mi2 = typeof(DataRowExtensions).GetMethod("Field", new Type[] { typeof(DataRow), typeof(int) });
mi2 = mi2.MakeGenericMethod(type);
List<Expression> list2 = new List<Expression>();
ParameterExpression datarow2 = Expression.Parameter(type, "datarow");
ConstantExpression constant2 = Expression.Constant(0, typeof(int));
list2.Add(datarow2);
list2.Add(constant2);
ReadOnlyCollection<Expression> collection2 = new ReadOnlyCollection<Expression>(list2);
MethodCallExpression right2 = Expression.Call(null, mi2, datarow2, constant2);
Expression left2 = Expression.Parameter(type, "Field2");
var expression2 = Expression.Assign(left2, right2);

List<Expression> bindings = new List<Expression>();
bindings.Add(expression1);
bindings.Add(expression2);
var newInit = Expression.New(typeof(object));
var init = Expression.NewArrayInit(type, bindings);

var dr = Expression.Parameter(type, "dr");
var linq = Expression.Lambda<Func<DataRow, DataRow>>(init, dr);

It compiles, however, there are several problems:

  • expression1 is (Field1 = datarow.Field(0)) instead of Field1 = datarow.Field<int>(0)
  • same for expression 2
  • it throws a runtime exception: 'System.Data.DataRow[]' cannot be used for return type 'System.Data.DataRow' on the line var linq = Expression.Lambda...

Could you please help?

@George I think I do not need a Type dictionary as I have a set of predefined types.

Reda
  • 13
  • 5
  • check out the dynamic linq library unless you wanna start building expression trees – johnny 5 Sep 03 '15 at 19:35
  • http://stackoverflow.com/questions/23787795/dynamic-linq-expression – Ruchi Sep 03 '15 at 19:36
  • 2
    First of all, it seems very bad to call "AsEnumerable()" followed by "AsQueryable".From your example your data is in memory. Generally speaking, If your data is in memory you don't need to call AsQueryable and if your data is in db (so myDataTable is in fact an objectset) you don't need to call AsEnumerable first. – George Lica Sep 03 '15 at 19:41
  • 1
    Build dynamic query (in string) and then run it – Lali Sep 03 '15 at 19:47
  • @Goerge Lica: I need an IQueryable result. The only way to convert DataTable to IQueryable is to call .AsEnumerable( ).AsQueryable( ) successively. DataTable doesn't directly implement AsQueryable( ). – Reda Sep 04 '15 at 09:44

1 Answers1

0

I strongly recommend for this kind of situations to build a query string and run it against your database.

But ... for the sake of art, you can do this. To achieve what you want you need more than just creating an expression tree. First, you will need a "pool" of anonymous types for each projection case found at runtime. Id est, you need to have a Dictionary < somekey, sometype> where somekey can be a string that represents a serialized combination of columns (column indexes, etc) and sometype will be a real System.Type. But wait those types does not exists at compilation time. The question is: How to create these types?. The ONLY solution is to use Reflection.Emit and AssemblyBuilder, TypeBuilder, ModuleBuilder, etc ... in order to create new types at runtime. Belive me or not, anonymous types are just normal types but C# compiler does this magic for us. You would also need a mapping for each column found in your datatable and each property defined in your new type.

1) So ... you eventually request an "anonymous type" for your particular situation. If that type is not currently built, just built it before, store it in your dictionary and then ... use it.

2) The remaining job is to emit the select body wich is actually simple. Use Expression.MemberInit and Expression.Bind API's. Search for them on google, you will find plenty of informations. To find out what to "Bind" using Expression.Bind, you search in the second dictionary (the mapping between column and property).

Hope this helps. I cannot give you code because I am writing from a device where i don't have visual studio.

George Lica
  • 1,798
  • 1
  • 12
  • 23
  • I have tables that are created every day in the DB for which the schema is not known in advance. Tables can contain large amounts of data, like 100 columns and several hundred thousand rows. I need to display this data in a third party UI component (grid view). This component can work in server mode (doing all its calculation on the server, thus preventing the need to load several MB of data on the client side). To work in server mode, it needs an IQueryable result. Thank you for your answer. I'll look into it in more details.. so far it's chinese to me ;) – Reda Sep 04 '15 at 09:39
  • Do you need only to show their content? (Like a report or something) or you need to perform operations such as delete, update, create?. If all that you need is a readonly datagrid, just build a dynamic "red code" sql, fill a datatable and bind your component to it. It is the most practical way of doing this. What i've described is a state of the art way of doing this by using advanced features like expression trees and dynamic code compilation. – George Lica Sep 04 '15 at 17:12