C# Linq-to-SQL: Refectoring this Generic GetByID method

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 cant 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 "db 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, butI'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.Pro
        

            From stackoverflow
        
    

• What if you rework this to use GetTable().Where(...), and put your filtering there?

  That would be more efficient, since the Where extension method should take care of your filtering better than fetching the entire table into a list.

  Andreas Grech : see my update in the answer

• Some thoughts...

  Just remove the ToList() call, SingleOrDefault works with an IEnumerably which I presume table is.

  Cache the call to e.GetType().GetProperties().First() to get the PropertyInfo returned.

  Cant you just add a constraint to T that would force them to implement an interface that exposes the Id property?

• Maybe executing a query might be a good idea.

  public static T GetByID(int id)
      {
          Type type = typeof(T);
          //get table name
          var att = type.GetCustomAttributes(typeof(TableAttribute), false).FirstOrDefault();
          string tablename = att == null ? "" : ((TableAttribute)att).Name;
          //make a query
          if (string.IsNullOrEmpty(tablename))
              return null;
          else
          {
              string query = string.Format("Select * from {0} where {1} = {2}", new object[] { tablename, "ID", id });
  
              //and execute
              return dbcontext.ExecuteQuery<T>(query).FirstOrDefault();
          }
      }
  

  Andreas Grech : The column name varies from table to table

  Misha N. : OK, I haven't get that primary key columns names can vary. I see that there is already a solution to get the primary key column name. Regards

• I have brought great shame upon myself. I should've tested an actual run instead of just posting code willy-nilly.

  Denis Troller : no, this will not work because IHasID is not mapped to the database. It will throw an exception saying IHasID.ID has not SQL equivalent.

  Andreas Grech : Yes, that's exactly my problem. Any ideas on how to fix this issue Denis?

  Denis Troller : see my answer above

• What you need is to build an expression tree that LinqToSQL 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 LinqToSQL 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 tell 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 PK name, assuming there is only one (not a composite PK), and assuming all is well type-wise (that is, your pK 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;
              }
  

  Andreas Grech : And what is the solution to overcome different field names?

  Andreas Grech : as in automatically, not specifying them in an abstract parameter from the sub class or anything that requires further maintenance

  Denis Troller : you could try to extract it from the DataContext's MappingSource I think. Let me see...

  Andreas Grech : Found the solution! I'm using his GetPrimaryKey() extension method he has in his MVc open source website. Will post the solution in a bit

  Denis Troller : I did not see it in his post, so I got rid of it. Here's a possible implementation.

  Denis Troller : also, you really should build some caching around that, so that you don't go around querying for the PK each time you call, but that is left as an exercise to the reader (or is left out for the sake of brevity, peek your favorite disclaimer)

  Andreas Grech : I download the Suket.Shop (mvs website) he has and searched in his files for that GetPrimaryKey function heh.

  Andreas Grech : ...and now I have quite a lot of work to do to fully understanding his methods, as regards Expression Trees and Funcs because I haven't yet really delved deeped inside those.

  Marc Gravell : For info, until .NET 4.0 ships, there are very good reasons (with LINQ-to-SQL) to use Single(pred) rather than Where(pred).Single() - it avoids a round-trip to the server for primary key fetches (where it has already seen the object).

  Denis Troller : Thanks for the info. My VB habits show, I very rarely use the method form of queries because it is so much easier in query form in VB :)

• System.NotSupportedException: The member 'MusicRepo_DataContext.IHasID.ID' has no supported translation to SQL.

  Hi, the simple workaround to your initial problem is to specify an Expression. See below, works like a charm for me.

  public interface IHasID
  {
      int ID { get; set; }
  }
  DataContext [View Code]:
  
  namespace MusicRepo_DataContext
  {
      partial class Artist : IHasID
      {
          [Column(Name = "ArtistID", Expression = "ArtistID")]
          public int ID
          {
              get { return ArtistID; }
              set { throw new System.NotImplementedException(); }
          }
      }
  }
  

  Cheers, Dan