Dynamically generating nested queries

Question

The ultimate objective is to have a piece of code that you can feed definitions of simple queries to and it generate data.

so given i can create this query

var query =
    from cha in ds.Channels
    select new object[] 
    { 
        cha.ChanLanguagestreamlocking, 
        cha.ChanMinimumtrailerduration,
        from link in cha.Channelaudiolanguagelinks
        select new object[]
        {
            link.ObjDatecreated
        }
    };

var data = query.ToArray();

how would i create it dynamically?

well i can get this far using a class stolen from another stack overflow question

public class SelectList<TSource>
{
    private List<LambdaExpression> members = new List<LambdaExpression>();
    public SelectList<TSource> Add<TValue>(Expression<Func<TSource, TValue>> selector)
    {
        members.Add(selector);
        return this;
    }

    public Expression<Func<TSource, object[]>> ToDynamicColumns()
    {
        var parameter = Expression.Parameter(typeof(TSource), "e");
        return Expression.Lambda<Func<TSource, object[]>>(
            Expression.NewArrayInit(
                typeof(object),
                members.Select(m =>
                    Expression.Convert(Expression.Invoke(m, parameter), typeof(object))
                )
            ),
            parameter);
    }
}

Imagine a case where the definition of which 'columns' to return is passed as a tree at runtime, this can then be mapped to predefined typessafe lambdas that define which columns to return and then the expression is created at runtime. So a hard coded version of this technique is this:

var channelColumns = new SelectList<Channel>();
channelColumns.Add(c => c.ChanLanguagestreamlocking);
channelColumns.Add(c => c.ChanMinimumtrailerduration);

var channelQuery = 
    ds.Channels.Select(channelColumns.ToDynamicColumns());
var bar = query.ToArray();

i.e. i can generate dynamic queries from the 'root' concept, but how do i generate the nested data.

If i do the obvious i.e. this

var audioColumns = new SelectList<Channelaudiolanguagelink>();
audioColumns.Add(a => a.ObjDatecreated);
var channelColumns = new SelectList<Channel>();
channelColumns.Add(c => c.ChanLanguagestreamlocking);
channelColumns.Add(c => c.ChanMinimumtrailerduration);
// next line causes an error
// Error CS1929  'ICollection<Channelaudiolanguagelink>' does not contain a definition for
// 'Select' and the best extension method overload
// 'Queryable.Select<Channel, object[]>(IQueryable<Channel>, Expression<Func<Channel, object[]>>)' requires a receiver of type 'IQueryable<Channel>' CSharpDb2Raw    C:\Users\mark.nicholls\source\repos\scaffold2\CSharpDb2Raw\Program.cs   57  Active
channelColumns.Add(c => c.Channelaudiolanguagelinks.Select(channelColumns.ToDynamicColumns()));
var channelQuery = 
    ds.Channels.Select(channelColumns.ToDynamicColumns());
var bar = query.ToArray();

the error makes perfect sense.

c.Channelaudiolanguagelinks is an ICollection and so the select is looking for a Func<T,U> and I've given it an Expression<Func<T,U>>

(I don't really understand Expressions!)

Comments are not for extended discussion; this conversation has been moved to chat. If someone notes that your question is unclear, makes a suggestion for improvement, or asks for additional information, do not reply in comments; instead, edit your question. — Cody Gray
– Cody Gray ♦, Commented Apr 7, 2022 at 9:16

Svyatoslav Danyliv · Accepted Answer · 2022-04-07 09:19:34Z

1

Problem that you have defined method for IQueryable version of Select, so basic solution is simple - transform IEnumerable to IQueryable.

channelColumns.Add(c => 
   c.Channelaudiolanguagelinks.AsQueryable().Select(audioColumns.ToDynamicColumns())
);

answered Apr 7, 2022 at 9:19

Svyatoslav Danyliv

28.2k4 gold badges24 silver badges46 bronze badges

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

1 Comment

MrD at KookerellaLtd Over a year ago

thanks v much....stupid me, in a way..but theres some magic with expressions and queryable I'm not used to. Works a treat

Collectives™ on Stack Overflow

Dynamically generating nested queries

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related