C# LINQ to SQL: Refactoring this Generic GetByID method

c#, expression-trees, generics, linq-to-sql

Solution

What you need is to build an expression tree that LINQ to SQL can understand. Assuming your "id" property is always named "id":

public virtual T GetById<T>(short id)
{
    var itemParameter = Expression.Parameter(typeof(T), "item");
    var whereExpression = Expression.Lambda<Func<T, bool>>
        (
        Expression.Equal(
            Expression.Property(
                itemParameter,
                "id"
                ),
            Expression.Constant(id)
            ),
        new[] { itemParameter }
        );
    var table = DB.GetTable<T>();
    return table.Where(whereExpression).Single();
}

This should do the trick. It was shamelessly borrowed from this blog. This is basically what LINQ to SQL does when you write a query like

var Q = from t in Context.GetTable<T)()
        where t.id == id
        select t;

You just do the work for LTS because the compiler cannot create that for you, since nothing can enforce that T has an "id" property, and you cannot map an arbitrary "id" property from an interface to the database.

==== UPDATE ====

OK, here's a simple implementation for finding the primary key name, assuming there is only one (not a composite primary key), and assuming all is well type-wise (that is, your primary key is compatible with the "short" type you use in the GetById function):

public virtual T GetById<T>(short id)
{
    var itemParameter = Expression.Parameter(typeof(T), "item");
    var whereExpression = Expression.Lambda<Func<T, bool>>
        (
        Expression.Equal(
            Expression.Property(
                itemParameter,
                GetPrimaryKeyName<T>()
                ),
            Expression.Constant(id)
            ),
        new[] { itemParameter }
        );
    var table = DB.GetTable<T>();
    return table.Where(whereExpression).Single();
}


public string GetPrimaryKeyName<T>()
{
    var type = Mapping.GetMetaType(typeof(T));

    var PK = (from m in type.DataMembers
              where m.IsPrimaryKey
              select m).Single();
    return PK.Name;
}

Problem

I wrote the following method. ``` public T GetByID(int id) { var dbcontext = DB; var table = dbcontext.GetTable<T>(); return table.ToList().SingleOrDefault(e => Convert.ToInt16(e.GetType().GetProperties().First().GetValue(e, null)) == id); } ``` Basically it's a method in a Generic class where `T` is a class in a DataContext. The method gets the table from the type of T (`GetTable`) and checks for the first property (always being the ID) to the inputted parameter. The problem with this is I had to convert the table of elements to a list first to execute a `GetType` on the property, but this is not very convenient because all the elements of the table have to be enumerated and converted to a `List`. How can I refactor this method to avoid a `ToList` on the whole table? [Update] The reason I can't execute the `Where` directly on the table is because I receive this exception: Method 'System.Reflection.PropertyInfo[] GetProperties()' has no supported translation to SQL. Because `GetProperties` can't be translated to SQL. [Update] Some people have suggested using an interface for T, but the problem is that the `T` parameter will be a class that is auto generated in [DataContextName].designer.cs, and thus I cannot make it implement an interface (and it's not feasible implementing the interfaces for all these "database classes" of LINQ; and also, the file will be regenerated once I add new tables to the DataContext, thus loosing all the written data). So, there has to be a better way to do this... [Update] I have now implemented my code like Neil Williams' suggestion, but I'm still having problems. Here are excerpts of the code: Interface: ``` public interface IHasID { int ID { get; set; } } ``` DataContext [View Code]: ``` namespace MusicRepo_DataContext { partial class Artist : IHasID { public int ID { get { return ArtistID; } set { throw new System.NotImplementedException(); } } } } ``` Generic Method: ``` public class DBAccess<T> where T : class, IHasID,new() { public T GetByID(int id) { var dbcontext = DB; var table = dbcontext.GetTable<T>(); return table.SingleOrDefault(e => e.ID.Equals(id)); } } ``` The exception is being thrown on this line: `return table.SingleOrDefault(e => e.ID.Equals(id));` and the exception is: `System.NotSupportedException: The member 'MusicRepo_DataContext.IHasID.ID' has no supported translation to SQL.` [Update] Solution: With the help of Denis Troller's posted answer and the link to the post at the Code Rant blog, I finally managed to find a solution: ``` public static PropertyInfo GetPrimaryKey(this Type entityType) { foreach (PropertyInfo property in entityType.GetProperties()) { ColumnAttribute[] attributes = (ColumnAttribute[])property.GetCustomAttributes(typeof(ColumnAttribute), true); if (attributes.Length == 1) { ColumnAttribute columnAttribute = attributes[0]; if (columnAttribute.IsPrimaryKey) { if (property.PropertyType != typeof(int)) { throw new ApplicationException(string.Format("Primary key, '{0}', of type '{1}' is not int", property.Name, entityType)); } return property; } } } throw new ApplicationException(string.Format("No primary key defined for type {0}", entityType.Name)); } public T GetByID(int id) { var dbcontext = DB; var itemParameter = Expression.Parameter(typeof (T), "item"); var whereExpression = Expression.Lambda<Func<T, bool>> ( Expression.Equal( Expression.Property( itemParameter, typeof (T).GetPrimaryKey().Name ), Expression.Constant(id) ), new[] {itemParameter} ); return dbcontext.GetTable<T>().Where(whereExpression).Single(); } ```

Original source