C# Linq to SQL: Refactoring This Generic Getbyid Method

C# LINQ to SQL: Refactoring this Generic GetByID method

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;
}

generic repository EF4 CTP5 getById

The most basic approach is simply

public T GetById(params object[] keys)
{
  _set.Find(keys);
}

If you know that all your entities have primary key called Id (it doesn't have to be called Id in DB but it must be mapped to property Id) of defined type you can use simply this:

public interface IEntity
{
  int Id { get; }
}

public class BaseRepository<T> where T : class, IEntity
{
  ...

  public T GetById(int id)
  {
    _set.Find(id);
  }
}

If data type is not always the same you can use:

public interface IEntity<TKey>
{
  TKey Id { get; }
}

public class BaseRepository<TEntity, TKey> where TEntity : class, IEntity<TKey>
{
  ...

  public TEntity GetById(TKey id)
  {
    _set.Find(id);
  }
}

You can also simply use:

public class BaseRepository<TEntity, TKey> where TEntity : class
{
  ...

  public TEntity GetById(TKey id)
  {
    _set.Find(id);
  }
}

How to make a Type variable work with Linq to SQL?

If you only want a generic method for getting the Id property, you could change

where DataBaseTable : class

To be something like

where DataBaseTable : IEntity

Where IEntity is an interface with an Id property on it which all of your entities could implement.

The reason you get the error you do is because it is trying to convert the reflection methods into SQL, which doesn't make any sense in SQL as there are no 'methods' on tables.

Determine primary key using LINQ to SQL

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

Why i can't make this generic repository to work ? What wrong?

In your repository declaration

public abstract class RepositoryBase<TEntity, TKey> : IRepository<TEntity>, IDisposable
    where TEntity : EntityBase<TKey>
    where TKey : class
{

you have specified the class constraint which will only allows reference types. See

http://msdn.microsoft.com/en-us/library/d5x73970%28v=vs.80%29.aspx

where T : class

The type argument must be a reference type, including any class,
interface, delegate, or array type. (See note below.)

Remove the : class constraint to allow any type.

Unless you're engaged in a learning exercise, I would not try to build your repository from scratch. I would leverage off what others have done. When I wrote a repository framework, I wanted a GetById method that would work with primary keys of varying types (although not multiple column primary keys). When writing it, I found the following two posts especially helpful:

C# LINQ to SQL: Refactoring this Generic GetByID method

http://goneale.com/2009/07/27/linq-to-sql-generic-repository/

Linq Select by PK?

You could try writing an extension method onto your DataContext object that facilitates the pulling of records from the database by their Primary Key, as outlined by Chris Sainty in his blog post LINQ to SQL: Generic Primary Key function :

public static class DataContextHelpers
{
    public static T GetByPk<T>(this DataContext context, object pk) where T : class {
        var table = context.GetTable<T>();
        var mapping = context.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(pk)), param);
        return table.SingleOrDefault(predicate);
    }
}

Then call the method by way of:

MyDataContext db = new MyDataContext();
Product p = db.GetByPk<Product>(1);

You might also want to have a look at Denis Troller's answer as an alternative.

DataGridView: Determine SQL Key Values or Relationships?

The DataTable has a property PrimaryKey which is a collection of DataColumns.

If you set the CommandBehavior parameter when you call ExecuteReader to KeyInfo then you can retrieve the primary key column information from the primary key property.

table.Load(cmd.ExecuteReader(CommandBehavior.KeyInfo));

Using the ColumnName property of each column in the collection you can then set the Readonly property of your DataGridView columns.

foreach (DataColumn d in dt.PrimaryKey)
{
    if (dataGridView1.Columns[d.ColumnName] != null)
    {
        dataGridView1.Columns[d.ColumnName].ReadOnly = true;
    }
}

---

There are also other useful values for CommandBehaviour, such as SchemaOnly, that you can combine as shown:

table.Load(cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehaviour.SchemaOnly));

---

Related Topics