Entity Framework Design Assistance

I have a mess of a series of Databases I have to work with. None have foreign keys, rarely have primary keys and for those that don’t I’m lucky if they actually have some combination of unique properties to count as a composite.

I’m trying to improve upon a design I’m currently using, but keep running into roadblocks.

A brief example of what I’m currently doing using 4 Entities, a repository and a service to interact with it.


IQueryable<Checks> GetChecks(); IQueryable<Vendors> GetVendors(); IQueryable<Details> GetDetails(); IQueryable<Transactions> GetTransactions(); 


var checks = _repo.GetChecks(); var vendors = _repo.GetVendors(); var details = _repo.GetDetails(); var transactions = _repo.GetTransactions(); var results = from c in checks join v in vendors on c.VendorCode equals v.VendorCode where c.CheckDate == checkDate && !c.VoidDate.HasValue && !excludeCategories.Contains(v.VendorCategory) && c.IgnoreColumn != 1 select new { .... Remittances = from check in checks join detail in details on check.CheckId equals detail.CheckId join tran in transactions on detail.InvTranNo equals tran.TranNo where check.CheckId == c.CheckId && tran.PaymentType == "CHK" select new { .... } }; 

​This works, but I don’t like it, doesn’t seem to follow any best practices and in general is tough to test. I have tried moving some of the where clauses into the repo (for instance)

IQueryable<Checks> GetChecks(DateTime checkDate); IQueryable<Vendors> GetVendors(IEnumerable<string> vendorCodes); IQueryable<Details> GetDetails(IEnumerable<string> checkIds); IQueryable<Transactions> GetTransactions(IEnumerable<string> tranNos); 

But this has proven to be more inefficient. The SQL generated takes 45 seconds alone on the GetDetails portion. The most efficient I’ve been is the original at the top.

I’ve tried messing around with [ForeignKey] but haven’t had any success other than confusing the heck out of myself. Ideally I’d love to avoid all the joins and work with this in a more native feeling manner that isn’t just a variation on a SQL query.

submitted by /u/gilliduck
[link] [comments]

Leave a Reply