Devart Forums

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) [ ]