Construct Query with Linq rather than SQL strings

Question

I've got a 2.0 server control that uses a dynamic query roughly in the form:

string sql = "Select " + columnvariable + " FROM " + tablenamevariable

So, obviously, you could give it any valid column name from any valid table in the DB and it would return the values in the column into a DataReader (in this case).

I'm trying to cut down on the amount of explicit piecemeal SQL in the codebase and would prefer to do this in LINQ. Is there an easy way to do this? Is it even advisable? I suppose that the resulting piecemeal SQL in this case would be so generic as to not really pose a security problem in this instance.

Even so, it seems like some fairly basic functionality so I'm curious. I've got as far as including System.Linq.Dynamic in my project but this seems to stop shy of allowing a programmer to dynamically select which table they want a dynamic column from.

I don't mean this to be a discussion. I'd like an answer like "Yes, this is possible and trivial, here's how..." or "Yes, but only if you construct this elaborate set of handler classes and basically rewrite parts of LINQ, here's how..."

I would, however, be interested to know whether people think doing this kind of thing in LINQ could best be described as a) a jolly good idea or b) crazy talk.

Answer 1

This entry in Rick Strahl's blog seems to be talking some kind of sense on this topic:

http://www.west-wind.com/Weblog/posts/314663.aspx

In the absence of anything even clearer I think I shall make my choices based on that.

Any other potential answerers should be thinking about whether their potential answer is any clearer than the above link. If not, then it would probably be a waste of your time getting involved.

EDIT: Although this is linked to in the above mentioned article this:

http://www.west-wind.com/WebLog/ShowPost.aspx?id=134706

clarifies my issue perfectly and is, in fact, a sort of prequel to the other article. Fascinating stuff.

Answer 2

Here's an example from good ol' Scott Gu(thrie):

Dynamic LINQ, Part 1

Answer 3

Your query:

string sql = "Select " + columnvariable + " FROM " + tablenamevariable

can be 'translated' with LINQ like this:

var sql = (from tablenamevariable select columnvariable).ToList();

and now the 'sql' variable contain the list of 'columnvariable' stored in 'tablenamevariable'.

You can make it much simple with LINQ.

Answer 4

You are going about this from the wrong angle. You think you want this:

void DoStuff(tablenamevariable, columnvariable)
{
    string sql = "Select " + columnvariable + " FROM " + tablenamevariable 
    // read data 
}


DoStuff("MyTable", "MyColumn");

By why that interface? What wrong with this:

void DoStuff<T>(IQueryable<T> query)
{
      // read data
}


DoStuff(from t in MyTable select t.MyColumn);

This still defines the table & column in the calling function, but with better type safety & flexibility. For example, you could now include a Where clause if needed.

DoStuff(from t in MyTable where t.OtherColumn == 5 select t.MyColumn);

Answer 5

Unfortunately your question doesn’t clarify where exactly the columnvariable comes from, but if I’m allowed to make the following assumptions, then I think I can provide you with a near-trivial solution:

• Assumption 1: The columnvariable is a value that is passed around through method calls, but ultimately it is a constant. I.e. it is not a user-provided string, it is not read from a file, etc.

• Assumption 2: You have access to all the code that passes in those constants for a columnvariable.

If these assumptions hold, then my recommendation is to change your method that contains this:

string sql = "Select " + columnvariable + " FROM " + tablenamevariable

into something more like this:

public IQueryable<Customer> GetCustomers<T>(Expression<Func<Customer, T>> column)
{
    return db.Customers.Select(column);
}

and then instead of

// Assuming "CustomerName" is the name of a column in the Customers table
var myCustomers = GetCustomers("CustomerName");

you would say

// Properly type-safe and compile-time checked
var myCustomers = GetCustomers(c => c.CustomerName);

Answer 6

Since you were looking for a straightforward answer ... No, this is not possible in LINQ.

LINQ is inherently strongly typed, while you're looking for something entirely dynamic. If your table and column are runtime variables (i.e. strings) then your SQL will need to be a string ... or you would need to use an ORM (NHibernate, L2S, EF, etc) to access the data.

Answer 7

If you need this to be completely dynamic, i.e. you could have any string for the table name and the column name, then I suspect your only option is to construct an expression tree manually. Something like...

public IQueryable<TResult> GetColumn<TResult>(string tableName, string columnName)
{
    // Get the table, e.g. for "Customers" get db.Customers
    var table = db.GetType().GetField(tableName).GetValue(db);

    // Find the specific type parameter (the T in IQueryable<T>)
    var iqueryableT = table.GetType().FindInterfaces((ty, obj) => ty.IsGenericType && ty.GetGenericTypeDefinition() == typeof(IQueryable<>), null).FirstOrDefault();
    var type = iqueryableT.GetGenericArguments()[0];

    // Construct a query that retrieves the relevant column
    var param = Expression.Parameter(type);
    var prop = Expression.Property(param, columnName);
    var expression = Expression.Lambda(prop, param);

    // Call Queryable.Select() with the appropriate parameters
    // Notice there are two Select methods, need to get the right one...
    return (IQueryable<TResult>) typeof(Queryable).GetMethods(BindingFlags.Static | BindingFlags.Public)
        .Where(meth => meth.Name == "Select")
        .Select(meth => meth.MakeGenericMethod(type, typeof(TResult)))
        .Where(meth => meth.GetParameters()[1].ParameterType == typeof(Expression<>).MakeGenericType(typeof(Func<,>).MakeGenericType(type, typeof(TResult))))
        .First()
        .Invoke(null, new object[] { table, expression });
}

And then you can call it like this:

var customerNames = GetColumn<string>("Customers", "CustomerName");

Of course you still need to know the type of the column because you want it to return a properly strongly-typed queryable. Alternatively, you could add an Expression.Convert if you want it to automatically convert all data to strings; then you don’t need to specify the string every time, but of course your column data will be converted to strings potentially lossily.