Need: To allow the user to select 3 columns to return from a local database. With the help of another post I was able to return 1 column for a different application in the program, and I figured I could apply the same process here, but I'm not seeing how to allow the user to select multiple columns within the same query. I see where Func<> accepts up to 17 overloads where all but one can be fields, but that doesn't seem to be the right approach.
Example:
TableX has the following columns: A,B,C,D,E,F
A B C D E F
1 2 6 2 4 6
3 6 2 7 2 1
4 8 0 3 7 6
User Selects columns A, D, F
Query should return:
1 2 6
3 7 1
4 3 6
SQL statement to achieve this would be the following:
col1 = user selection 1.SelectedItem.ToString();
col2 = user selection 2.SelectedItem.ToString();
col3 = user selection 3.SelectedItem.ToString();
SELECT @col1, @ col2, @col3 FROM TableX;
Linq statement would be something like the following if I knew the fields that were going to be selected before runtime:
(from UserPageTbl in conn.Table<TableX>()
select new
{
TableX.A,
TableX.B,
TableX.C,
}
).ToList();
*but I don't see how to parameterize selections in LINQ and pass them as a field in a select statement.
I get the concept of stacking sections of the Linq Expressions using an Expression Tree to return 1 column (like an ORDERBY and a WHERE clause), but I don't see how to do that for multiple columns. It would work the same way to build a SQL statement. I tried to use the Expression.New thinking that I needed the new {} to generate a list of the return columns and that didn't seem to be the right process.
Current Set Up: Using an extension method with an expression tree and lambda expression with LINQ to return 1 column. This does work. See Code below:
private static IQueryable<TResult> SelectByFieldName<T, TResult>(IQueryable<T> query, string fieldName)
{
var param = Expression.Parameter(typeof(T), "e");
Expression body = Expression.PropertyOrField(param, fieldName);
if (body.Type != typeof(TResult))
body = Expression.Convert(body, typeof(TResult));
var lambda = Expression.Lambda<Func<T, TResult>>(body, param);
return query.Select(lambda);
}
Problem: I cannot figure out how to add additional columns to the lambda expression to return multiple selected columns.
Trying to combine the items in a Select Query lambda expression as above:
private static IQueryable<TResult> GraphFields<T, a, b, c, TResult>(IQueryable<T> query, string aField, string bField, string cField)
{
var param = Expression.Parameter(typeof(T), "e");
Expression aFieldBody = Expression.PropertyOrField(param, aField);
if (aFieldBody.Type != typeof(TResult))
aFieldBody = Expression.Convert(aFieldBody, typeof(TResult));
Expression bFieldBody = Expression.PropertyOrField(param, bField);
if (bFieldBody.Type != typeof(TResult))
bFieldBody = Expression.Convert(bFieldBody, typeof(TResult));
Expression cFieldBody = Expression.PropertyOrField(param, cField);
if (cFieldBody.Type != typeof(TResult))
cFieldBody = Expression.Convert(cFieldBody, typeof(TResult));
var lambdaA = Expression.Lambda<Func<T, TResult>>(aFieldBody, param);
var lambdaB = Expression.Lambda<Func<T, TResult>>(bFieldBody, param);
var lambdaC = Expression.Lambda<Func<T, TResult>>(cFieldBody, param);
return query.Select(lambdaA, lambdaB, lambdaC);
}
I've also tried writing the following using the field variables in the "Select" statement,
return query.Select(new { aFieldBody, bFieldBody, cFieldBody }, param);
I've tried the following, because I thought multiple overloads would allow me to
var lambda = Expression.Lambda<Func<T,a,b, TResult>>(aFieldBody, bFieldBody, cFieldBody, param);
I've also researched adding it to the PropertyOrField but it only accepts two overloads one for the parameter expression and 1 for the field name. I just can't figure out how to append more columns.
Tried using Linq.Dynamic.Core, by doing the following:
var list = conn.Table<Table1>()
.Where(t => t.User_ID == user).Distinct().ToList();
var listquery = list.AsQueryable();
var Values = listquery
.Select("new {Field1}")
.ToDynamicList();
I get the following exception: System.TypeInitializationException thrown The type initializer for System.Linq.Dynamic.Core.Parser.EnumerationsFromMscorlib threw an exception.
