Using reflection to address a Linqed property

Question

I'm trying to writing a generic method that will load a record of a specific type, with a specific ID. Here's one way that works:

    public abstract class LinqedTable<T> where T : LinqableTable {
 public static T Get(long ID) {
  DataContext context = LinqUtils.GetDataContext<T>();
  var q = from obj in context.GetTable<T>()
      where obj.ID == ID
      select obj;
  return q.Single<T>();
 }
}

public abstract class LinqableTable {
 public abstract long ID { get; set; }
}

You can ignore the call to LinqUtils.GetDataContext<T>(); that's a utility function I've got to deal with the fact that I have multiple data contexts in my program. The point is that now I can declare any of my classes as subclasses of LinqableTable, and I can easily instantiate a record of that table just by calling LinqedTable<MyType>.Get(ID).

This has some limitations, however. Firstly, it forces all of my tables to have an I identity field of type long, named ID. Secondly, because I'm using an abstract method, I am forced to go to the O/R designer and change the inheritance property of every ID field in my system to "override".

I want more flexibility than that. So naturally, I tried reflection, and came out with the following:

    public abstract class LinqedTable<T> where T : LinqableTable {
 public static T Get(long ID) {
  DataContext context = LinqUtils.GetDataContext<T>();
  var q = from obj in context.GetTable<T>()
      where obj.IDValue == ID
      select obj;
  return q.Single<T>();
 }
}

public abstract class LinqableTable {
 internal long IDValue {
  get { return (long)IDProperty.GetValue(this, null); }
  set { IDProperty.SetValue(this, value, null); }
 }
 internal PropertyInfo IDProperty {
  get { return this.GetType().GetProperty(IDPropertyName); }
 }
 internal protected virtual string IDPropertyName {
  get { return "ID"; }
 }
}

Theoretically, this allows me to override the ID column name, the cast to long should be OK with any integral data type, and I don't need to go defining all my ID columns as overrides.

BUT

Linq doesn't like this. On the call to q.Single<T>(); I get a runtime error:

The member 'EISS.Utils.LinqableTable.IDValue' has no supported translation to SQL.

OK, today I learned that Linq does some kind of magic on the back end; it doesn't instantiate obj and just read the IDValue property. So must be there's some attribute that needs to be set on the IDValue property that lets Linq do its thing.

But what?

Answer 1

Since LINQ statements referred to a LINQ-to-SQL IQueryable are translated to SQL queries, you will have to use the AsEnumerable extension (which will in turn cause a read of all the items in the database) and do reflection-related stuff on that IEnumerable.
EDIT
As required here's a clarification
As specified in a comment, what I meant was something like:

 (from obj in context.GetTable<T>() select obj).AsEnumerable().Where(x => x.IDValue == ID)

Unlike a query executed on an IQueryable, which can be perfectly translated to SQL such as

context.GetTable().Where(x => x.Text == "Hello")

which gets converted to something similar to

SELECT * FROM TABLE_MAPPED_TO_TYPE_T WHERE Text = 'Hello'

a query executed against an IEnumerable - in your case - will be executed by fetching all the entries of your table and then applying code-wise the specified filter.

Answer 2

Linq to SQL tries to translate your linq-query into SQL, but it does not know how to translate your property to a column name in the DB.

A good explanation can be found here on SO: http://stackoverflow.com/questions/332670/simple-linq-to-sql-has-no-supported-translation-to-sql

But how to solve it, is another matter. I have with succes used the apporoach from this thread:

http://social.msdn.microsoft.com/forums/en-US/linqprojectgeneral/thread/df9dba6e-4615-478d-9d8a-9fd80c941ea2/

Or you can use dynamic query as mentioned here by scott guthrie:

http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

Answer 3

Having read these posts: Generic Data Access using LINQ to SQL and C#, LINQ-to-SQL: Generic Primary Key function and Calling a generic method with Type

My colleague and I came up with the following digest:

We added the following method to our datacontext (in a partial class).

public T GetInstanceByPrimaryKey<T>(object primaryKeyValue) where T : class
{
    var table = this.GetTable<T>();
    var mapping = this.Mapping.GetTable(typeof(T));
    var pkfield = mapping.RowType.DataMembers.SingleOrDefault(d => d.IsPrimaryKey);

    if (pkfield == null)
        throw new Exception(String.Format("Table {0} does not contain a Primary Key field", mapping.TableName));

    var param = Expression.Parameter(typeof(T), "e");

    var predicate =
        Expression.Lambda<Func<T, bool>>(Expression.Equal(Expression.Property(param, pkfield.Name), Expression.Constant(primaryKeyValue)), param);

    return table.SingleOrDefault(predicate);
}

Then, where we need to instanciate from the type name and primary key value:

string name = "LinqObjectName";
int primaryKey = 123;

var dc = new YourDataContext();

Type dcType = dc.GetType();
Type type = dcType.Assembly.GetType(String.Format("{0}.{1}", dcType.Namespace, name));

MethodInfo methodInfoOfMethodToExcute = dc.GetType().GetMethod("GetInstanceByPrimaryKey");
MethodInfo methodInfoOfTypeToGet = methodInfoOfMethodToExcute.MakeGenericMethod(name);
var instance = methodInfoOfTypeToGet.Invoke(dc, new object[] { primaryKey });

return instance;

Hope this helps!