Using Field<> with Linq to SQL

Question

I'm trying to pass a column name to a class and retrieve a value for that column from a sql database. Looks like Field<> is not available for a Table, but it will work with DataTable. Can someone point me how can i do it using linq? I'm sure it is a very simple thing. Thanks.

[Table(Name = "[Keys]")]
public class Keys
{
    [Column]
    public string Column_A{ get; set; }
    [Column]
    public string Column_B{ get; set; }
    [Column]
    public string Column_C{ get; set; }
}

    public string ReadKey(string DBKey)
{
    DataContext dc = new DataContext(sqlconn);
    Table<Keys> keysTable = dc.GetTable<Keys>();

    var query = from k in keysTable.AsEnumerable()
    select k.Field<string>("DBKey");     <---------------- wrong

}

score 3 · Answer 1 · edited May 23 '17 at 09:59

The Field<> is for LINQ to DataTables, and is included as an extension to System.Data. It's not a standard LINQ extension method. See this SO question on how to use LINQ to DataTables.

What you're trying to do can be accomplished by using an Expression, or Dynamic LINQ. Dynamic LINQ is not part of what's shipped with .NET; it was included as an example in VS2008 and people have continued to use it. I'm not sure if it's supported, updated, etc. by Microsoft. I wouldn't count on it until it's incorporated into the framework.

You could create a method create the selector mentioned in Jon's answer as such:

public Expression<Func<Keys,T>> GetSelectLambda<T>(string propertyName)
{
    ParameterExpression lhsParam = Expression.Parameter(typeof(Keys), "s");
    Expression fieldParam = Expression.Property(lhsParam, propertyName);
    var theExpression = Expression.Lambda<Func<Keys, T>>(fieldParam, lhsParam);
    return theExpression;
}

So passing in GetSelectLambda<string>("DBKey") should generate a lambda of s => s.DBKey, which is then returned and passed into Jon's method.

Hope this helps.

score 2 · Answer 2 · answered Aug 14 '11 at 16:59

You generally shouldn't use a column name with LINQ to SQL - one of the benefit of LINQ to SQL over using untyped DataTable objects is that you get more compile-time safety.

If you need to allow different columns to be selected, you can use an expression tree to select them:

public string ReadKey(Expression<Func<Keys, string>> selector)
{
    DataContext dc = new DataContext(sqlconn);
    Table<Keys> keysTable = dc.GetTable<Keys>();

    // Generally speaking you don't want to use AsEnumerable here...
    var query = keysTable.Select(selector);
    // Now do something with query
}

Note that using GetTable is rarely a great idea - normally you'd work with a specific table on a specific DataContext.

Using Field<> with Linq to SQL

2 Answers2