Repository Pattern – Using Repository Pattern with Joined Queries in C#

In conjunction with Unit Tests and Dependency Injection I (and my primary coworker) are exploring Repositories. However we cannot come to a solid plan of action for implementation.

In a basic scenario we have a Repository that encapsulates a single context and one or more entities. The public methods of that Repository return either List or a single entity result. IE

public class SomeEntity
{
    public int Id { get; set; }
    public string SomeValue { get; set; }
}

public class SomeContext : DbContext
{
    public virtual DbSet<SomeEntity> SomeEntities { get; set; }
}

public class SomeRepo
{
    public SomeRepo()
    {
        _context = new SomeContext();
    }

    public SomeEntity GetEntity(int id)
    {
        return _context.SomeEntities.Find(id);
    }

    public IQueryable<SomeEntity> GetAllEntities()
    {
        return _context.SomeEntities;
    }
}

This is all well and good and works fine for us 99% of the time. The quandry is when there are multiple entities in a Repo and a join is required. Currently we just do something like the below in a UOW class that uses the repository;

public SomeModel SomeMethod()
{
    var entity1 = _repo.GetEntity1();
    var entity2 = _repo.GetEntity2();
    return from a in entity1
           join b in entity2 on a.id equals b.id
           select new SomeModel
           {
               Foo = a.foo,
               Bar = b.bar
           };
}

From the many contradictory discussions/posts/blogs/etc about Repositories and our own personal feelings, this doesn't seem right. What also doesn't seem right is doing the join inside the repo and then returning something that isn't one of the entities.

Our typical design is to have a context wrapped inside an Repository which is Dependency Injected into a UOW class. This way we can have a Unit Test that mocks the Repo returning fake DB results.

Knowing that this is a loaded question, what might be a good pattern for us?

For a more real world example of a join scenario (I am not happy with this code, it was a rush job to make something happen, but it's a good example of the scenario we need to address):

public class AccountingContext : DbContext
{
    public DbSet<Vendor> Vendors { get; set; }
    public DbSet<Check> Checks { get; set; }
    public DbSet<ApCheckDetail> CheckDetails { get; set; }
    public DbSet<Transaction> Transactions { get; set; }

    public AccountingContext(string connString) : base(connString)
    {

    }
}

public class AccountingRepo : IAccountingRepo
{
    private readonly AccountingContext _accountingContext;

    public AccountingRepo(IConnectionStringMaker connectionStringMaker, ILocalConfig localConfig)
    {
        // code to generate connString

        _accountingContext = new AccountingContext(connString);
    }

    public IEnumerable<Check> GetChecksByDate(DateTime checkDate)
    {
        return _accountingContext.Checks
            .Where(c => c.CHKDATE.Value == checkDate.Date &&
                        !c.DELVOIDDATE.HasValue);
    }

    public IEnumerable<Vendor> GetVendors(IEnumerable<string> vendorId)
    {
        return _accountingContext.Vendors
            .Where(v => vendorId.Contains(v.VENDCODE))
            .Distinct();
    }

    public IEnumerable<ApCheckDetail> GetCheckDetails(IEnumerable<string> checkIds)
    {
        return _accountingContext.CheckDetails
            .Where(c => checkIds.Contains(c.CheckId));
    }

    public IEnumerable<Transaction> GetTransactions(IEnumerable<string> tranNos, DateTime checkDate)
    {
        var ids = tranNos.ToList();
        var sb = new StringBuilder();
        sb.Append($"'{ids.First()}'");
        for (int i = 1; i < ids.Count; i++)
        {
            sb.Append($", '{ids[i]}'");
        }

        var sql = $"Select TranNo = TRANNO, InvoiceNo = INVNO, InvoiceDate = INVDATE, InvoiceAmount = INVAMT, DiscountAmount = DISCEARNED, TaxWithheld = OTAXWITHAMT, PayDate = PAYDATE from APTRAN where TRANNO in ({sb})";
        return _accountingContext.Set<Transaction>().SqlQuery(sql).ToList();
    }
}

public class AccountingInteraction : IAccountingInteraction
{
    private readonly IAccountingRepo _accountingRepo;

    public AccountingInteraction(IAccountingRepo accountingRepo)
    {
        _accountingRepo = accountingRepo;
    }

    public IList<CheckDetail> GetChecksToPay(DateTime checkDate, IEnumerable<string> excludeVendCats)
    {
        var todaysChecks = _accountingRepo.GetChecksByDate(checkDate).ToList();

        var todaysVendors = todaysChecks.Select(c => c.APCODE).Distinct().ToList();
        var todaysCheckIds = todaysChecks.Select(c => c.CheckId).ToList();

        var vendors = _accountingRepo.GetVendors(todaysVendors).ToList();
        var apCheckDetails = _accountingRepo.GetCheckDetails(todaysCheckIds).ToList();
        var todaysCheckDetails = apCheckDetails.Select(a => a.InvTranNo).ToList();

        var tranDetails = _accountingRepo.GetTransactions(todaysCheckDetails, checkDate).ToList();


        return (from c in todaysChecks
                join v in vendors on c.APCODE equals v.VENDCODE
                where !c.DELVOIDDATE.HasValue &&
                      !excludeVendCats.Contains(v.VENDCAT) &&
                      c.BACSPMT != 1 &&
                      v.DEFPMTTYPE == "CHK"
                select new CheckDetail
                {
                    VendorId = v.VENDCODE,
                    VendorName = v.VENDNAME,
                    CheckDate = c.CHKDATE.Value,
                    CheckAmount = c.CHKAMT.Value,
                    CheckNumber = c.CHECKNUM.Value,
                    Address1 = v.ADDR1,
                    Address2 = v.ADDR2,
                    City = v.CITY,
                    State = v.STATE,
                    Zip = v.ZIP,
                    Company = c.COMPNUM.Value,
                    VoidDate = c.DELVOIDDATE,
                    PhoneNumber = v.OFFTELE,
                    Email = v.EMAIL,
                    Remittances = (from check in todaysChecks
                                   join d in apCheckDetails on check.CheckId equals d.CheckId
                                   join t in tranDetails on d.InvTranNo equals t.TranNo
                                   where check.CheckId == c.CheckId
                                   select new RemittanceModel
                                   {
                                       InvoiceAmount = t.InvoiceAmount,
                                       CheckAmount = d.PaidAmount,
                                       InvoiceDate = t.InvoiceDate,
                                       DiscountAmount = t.DiscountAmount,
                                       TaxWithheldAmount = t.TaxWithheld,
                                       InvoiceNumber = t.InvoiceNo
                                   }).ToList()
                }).ToList();
    }
}