How do I do a dynamic Linq2SQL Query?

Question

I'm trying to build a method that will receive a Linq table, and should return a List<> of values that will be a DropDownList Datasource.

This is what I've got till now:

public static List<Structs.NameValuePair> GenDropDownItens<T>(string ValueField , string TextField ) where T: class

What i don't know how to do is, query the table getting only the fields that are passed ( ValueField, TextField)...

Tks!

Answer 1

Table.Select( t => t.field1, t.field2 )

Also check out Scott Gutherie's blog series here.

Answer 2

Why not just do something like;

var dropDownValues = dataContext.SomeTable.ToDictionary(
    s => s.Name,
    s => s.Value
);

foreach(var item in dropDownValues) {
    var OptionName = item.Key;
    var OptionValue = item.Value
};

Hope this helps, I really don't think you need to create a while method. But if you wanted to I would say have it take a IDictionary object, and convert it from there.

Answer 3

Are you trying to do something like the following with your method

GetDropDownItems("Gates", "LastName") ????

If so, included as part of the SDK samples is a project called DynamicQuery. Using this you can basically create a text version of the query you want. You could do something like

"LastName == 'Gates'"

However, it is just as easy to build the expression tree yourself. The best way to learn what the expressions tree's look like is to use the ExpressionTreeVisualizer VS debugger add in (note this is also another sample contained in the SDK CSharpSamples). It would be something like

ParameterExpression parameter = Expression.Parameter(typeof(T), "x"); var expression = Expression.Equals(Expression.Property(parameter, "LastName"), Expression.Constant("Gates")

Answer 4

Project the result of your LINQ2SQL query into a System.Collections.Generic.KeyValuePair object like so:

ddl.DataSource = DataContext.Table.Select(o => new KeyValuePair<string, string>(o.ID, o.DisplayField));
ddl.DataBind();

You will then want to set the DataValueField and DataTextField attributes on the DropDownList to "Key" and "Value" respectively.

Answer 5

If "Key" and "Value" are strings that represent the name of the properties you would like to get, and they are known only at runtime... here's your code:

private static Func<T, DictionaryEntry> GetNameValuePairFunc<T>(string valueField, string textField)
{
    Func<T, DictionaryEntry> result = (item) =>
    {
     object key = typeof(T).GetProperty(valueField).GetValue(item, null);

     object text = typeof(T).GetProperty(textField).GetValue(item, null);

     return new DictionaryEntry(key, text);
    };

    return result;
}