Determine primary key using LINQ to SQL

Question

I'm writing a LINQ to SQL based repository where I want to allow a GetByID with an int parameter. The signature is:

public T GetByID(int id)
{
     // Return
     return _dataContext.GetTable<T>() ....;
}

My tables have different names for the primary key. What I'd like to do is determine dynamically for each T what the primary key is and query it for a value of integer = id. Any ideas how to best pull this off?

Sorry, my answer was no good -- I confused an in-memory list of objects with a `Table`, which queries the database. This question was previously answered here: http://stackoverflow.com/questions/735140/c-linq-to-sql-refectoring-this-generic-getbyid-method — Ben M, Aug 08 '09 at 18:39

score 10 · Answer 1 · answered Aug 08 '09 at 19:23

Something like below (which supports other types than just int, but defaults to int). Importantly, don't fall into the trap of looking at Attribute data via reflection; LINQ-to-SQL supports objects without attributes too:

public static TEntity Get<TEntity>(this DataContext dataContext, int id)
        where TEntity : class
{
    return Get<TEntity, int>(dataContext, id);
}
public static TEntity Get<TEntity, TKey>(this DataContext dataContext, TKey id)
    where TEntity : class
{
    // get the row from the database using the meta-model
    MetaType meta = dataContext.Mapping.GetTable(typeof(TEntity)).RowType;
    if (meta.IdentityMembers.Count != 1) throw new InvalidOperationException(
        "Composite identity not supported");
    string idName = meta.IdentityMembers[0].Member.Name;

    var param = Expression.Parameter(typeof(TEntity), "row");
    var lambda = Expression.Lambda<Func<TEntity, bool>>(
        Expression.Equal(
            Expression.PropertyOrField(param, idName),
            Expression.Constant(id, typeof(TKey))), param);

    return dataContext.GetTable<TEntity>().Single(lambda);
}

This is definitely the best approach, and a good answer besides, since it points out a less well-known feature (runtime metadata) of LINQ to SQL. — Ben M, Aug 09 '09 at 02:31

score 3 · Accepted Answer · edited May 23 '17 at 12:24

3

Dennis Troller answered it to the question that Ben linked to in the comments in the question.

edited May 23 '17 at 12:24

Community

1
1

answered Aug 08 '09 at 18:44

p.campbell

98,673
67
256
322

tvanfosson · Answer 3 · 2011-03-22T02:20:16.083

Personally, I think it would be easier to provide a SingleOrDefault<T> method that takes a Func<int,T> selector argument. Then you can provide whatever selector you wish, including the one that selects based on that table's id.

 public abstract class Repository<T> where T : class
 {
     public abstract T GetById( int id );
     public T SingleOrDefault( Func<int,T> selector )
     {
           return _dataContext.GetTable<T>().SingleOrDefault( selector );
     }
 }

Usage:

 var myObj = repos.SingleOrDefault<MyClass>( c => c.MyClassID == id );

A strongly-typed respository could then use this method to implement GetById()

 public class MyClassRepository : Repository<MyClass>
 {
     public override MyClass GetById( int id )
     {
         return this.SingleOrDefault( c => c.MyClassID == id );
     }
 }

Didn't you forget to actually check the passed in `id` value against the `c.MyClassID`? — GregL, Mar 22 '11 at 01:37

Determine primary key using LINQ to SQL

3 Answers3

Linked