How to create a very dynamic LinqToEntity query?

 or

 or

This selection will is available in another list:

E.g for selection 3:

viewInfo["Table_A"] contains "Name"

viewInfo["Table_B"] contains "Name", "Desc"

viewInfo["Table_C"] contains "Name"

How do I dynamic create a query just using the needed tables and fields to get the desired result?

I have done the same thing for a project I'm working on where the query is completely created at runtime based on selections made in the UI by a user.

I construct the LINQ queries using expression trees by using the classes in the System.Linq.Expressions namespace. It's very powerful but has a steep learning curve.

You can use LINQPad to write queries and then dump the expressions to see what the tree looks like underneath so that you know how to construct the queries yourself.

For example, running the following code in LINQPad will generate a dump of the expression tree.

var query = from p in Puzzles
select p;

query.Expression.Dump(20);

So how does one actually write code that dynamically creates a simple LINQ query?

Consider the following example which is simplest of queries:

var query = from person in data
   select person;

The following code will generate an equivalent query on the fly.

using System;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;

namespace TestLinqGenerator
{
    class Program
    {
        static void Main(string[] args)
        {
            // Set up dummy data
            var data = new[]
                           {
                               new {Name = "Fred"},
                               new {Name = "Simon"}
                           }.AsQueryable();
            var dataType = data.ElementType;

            // IQueryable: data
            var source = Expression.Constant(data);

            // Parameter: person
            var parameter = Expression.Parameter(dataType, "person");

            // person => person
            var lambda = Expression.Lambda(parameter, parameter);

            // Expression: data.Select(person => person)
            var callSelect = Expression.Call(GetSelect().MakeGenericMethod(dataType, dataType), source, Expression.Quote(lambda));

            // IQueryable: data.Select(person => person)
            var query = data.Provider.CreateQuery(callSelect);

            // Execute query
            var results = query.Cast<object>().ToList();

        }

        private static MethodInfo GetSelect()
        {
            // Get MethodInfo of the following method from System.Linq.Queryable:
            // public static IQueryable<TSource> Select<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, bool>> predicate)
            return typeof(System.Linq.Queryable).GetMethods().Where(
                method => method.Name == "Select" && method.GetParameters().Length == 2 &&
                          method.GetParameters()[1].ParameterType.GetGenericArguments()[0].Name == typeof(Func<,>).Name).Single();
        }

    }
}

You should be able to run this code by pasting it into a console application. Step through with the debugger to understand what each step does.

Extra Info

Looking at the implementation of Queryable.Select using Reflector can be helpful in understanding what needs to happen when writing a query dynamically. I've copied it below:

public static IQueryable<TResult> Select<TSource, TResult>(this IQueryable<TSource> source, Expression<Func<TSource, int, TResult>> selector)
{
    if (source == null)
    {
        throw Error.ArgumentNull("source");
    }
    if (selector == null)
    {
        throw Error.ArgumentNull("selector");
    }
    return source.Provider.CreateQuery<TResult>(Expression.Call(null, ((MethodInfo) MethodBase.GetCurrentMethod()).MakeGenericMethod(new Type[] { typeof(TSource), typeof(TResult) }), new Expression[] { source.Expression, Expression.Quote(selector) }));
}

Interestingly, the implementation of Queryable.Select simply creates a LINQ Expression representation of calling itself. The LINQ provider actually translates that expression into something else - TSQL. The Select method itself doesn't actually perform the select.

Your code should do the same thing - create LINQ expressions.

Once you are comfortable with how to do a simple select, you can look at adding Queryable.Where to the mix and other features of a LINQ query. I suggest leaving projections (select new {x, y, z} etc) to last because they are quite difficult. You will need to generate types at runtime in much the same way as the compiler generates anonymous types for you. System.Reflection.Emit is your tool for the job.

One of the nice things about this approach is that you can use it with any LINQ provider, such as LINQ to Entities, LINQ to SQL, Mindscape Lightspeed and the in-memory LINQ provider implementation provided by AsQueryable.

My code that generates LINQ expressions will accept an IQueryable and at runtime this is currently supplied with the Mindscape Lightspeed IQueryables, but could also be one of the others. Then in my unit tests I create test data using arrays of objects and then turn that into an IQueryable using AsQueryable which is passed into the LINQ expression generator. My unit tests can then generate all ranges of complex queries but can be easily tested without requiring a database. The sample above shows how this can be done.

There is a project called Dynamic LINQ that can help you construct queries dynamically. I think you should take a look at this project.

Other than that, it is also possible to create queries in parts by querying a LINQ query. You can put conditional statements in your code and if some branch is followed then you can create a new query from an existing query by querying it again. The query is not executed until you request the results so performance-wise it doesn't matter much if you build up the query in small pieces or make one huge query from the beginning. Using this technique you can (based on the values of the inputs) build up structurally different queries that share some common parts whilst having the benefits of static typing and intellisense.

I solved my problem using the very interesting framework NLinq found on Codeplex. You just have to build a string containing your "normal" Linq query!

Citation from prject description:

NLinq is a framework focusing on reimplementing the Linq functionnalities in Visual Studio .Net 2003 and Visual Studio 2005 (C# & VB .Net) by providing a Linq grammar parser and a "Linq To Objects" execution environment. With NLinq you can take advantage of major C# 3.0 features right now, without requiring it.

Example:

Data sources used for the samples
        Person[] people = new Person[] { 
            new Person("Bill", 31), 
            new Person("John", 30), 
            new Person("Cindy", 25), 
            new Person("Sue", 29) 
        };

        // For testing physical links
        people[0].Friends.Add(people[0]);
        people[0].Friends.Add(people[1]);
        people[1].Friends.Add(people[2]);
        people[2].Friends.Add(people[3]);
        people[3].Friends.Add(people[0]);

        // For testing logical links
        Address[] addresses = new Address[] {
            new Address("Bill", "Redmon"),
            new Address("Bill", "Boston"),
            new Address("Cindy", "New York")
        };

Projections query = new NLinqQuery(
                @"  from c in people 
                    from d in people
                    where c.Age > d.Age
                    select new NLinq.Play.Person ( c.Firstname, d.Age )");

        linq = new LinqToMemory(query);
        linq.AddSource("people", people);


Result:
Sue (25)
John (25)
John (29)
Bill (30)
Bill (25)
Bill (29)