generic GetById for complex PK

Question

I am looking a way to create Generic GetById that get params object[] as parameter, knows to find the key/s field/s and know to find the relevant entity.

In the way to find a solution I thought on a generic method that returns the PK fields definition and a generic method that can return the entity based on fields.

I am looking for something I can use in table with one or more fields as primary key.

EDIT one or more fields as primary key example =
table Customers have (CompanyId, CustomerName, Address, CreateDate).
The primary key of Customers are CompanyId are CustomerName.

I am looking for generic GetById that will know to handle also those such of tables.

Answer 1

You can't get "generic" approach if you don't know how many members is in the key and what types do they have. I modified my solution for single key to multiple keys but as you can see it is not generic - it uses order in which keys are defined:

// Base repository class for entity with any complex key
public abstract class RepositoryBase<TEntity> where TEntity : class
{
    private readonly string _entitySetName;
    private readonly string[] _keyNames;

    protected ObjectContext Context { get; private set; }
    protected ObjectSet<TEntity> ObjectSet { get; private set; }

    protected RepositoryBase(ObjectContext context)
    {
        if (context == null)
        {
            throw new ArgumentNullException("context");
        }

        Context = context;
        ObjectSet = context.CreateObjectSet<TEntity>();

        // Get entity set for current entity type
        var entitySet = ObjectSet.EntitySet;
        // Build full name of entity set for current entity type
        _entitySetName = context.DefaultContainerName + "." + entitySet.Name;
        // Get name of the entity's key properties
        _keyNames = entitySet.ElementType.KeyMembers.Select(k => k.Name).ToArray();
    }

    public virtual TEntity GetByKey(params object[] keys)
    {
        if (keys.Length != _keyNames.Length)
        {
            throw new ArgumentException("Invalid number of key members");
        }

        // Merge key names and values by its order in array
        var keyPairs = _keyNames.Zip(keys, (keyName, keyValue) => 
            new KeyValuePair<string, object>(keyName, keyValue));

        // Build entity key
        var entityKey = new EntityKey(_entitySetName, keyPairs);
        // Query first current state manager and if entity is not found query database!!!
        return (TEntity)Context.GetObjectByKey(entityKey);
    }

    // Rest of repository implementation
}

Answer 2

I don't know how useful this would be because it is generic but you could do this:

public TEntity GetById<TEntity>(params Expression<Func<TEntity, bool>>[] keys) where TEntity : class
{
    if (keys == null)
      return default(TEntity);

    var table = context.CreateObjectSet<TEntity>();
    IQueryable<TEntity> query = null;
    foreach (var item in keys)
    {
        if (query == null)
            query = table.Where(item);
        else
            query = query.Where(item);
    }
    return query.FirstOrDefault();
}

and then you could call it like this:

var result = this.GetById<MyEntity>(a => a.EntityProperty1 == 2, a => a.EntityProperty2 == DateTime.Now);

Disclaimer: this really isn't a GetByid, it's really a "let me give you a couple parameters and give me the first entity that matches". But that being said, it uses generics and it will return an entity if there is a match and you search based on primary keys.

Answer 3

I think you can't implement such thing because you won't be able to join between each passed value with the appropriate key field.

I'd suggest using custom method for each entity:

Assuming Code and Name are keys in Person table:

 public IEnumerable<Person> ReadById(int code, string name)
 {
     using (var entities = new Entities())
        return entities.Persons.Where(p => p.Code = code && p.Name = name);
 }

Answer 4

Ok this is my second stab at it. I think this would work for you.

public static class QueryExtensions
{
    public static Customer GetByKey(this IQueryable<Customer> query, int customerId,string customerName)
    {
        return query.FirstOrDefault(a => a.CustomerId == customerId && a.CustomerName == customerName);
    }

}

So the beauty behind this extension method is you can now do this:

Customer customer = Db.Customers.GetByKey(1,"myname");

You obviously have to do this for every type, but probably worth it if you need it :)

Answer 5

I think the set up is a bit but I do think creating a reusable pattern will pay off in the long run. I just wrote this up and haven't tested, but I based off a searching pattern I use a lot.

Required Interfaces:

public interface IKeyContainer<T>
{
    Expression<Func<T, bool>> GetKey();
}

public interface IGetService<T>
{
    T GetByKey(IKeyContainer<T> key);
}

Example Entities:

public class Foo
{
    public int Id { get; set; }
}

public class ComplexFoo
{
    public int Key1 { get; set; }

    public int Key2 { get; set; }
}

Implementation Example:

public class FooKeyContainer : IKeyContainer<Foo>
{
    private readonly int _id;

    public FooKeyContainer(int id)
    {
        _id = id;
    }

    public Expression<Func<Foo, bool>> GetKey()
    {
        Expression<Func<Foo, bool>> key = x => x.Id == _id;
        return key;
    }
}

public class ComplexFooKeyContainer : IKeyContainer<ComplexFoo>
{
    private readonly int _id;
    private readonly int _id2;

    public ComplexFooKeyContainer(int id, int id2)
    {
        _id = id;
        _id2 = id2;
    }

    public Expression<Func<ComplexFoo, bool>> GetKey()
    {
        Expression<Func<ComplexFoo, bool>> key = x => x.Key1 == _id && x.Key2 == _id2;
        return key;
    }
}

public class ComplexFooService : IGetService<ComplexFoo>
{
    public ComplexFoo GetByKey(IKeyContainer<ComplexFoo> key)
    {
       var entities = new List<ComplexFoo>();            
       return entities.Where(key.GetKey()).FirstOrDefault();
    }
}

Usage:

var complexFoo = ComplexFooService.GetByKey(new ComplexFooKeyContainer(1, 2));