OUTER APPLY

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
dan_j
Posts: 11
Joined: Wed 28 Jun 2017 14:06

OUTER APPLY

Post by dan_j » Mon 08 Feb 2021 17:03

How do I prevent DotConnect for Mysql from using OUTER APPLY which is not supported on MySQL?

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()
                                  };
results in this SQL code, which is not supported by MySQL.

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

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: OUTER APPLY

Post by Shalex » Thu 11 Feb 2021 15:30

We will investigate the possibility of translating specific expression tree scenarios using the LITERAL clause introduced in MySQL v8.0.14 instead of SQL Server's CROSS APPLY and OUTER APPLY operators. There is no timeframe for this task.

As a workaround, when constructing your LINQ to Entities queries, avoid the following typical scenarios that might lead to the presence of CROSS APPLY and/or OUTER APPLY operators in the output query:
* a correlated subquery with paging
* an AnyElement over a correlated sub-query, or over a collection produced by navigation
* LINQ queries that use grouping methods that accept an element selector
* a query in which a CROSS APPLY or an OUTER APPLY is explicitly specified
* a query that has a DEREF construct over a REF construct

Post Reply