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;
}