For example, this code...
Code: Select all
var formattedApartmentBlocks = from apartmentBlock in dbContext.ApartmentBlocks
select new InvoicingApartmentViewModel()
{
ID = apartmentBlock.ID,
BlockName = apartmentBlock.Blockname,
BlockNameWithPostcode = apartmentBlock.Blockname + " " + apartmentBlock.Postcode,
RunNow = false,
InvoiceRunUpto = apartmentBlock.InvoiceRunUpto,
InvoiceRunRequested = apartmentBlock.Invoicerunrequested,
EarliestUninvoicedTransaction = (from transaction in dB.Transactions
where transaction.InvoiceLine == null &&
transaction.TransactionType.ShouldBeInvoiced &&
transaction.Tenancy.Customer.Istestaccount == false &&
transaction.Tenancy.Apartment.ApartmentblockID == apartmentBlock.ID
orderby transaction.Datetime
select transaction).FirstOrDefault()
};
Code: Select all
SELECT t1.ID, t1.blockname AS Blockname, CONCAT(CONCAT(t1.blockname, :p1), t1.postcode) AS C1, t1.InvoiceRunUpto, t1.invoicerunrequested AS Invoicerunrequested, t2.ID AS ID1, t2.`datetime`, t2.tenancyID, t2.meterID, t2.periodstartvalue, t2.periodstartdate, t2.periodendvalue, t2.periodenddate, t2.createdbyID, t2.typeID, t2.description, t2.IsAReversal, t2.subdescription, t2.unitprice, t2.quantity, t2.subtotal, t2.vatrateID, t2.vatpercentage, t2.vatamount, t2.total, t2.created, t2.InvoiceLineID, t2.openingmeterreadingID, t2.closingmeterreadingID, t2.reversaltransactionID, t2.managementcompanyid
FROM hayg.apartmentblocks t1
OUTER APPLY (
SELECT t3.ID, t3.`datetime`, t3.tenancyID, t3.meterID, t3.periodstartvalue, t3.periodstartdate, t3.periodendvalue, t3.periodenddate, t3.createdbyID, t3.typeID, t3.description, t3.IsAReversal, t3.subdescription, t3.unitprice, t3.quantity, t3.subtotal, t3.vatrateID, t3.vatpercentage, t3.vatamount, t3.total, t3.created, t3.InvoiceLineID, t3.openingmeterreadingID, t3.closingmeterreadingID, t3.reversaltransactionID, t3.managementcompanyid
FROM hayg.transactions t3
INNER JOIN (hayg.tenancies t4
INNER JOIN hayg.apartments t5 ON t4.apartmentID = t5.ID
INNER JOIN hayg.customers t6 ON t4.customerID = t6.ID) ON t3.tenancyID = t4.ID
INNER JOIN hayg.transactiontypes t7 ON t3.typeID = t7.ID
WHERE (t3.InvoiceLineID IS NULL) AND (t7.ShouldBeInvoiced <> 0) AND ((t6.istestaccount <> 0) = :p0) AND (t5.apartmentblockID = t1.ID)
ORDER BY t3.`datetime` LIMIT 1
) t2
-- p0: Input Bit (Size = 0; DbType = Int64) [False]
-- p1: Input VarChar (Size = 1; DbType = AnsiString) [ ]