Entity Framework invoice queries

Question

I have these queries written in Entity Framework where I try to get:

• Name of the customer
• Most Recent Invoice Ref
• Most Recent Invoice Amount (£)
• Number of outstanding invoices (#)
• Total of all outstanding invoices (£)

I use these two tables:

CREATE TABLE Customers
(
    CustomerId INT,
    Name NVARCHAR(100),
    Address1 NVARCHAR(100),
    Address2 NVARCHAR(100),
    Postcode NVARCHAR(100),
    Telephone NVARCHAR(15),
    CONSTRAINT Customers_PK PRIMARY KEY (CustomerId)
)

CREATE TABLE Invoices
(
    InvoiceId INT,
    CustomerId INT, -- FK
    Ref NVARCHAR(10),
    InvoiceDate DATETIME,
    IsPaid BIT,

    Value DECIMAL,
    CONSTRAINT Invoices_PK PRIMARY KEY (InvoiceId)
)

And the queries I make are:

public PaginatedList<CustomerListEntity> GetCustomers(int pageIndex, int pageSize, out int totalRecords)
        {
            var customers = _customerRepository.AllIncluding(x => x.Invoices).OrderBy(x => x.CustomerId).Select(x => new
              CustomerListEntity {
                Name = x.Name,
                CustomerID=x.CustomerId,
                RecentInvoiceRef = x.Invoices.OrderByDescending(t => t.InvoiceDate).FirstOrDefault() != null ? x.Invoices.OrderByDescending(t => t.InvoiceDate).FirstOrDefault().Ref : string.Empty,
                RecentInvoiceAmount = x.Invoices.OrderByDescending(t=>t.InvoiceDate).FirstOrDefault()!=null? x.Invoices.OrderByDescending(t => t.InvoiceDate).FirstOrDefault().Value:null,
                UnpaidInvoicesNumber=x.Invoices.Where(t=>t.IsPaid==false).Count(),
                UnpaidInvoicesTotalAmount = x.Invoices.Where(t => t.IsPaid == false).Sum(k=>k.Value)
            });
            totalRecords = customers.Count();
            return customers.ToPaginatedList(pageIndex, pageSize);
        }

Here is the implementation of AllINcluding:

public virtual IQueryable<T> AllIncluding(params Expression<Func<T, object>>[] includeProperties)
    {
        IQueryable<T> query = _entitiesContext.Set<T>();

        foreach (var includeProperty in includeProperties)
        {
            query = query.Include(includeProperty);
        }
        return query;
    }

Answer 1

First off, the Include is ignored because you project the result to a new result (Select(x => new{ ... }). So you may as well remove it.

Then, there is some room for improvement by eliminating the repetitive parts. It's much easier to do this in query syntax:

var customers = from cst in _customerRepository.All() // Assuming this method exists
    let lastInvoice = cst.Invoices.OrderByDescending(t => t.InvoiceDate).FirstOrDefault()
    let upaidInvoices = cst.Invoices.Where(t => !t.IsPaid)
    select new
      CustomerListEntity {
        Name = cst.Name,
        CustomerID = cst.CustomerId,
        RecentInvoiceRef = lastInvoice.Ref ?? string.Empty,
        RecentInvoiceAmount = lastInvoice.Value,
        UnpaidInvoicesNumber = upaidInvoices.Count(),
        UnpaidInvoicesTotalAmount = upaidInvoices.Sum(k => k.Value)
    });

The let keyword defines a local variable that can be reused in the LINQ statement. As you see, this greatly improves the readability of the code.

It also slightly improves the generated SQL query, because Ref and Value will now be retrieved in one subquery instead of two. Unfortunately, this doesn't apply to Count and Sum, because aggregates require separate subqueries.

Also, note that the null checks are removed. The LINQ statement is translated into SQL and executed in the database. SQL doesn't have this null reference concept.

One last point is that there is broad consensus on the additional repository layer being totally redundant. Consider removing and query directly on the context.