Bug #93855 Incorrect query generated giving error Failed in 8 ms with error: Unknown column
Submitted: 8 Jan 18:17 Modified: 16 Jan 17:26
Reporter: Sandeep Akhare Email Updates:
Status: Open Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:8.0.13 OS:Microsoft Windows
Assigned to: CPU Architecture:Any

[8 Jan 18:17] Sandeep Akhare
Description:
For Entity Framework 6.2 and MYSQL.Data.Entityframework  i am getting below error . It gave me same error for MySql.Data.Entity.6.9.9 as well 

Below is the script it has generated I removed some include statements 

Entity framework code C#
var expenseReports = _unitOfWork.ExpenseReportRepository.Get(x => ReportIds.Contains(x.ExpenseReportId) && x.ExpenseStatusId != 6, null, "expenseitems.reciepts, expenseitems.expenseitemfinancedocuments").ToList();

Opened connection at 1/8/2019 1:13:46 PM -05:00
SELECT
`Project3`.`ExpenseReportId`, 
`Project3`.`EmpId`, 
`Project3`.`Address`, 
`Project3`.`EmpPaid`, 
`Project3`.`ExpenseType`, 
`Project3`.`ExpenseStatusId`, 
`Project3`.`RejectReason`, 
`Project3`.`CreatedDate`, 
`Project3`.`SubmittedDate`, 
`Project3`.`ReconciledDate`, 
`Project3`.`LastModifiedDate`, 
`Project3`.`expensereportscol`, 
`Project3`.`Approve1`, 
`Project3`.`Approve2`, 
`Project3`.`C37` AS `C1`, 
`Project3`.`C2`, 
`Project3`.`C3`, 
`Project3`.`C4`, 
`Project3`.`C5`, 
`Project3`.`C6`, 
`Project3`.`C7`, 
`Project3`.`C8`, 
`Project3`.`C9`, 
`Project3`.`C10`, 
`Project3`.`C11`, 
`Project3`.`C12`, 
`Project3`.`C13`, 
`Project3`.`C14`, 
`Project3`.`C15`, 
`Project3`.`C16`, 
`Project3`.`C17`, 
`Project3`.`C18`, 
`Project3`.`C19`, 
`Project3`.`C20`, 
`Project3`.`C21`, 
`Project3`.`C22`, 
`Project3`.`C23`, 
`Project3`.`C1` AS `C24`, 
`Project3`.`C24` AS `C25`, 
`Project3`.`C25` AS `C26`, 
`Project3`.`C26` AS `C27`, 
`Project3`.`C27` AS `C28`, 
`Project3`.`C28` AS `C29`, 
`Project3`.`C29` AS `C30`, 
`Project3`.`C30` AS `C31`, 
`Project3`.`C31` AS `C32`, 
`Project3`.`C32` AS `C33`, 
`Project3`.`C33` AS `C34`, 
`Project3`.`C34` AS `C35`, 
`Project3`.`C35` AS `C36`, 
`Project3`.`C36` AS `C37`
FROM (SELECT
`Filter1`.`ExpenseReportId`, 
`Filter1`.`EmpId`, 
`Filter1`.`Address`, 
`Filter1`.`EmpPaid`, 
`Filter1`.`ExpenseType`, 
`Filter1`.`ExpenseStatusId`, 
`Filter1`.`RejectReason`, 
`Filter1`.`CreatedDate`, 
`Filter1`.`SubmittedDate`, 
`Filter1`.`ReconciledDate`, 
`Filter1`.`LastModifiedDate`, 
`Filter1`.`expensereportscol`, 
`Filter1`.`Approve1`, 
`Filter1`.`Approve2`, 
`UnionAll1`.`C1`, 
`UnionAll1`.`ExpenseItemId` AS `C2`, 
`UnionAll1`.`ExpenseItemId1` AS `C3`, 
`UnionAll1`.`ExpenseReportId` AS `C4`, 
`UnionAll1`.`Date` AS `C5`, 
`UnionAll1`.`ProjId` AS `C6`, 
`UnionAll1`.`DeptId` AS `C7`, 
`UnionAll1`.`IsBillable` AS `C8`, 
`UnionAll1`.`PaymentMethodId` AS `C9`, 
`UnionAll1`.`ExpenseTypeId` AS `C10`, 
`UnionAll1`.`CurrencyId` AS `C11`, 
`UnionAll1`.`Vendor` AS `C12`, 
`UnionAll1`.`Amount` AS `C13`, 
`UnionAll1`.`IsReceiptAttached` AS `C14`, 
`UnionAll1`.`Comments` AS `C15`, 
`UnionAll1`.`ExpenseStatusId` AS `C16`, 
`UnionAll1`.`Reconsile` AS `C17`, 
`UnionAll1`.`Notes` AS `C18`, 
`UnionAll1`.`BusinessPurpose` AS `C19`, 
`UnionAll1`.`Mileage` AS `C20`, 
`UnionAll1`.`SourceCurrencyAmount` AS `C21`, 
`UnionAll1`.`PSAPushed` AS `C22`, 
`UnionAll1`.`ExternalId` AS `C23`, 
`UnionAll1`.`RecieptsId` AS `C24`, 
`UnionAll1`.`ExpenseItemId2` AS `C25`, 
`UnionAll1`.`Image` AS `C26`, 
`UnionAll1`.`FileName` AS `C27`, 
`UnionAll1`.`EmpId` AS `C28`, 
`UnionAll1`.`CreatedDate` AS `C29`, 
`UnionAll1`.`FilePath` AS `C30`, 
`UnionAll1`.`C2` AS `C31`, 
`UnionAll1`.`C3` AS `C32`, 
`UnionAll1`.`C4` AS `C33`, 
`UnionAll1`.`C5` AS `C34`, 
`UnionAll1`.`C6` AS `C35`, 
`UnionAll1`.`C7` AS `C36`, 
CASE WHEN (`UnionAll1`.`ExpenseItemId` IS NOT NULL) THEN (1)  ELSE (NULL) END AS `C37`
FROM (SELECT
`Extent1`.`ExpenseReportId`, 
`Extent1`.`EmpId`, 
`Extent1`.`Address`, 
`Extent1`.`EmpPaid`, 
`Extent1`.`ExpenseType`, 
`Extent1`.`ExpenseStatusId`, 
`Extent1`.`RejectReason`, 
`Extent1`.`CreatedDate`, 
`Extent1`.`SubmittedDate`, 
`Extent1`.`ReconciledDate`, 
`Extent1`.`LastModifiedDate`, 
`Extent1`.`expensereportscol`, 
`Extent1`.`Approve1`, 
`Extent1`.`Approve2`
FROM `expensereports` AS `Extent1`
 WHERE (`Extent1`.`ExpenseReportId` IN ( 4 )) AND (NOT ((6 = `Extent1`.`ExpenseStatusId`) AND (`Extent1`.`ExpenseStatusId` IS NOT NULL)))) AS `Filter1` LEFT OUTER JOIN ((SELECT
CASE WHEN (`Extent3`.`RecieptsId` IS NOT NULL) THEN (1)  ELSE (NULL) END AS `C1`, 
`Extent2`.`ExpenseItemId`, 
`Extent2`.`ExpenseItemId` AS `ExpenseItemId1`, 
`Extent2`.`ExpenseReportId`, 
`Extent2`.`Date`, 
`Extent2`.`ProjId`, 
`Extent2`.`DeptId`, 
`Extent2`.`IsBillable`, 
`Extent2`.`PaymentMethodId`, 
`Extent2`.`ExpenseTypeId`, 
`Extent2`.`CurrencyId`, 
`Extent2`.`Vendor`, 
`Extent2`.`Amount`, 
`Extent2`.`IsReceiptAttached`, 
`Extent2`.`Comments`, 
`Extent2`.`ExpenseStatusId`, 
`Extent2`.`Reconsile`, 
`Extent2`.`Notes`, 
`Extent2`.`BusinessPurpose`, 
`Extent2`.`Mileage`, 
`Extent2`.`SourceCurrencyAmount`, 
`Extent2`.`PSAPushed`, 
`Extent2`.`ExternalId`, 
`Extent3`.`RecieptsId`, 
`Extent3`.`ExpenseItemId` AS `ExpenseItemId2`, 
`Extent3`.`Image`, 
`Extent3`.`FileName`, 
`Extent3`.`EmpId`, 
`Extent3`.`CreatedDate`, 
`Extent3`.`FilePath`, 
NULL AS `C2`, 
NULL AS `C3`, 
NULL AS `C4`, 
NULL AS `C5`, 
NULL AS `C6`, 
NULL AS `C7`
FROM `expenseitem` AS `Extent2` LEFT OUTER JOIN `reciepts` AS `Extent3` ON `Extent2`.`ExpenseItemId` = `Extent3`.`ExpenseItemId`
 WHERE 1 = 1) UNION ALL (SELECT
2 AS `C1`, 
`Extent4`.`ExpenseItemId`, 
`Extent4`.`ExpenseItemId` AS `ExpenseItemId1`, 
`Extent4`.`ExpenseReportId`, 
`Extent4`.`Date`, 
`Extent4`.`ProjId`, 
`Extent4`.`DeptId`, 
`Extent4`.`IsBillable`, 
`Extent4`.`PaymentMethodId`, 
`Extent4`.`ExpenseTypeId`, 
`Extent4`.`CurrencyId`, 
`Extent4`.`Vendor`, 
`Extent4`.`Amount`, 
`Extent4`.`IsReceiptAttached`, 
`Extent4`.`Comments`, 
`Extent4`.`ExpenseStatusId`, 
`Extent4`.`Reconsile`, 
`Extent4`.`Notes`, 
`Extent4`.`BusinessPurpose`, 
`Extent4`.`Mileage`, 
`Extent4`.`SourceCurrencyAmount`, 
`Extent4`.`PSAPushed`, 
`Extent4`.`ExternalId`, 
NULL AS `C2`, 
NULL AS `C3`, 
NULL AS `C4`, 
NULL AS `C5`, 
NULL AS `C6`, 
NULL AS `C7`, 
NULL AS `C8`, 
`Extent5`.`DocumentId`, 
`Extent5`.`ExpenseItemID` AS `ExpenseItemId2`, 
`Extent5`.`DocumentName`, 
`Extent5`.`DocumentPath`, 
`Extent5`.`UploadedDate`, 
`Extent5`.`UploadedBy`
FROM `expenseitem` AS `Extent4` INNER JOIN `expenseitemfinancedocument` AS `Extent5` ON `Extent4`.`ExpenseItemId` = `Extent5`.`ExpenseItemID`
 WHERE 1 = 1)) AS `UnionAll1` ON `Extent1`.`ExpenseReportId` = `UnionAll1`.`ExpenseReportId`) AS `Project3`
 ORDER BY 
`Project3`.`ExpenseReportId` ASC, 
`Project3`.`C37` ASC, 
`Project3`.`C3` ASC, 
`Project3`.`C1` ASC
-- Executing at 1/8/2019 1:13:46 PM -05:00
-- Failed in 695 ms with error: Unknown column 'Extent1.ExpenseReportId' in 'on clause'

Closed connection at 1/8/2019 1:13:48 PM -05:00

How to repeat:
MY SQL Database 

MySQL Connector 8.0.13/6.9.9

Entityframework 6/6.2 

Query
[8 Jan 18:23] Sandeep Akhare
Entity diagram showing the relation between entities

Attachment: Untitled.png (image/png, text), 84.19 KiB.

[16 Jan 17:26] Sandeep Akhare
Could you please update if there is something wrong with the relation or this issue is present in the mysql entity framework