How do I make this Func work with Linq to SQL?

Question

I have an abstract, generic repository class that encapsulates a lot of logic, and it is able to do that because it knows how to isolate a unique data row, thanks to its KeyCompare property, which is injected into the repository's constructor:

Func<T, T, bool> KeyCompare { get; }

The role of this property is to tell the base class what fields to use to fetch a unique record - sometimes it's just "Id" and KeyCompare can be assigned like this (passed to constructor):

(item1, item2) => item1.Id == item2.Id

However it also gives me the flexibility of using Linq to SQL classes that have keys that span several columns, like this:

(item1, item2) => item1.Field1 == item2.Field1 && item1.Field2 == item2.Field2

And this works very well in the unit tests, but only because I'm testing with a mock repo that uses Linq to Object.

Production code uses Linq to SQL and since I've been told my KeyCompare property won't behave as expected (because the Func won't be translated to SQL), that I should be using an Expression instead. So I started googling around (and apparently Func won't be translated to SQL), and saw many examples, but nothing like what I'm doing (is that a sign of something?).

The way I'm using the Func is as follows:

public T Item(T item)
{
    return (_items.SingleOrDefault(e => KeyCompare(e, item));
}

I was told to implement the method like this (with KeyCompare being an Expression):

public T Item(T item)
{
    return (_items.SingleOrDefault(KeyCompare);
}

But this won't compile (.SingleOrDefault wants a Func, not an Expression) and, if it did, it wouldn't be using the item value that I need to give it in order to do what I intend.

I'm injecting this Func into the repo's constructor with Ninject; here's how I bind the KeyCompare constructor argument:

.WithConstructorArgument("keyCompare", (Func<MockEntity, MockEntity, bool>)((item1, item2) => item1.Id == item2.Id));

So I'm stuck. How do I make this work with Linq to SQL?

The alternative, if I drop this KeyCompare property in the base class, is to write redundant code every time I need to implement a concrete repository, which I'd like to avoid. With this KeyCompare property, I only need to write the code that's specific to each repository type to implement a new one, and I like that.

This post has the complete [original - it has changed since] code for the abstract repository class I'm talking about: Can Ninject use an anonymous delegate (func) as a ConstructorArgument?

EDIT

LINQ-to-SQL : Convert Func<T, T, bool> to an Expression<Func<T, T, bool>> has an interesting answer and a pinpoint accurate title (and I really wonder how I could have missed that one in my research). It actually answers this question with an excellent, detailed answer that I'm going to try in a moment.

The usage is now a little weird, since this is a curried lambda. Instead of passing (x,y) => ... you will be passing x => y => ....

I'm sure the solution works (it's quite clever), but it does not explain why passing (x,y) needs a backflip and a triple axel to perform. What's the deal with two inputs? If Linq to Objects can pull this off, what is it exactly that's preventing Linq to SQL from doing the same?

Answer 1

I got all tests to pass - both with the LinqToObject "mock" repo and the LinqToSql "production" repo (the latter being an automated integration test). Here's how:

From the answer on this post: LINQ-to-SQL : Convert Func<T, T, bool> to an Expression<Func<T, T, bool>>, grab the code to create the PartialApplier static class whose role is to rewrite the expression on the fly (from what I grasped).

I then made both ListRepository and DataRepository derive from RepositoryBase which implements IRepository<T>. Here's the repo class:

public abstract class RepositoryBase<T> : IRepository<T>
    where T : class
{
    public abstract IQueryable<T> Select();
    public abstract void Save(T item);
    public abstract void Delete(T item);
    public abstract void Delete(IEnumerable<T> items);

    public virtual Expression<Func<T, T, bool>> KeyCompare { get; private set; }
    public virtual int Count { get { return Select().Count(); } }

    protected RepositoryBase(Expression<Func<T, T, bool>> keyCompare)
    {
        KeyCompare = keyCompare;
    }

    public virtual T Item(T item)
    {
        var applied = PartialApplier.PartialApply(KeyCompare, item);
        var result = (Select().SingleOrDefault(applied));

        return result;
    }

    /// <summary>
    /// A method that updates the non-key fields of an existing entity with those of specified <see cref="item"/>.
    /// Called by the <see cref="Save"/> method.
    /// </summary>
    /// <param name="existing">The existing record to update.</param>
    /// <param name="item">A <see cref="T"/> object containing the values to update <see cref="existing"/> object with.</param>
    /// <returns></returns>
    protected abstract void UpdateExisting(T existing, T item);

    /// <summary>
    /// A method that saves all specified items.
    /// </summary>
    /// <param name="items">The entities to be saved.</param>
    public virtual void Save(IEnumerable<T> items)
    {
        foreach (var item in items)
        {
            Save(item);
        }
    }
}

The ListRepository was no issue, but needed a bit of reworking to properly derive from RepositoryBase:

/// <summary>
/// A non-persistent repository implementation for mocking the repository layer.
/// </summary>
/// <typeparam name="T"></typeparam>
public class ListRepository<T> : RepositoryBase<T>
    where T : class
{
    private readonly List<T> _items = new List<T>();

    public ListRepository(IEnumerable<T> items, Expression<Func<T, T, bool>> keyCompare)
        : base(keyCompare)
    {
        _items.AddRange(items);
    }

    public override IQueryable<T> Select()
    { 
        return _items.AsQueryable(); 
    }

    public override void Save(T item)
    {
        var existing = Item(item);
        if (existing != null)
        {
            _items.Remove(item);
        }

        _items.Add(item);
    }

    public override void Save(IEnumerable<T> items)
    {
        foreach (var item in items)
        {
            Save(item);
        }
    }

    public override void Delete(T item)
    {
        _items.Remove(item);
    }

    public override void Delete(IEnumerable<T> items)
    {
        foreach (var item in items)
        {
            Delete(Item(item));
        }
    }

    /// <summary>
    /// Not implemented.
    /// </summary>
    /// <param name="existing"></param>
    /// <param name="item"></param>
    protected override void UpdateExisting(T existing, T item)
    {
        throw new NotImplementedException(); // list repo just wipes existing data
    }
}

And then the DataRepository could look like this:

public abstract class DataRepository<T> : RepositoryBase<T>
    where T : class
{
    private DataContext _context;

    public DataRepository(DataContext context, Expression<Func<T, T, bool>> keyCompare)
        : base(keyCompare)
    {
        _context = context;
    }

    public override IQueryable<T> Select()
    {
        return _context.GetTable<T>().AsQueryable();
    }

    /// <summary>
    /// A method that updates an existing item or creates a new one, as needed.
    /// </summary>
    /// <param name="item">The entity containing the values to be saved.</param>
    public override void Save(T item)
    {
        var existing = Item(item);
        if (existing != null)
        {
            UpdateExisting(existing, item);
        }
        else
        {
            _context.GetTable<T>().InsertOnSubmit(item);
        }

        _context.SubmitChanges();
    }

    /// <summary>
    /// A method that deletes specified item.
    /// </summary>
    /// <param name="item"></param>
    public override void Delete(T item)
    {
        var existing = Item(item);
        if (existing != null)
        {
            _context.GetTable<T>().DeleteOnSubmit(existing);
        }

        _context.SubmitChanges();
    }

    public override void Delete(IEnumerable<T> items)
    {
        foreach (var item in items)
        {
            Delete(item);
        }
    }

    protected override void UpdateExisting(T existing, T item)
    {
        throw new NotImplementedException(); // must override in derived classes - not obvious I know
    }
}

So, given a DataContext with only a simple object (called DataRepositoryIntegrationTest in this case), we can create an implementation to test it all out:

public class TestRepository : DataRepository<DataRepositoryIntegrationTest>
{
    public TestRepository(DataContext context)
        : base(context,  (item1, item2) => item1.Id == item2.Id)
    { }

    protected override void UpdateExisting(DataRepositoryIntegrationTest existing, DataRepositoryIntegrationTest item)
    {
        existing.Value = item.Value;
        existing.DateUpdated = DateTime.Now;
    }
}

And then the tests can be written:

[TestClass]
public class DataRepositoryTests
{
    private IRepository<DataRepositoryIntegrationTest> _repo;
    private static DataContext _context;

    [ClassCleanup]
    public static void CleanUp()
    {
        _context.Dispose();
    }

    public DataRepositoryTests()
    {
        _context = new DataContext(Settings.Default.IntegrationTestConnectionString);
    }

    [TestInitialize]
    public void InitRepository()
    {
        _context.ExecuteCommand("truncate table dbo.IntegrationTest");
        _context.ExecuteCommand("insert into dbo.IntegrationTest ([Value], [DateInserted]) values ('Test1', getdate())");
        _context.ExecuteCommand("insert into dbo.IntegrationTest ([Value], [DateInserted]) values ('Test2', getdate())");
        _context.ExecuteCommand("insert into dbo.IntegrationTest ([Value], [DateInserted]) values ('Test3', getdate())");
        _repo = new TestRepository(_context);
    }

    private DataRepositoryIntegrationTest CreateItem(string value)
    {
        return new DataRepositoryIntegrationTest { Value = value, DateInserted = DateTime.Now };
    }

    [TestMethod]
    public void ShouldInsertItem()
    {
        // arrange
        var item = CreateItem("Test.ShouldInsert1");
        var expectedCount = _repo.Count + 1;

        // act
        _repo.Save(item);
        var actualCount = _repo.Count;

        // assert
        Assert.AreEqual(expectedCount, actualCount);
    }

    [TestMethod]
    public void ShouldInsertItems()
    {
        // arrange
        var count = _repo.Count;
        var items = new DataRepositoryIntegrationTest[] { 
            CreateItem("Test.ShouldInsert1"),
            CreateItem("Test.ShouldInsert2")};
        var expected = count + items.Length;

        // act
        _repo.Save(items);
        var actual = _repo.Count;

        // assert
        Assert.AreEqual(expected, actual);
    }

    [TestMethod]
    public void ShouldUpdateItem()
    {
        if (_repo.Count == 0) throw new AssertInconclusiveException("ShouldUpdateItem requires an existing item.");

        // arrange
        var item = _repo.Item(new DataRepositoryIntegrationTest { Id = 1 });
        var expected = "updated";
        item.Value = expected;

        // act
        _repo.Save(item);
        var result = _repo.Item(new DataRepositoryIntegrationTest { Id = item.Id });
        var actual = result.Value;

        // assert
        Assert.AreEqual(expected, actual);
    }

    [TestMethod]
    public void ShouldDeleteItem()
    {
        if (_repo.Count == 0) throw new AssertInconclusiveException("ShouldUpdateItem requires an existing item.");

        // arrange
        var item = _repo.Item(new DataRepositoryIntegrationTest { Id = 1 });
        var expected = _repo.Count - 1;

        // act
        _repo.Delete(item);
        var actual = _repo.Count;

        // assert
        Assert.AreEqual(expected, actual);
    }

    [TestMethod]
    public void ShouldDeleteItems()
    {
        if (_repo.Count < 2) throw new AssertInconclusiveException("ShouldUpdateItem requires 2 existing items.");

        // arrange
        var items = new DataRepositoryIntegrationTest[] {
            new DataRepositoryIntegrationTest { Id = 1 },
            new DataRepositoryIntegrationTest { Id = 2 }};
        var expected = _repo.Count - items.Count();

        // act
        _repo.Delete(items);
        var actual = _repo.Count;

        // assert
        Assert.AreEqual(expected, actual);
    }
}

And they ALL pass!