ok lets say I have a collection of these ...

public class ReferenceLookup
    public string InvoiceNumber { get; set; }
    public CompanyReference Buyer { get; set; }
    public CompanyReference Supplier { get; set; }

I need a collection of these as results where any of the pieces exist ...

public class ReferenceLookupResult
    public SIHead SIHead { get; set; }
    public Company Buyer { get; set; }
    public Company Supplier { get; set; }

SIHead == invoice

Companies have related tables that contain external references for the company I somehow (if possible) want to write a single linq query that can do this in a way that exposes me a queryable set of ReferenceLookupResult if possible, how I would get these pieces is quite complex though

for example here's how I currently get a collection of companies by reference ...

public IQueryable<Company> ByReferences(CompanyReference[] refs)
    var refStrings = refs.Select(r => r.System + "|" + r.Ident);
    return GetAll().Where(c => c.CompanyTaxIdentifiers.Any(cti => refStrings.Contains(cti.ExternalReferenceSystem.DisplayName + "|" + cti.Reference)));

... i think i need multiple collections of companies and a collection of invoices where one from each set makes up 1 result dto

here's how I get invoices by reference ...

public IQueryable<SIHead> GetAllByRefs(ICollection<InvoiceReference> refs)
    var keys = GetAll().Select(i => new { Id = i.SIHeadId, Number = i.InvoiceNumber, Supplier = i.SupplierCompanyAux.CompanyId }).ToList();
    var resultItemIds = keys.Where(k => refs.Any(r => r.InvoiceNumber == k.Number && r.SupplierId == k.Supplier)).Select(k => k.Id).ToArray();
    return GetAll(resultItemIds);

an invoice reference is an invoice number and a supplier reference all invoices are at their root generated by suppliers.

Options considered so far

  • Create a sproc and call that (not queryable though I would think)
  • Create a view and add that to the context (EF Code First treats this like a normal entity set)

I'm thinking the latter might be the way to go, but in the past each time I've had a view for something I've ended up replacing it longer term with something LINQ based.

1 Answer 1


Using a view to produce the complex result set seems to be the best option in my case of the options presented in the question however ...

I found that with a slight architectural change in the business process I wouldn't end up in this situation at all, resulting 2 or 3 simpler requests rather than 1 complex one requiring a "special case" like this to be handled in the call handling logic.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.