Generate too many sub query when use where conditional on navigation property.

[Caskia]

• verison: 2.6.0
• attendee.cs

public class Attendee : FullAuditedEntity<long, User>
    {
        #region Const Properties

        public const int BarcodeLength = 12;

        public const int CheckCodeLength = 6;

        public const string GenerateIdKey = "attendees_p_id";

        public const int MaxRemarkLength = 5000;

        #endregion Const Properties

        #region Properties

        [Required]
        [StringLength(BarcodeLength, MinimumLength = BarcodeLength)]
        public virtual string Barcode { get; set; }

        [Required]
        [StringLength(CheckCodeLength, MinimumLength = CheckCodeLength)]
        public virtual string CheckCode { get; set; }

        public virtual DateTime? CheckinTimeUtc { get; set; }

        [StringLength(User.MaxEmailAddressLength)]
        public virtual string EmailAddress { get; set; }

        public virtual EmailStatus EmailStatus { get; set; } = EmailStatus.NoEmail;

        public virtual long EventId { get; set; }

        public virtual long EventScheduleId { get; set; }

        [StringLength(User.MaxNameLength)]
        public virtual string FirstName { get; set; }

        public virtual bool HasChecked { get; protected set; } = false;

        public virtual bool HasVoided { get; protected set; } = false;

        public virtual bool IsAvailable { get; protected set; } = false;

        [StringLength(User.MaxSurnameLength)]
        public virtual string LastName { get; set; }

        public virtual long OrderId { get; set; }

        public virtual long OrderTicketItemId { get; set; }

        [StringLength(User.MaxPhoneNumberLength)]
        public virtual string PhoneNumber { get; set; }

        [Column(TypeName = "text")]
        [StringLength(MaxRemarkLength)]
        public virtual string Remark { get; set; }

        public virtual long TicketId { get; set; }

        public virtual long? UserId { get; set; }

        #region Navigation Properties

        [ForeignKey("EventId")]
        public virtual Event Event { get; set; }

        [ForeignKey("EventScheduleId")]
        public virtual EventSchedule EventSchedule { get; set; }

        [ForeignKey("OrderId")]
        public virtual Order Order { get; set; }

        [ForeignKey("OrderTicketItemId")]
        public virtual OrderTicketItem OrderTicketItem { get; set; }

        [ForeignKey("TicketId")]
        public virtual Ticket Ticket { get; set; }

        [ForeignKey("UserId")]
        public virtual User User { get; set; }

        #region Collection Properties

        public virtual ICollection<AttendeeQuestionAnswer> QuestionAnswers { get; set; } = new List<AttendeeQuestionAnswer>();

        public virtual ICollection<OrderTicketRefund> Refunds { get; set; } = new List<OrderTicketRefund>();

        #endregion Collection Properties

        #endregion Navigation Properties
}

• linq

var query =  _attendeeRepository.GetAll().Where(a => a.Order.CustomerFirstName == "test")

• generate sql

SELECT
`Apply1`.`Id`, 
`Apply1`.`Barcode`, 
`Apply1`.`CheckCode`, 
`Apply1`.`CheckinTimeUtc`, 
`Apply1`.`EmailAddress`, 
`Apply1`.`EmailStatus`, 
`Apply1`.`EventId`, 
`Apply1`.`EventScheduleId`, 
`Apply1`.`FirstName`, 
`Apply1`.`HasChecked`, 
`Apply1`.`HasVoided`, 
`Apply1`.`IsAvailable`, 
`Apply1`.`LastName`, 
`Apply1`.`OrderId`, 
`Apply1`.`OrderTicketItemId`, 
`Apply1`.`PhoneNumber`, 
`Apply1`.`Remark`, 
`Apply1`.`TicketId`, 
`Apply1`.`UserId`, 
`Apply1`.`IsDeleted`, 
`Apply1`.`DeleterUserId`, 
`Apply1`.`DeletionTime`, 
`Apply1`.`LastModificationTime`, 
`Apply1`.`LastModifierUserId`, 
`Apply1`.`CreationTime`, 
`Apply1`.`CreatorUserId`
FROM (SELECT
`Filter1`.`Id`, 
`Filter1`.`Barcode`, 
`Filter1`.`CheckCode`, 
`Filter1`.`CheckinTimeUtc`, 
`Filter1`.`EmailAddress`, 
`Filter1`.`EmailStatus`, 
`Filter1`.`EventId`, 
`Filter1`.`EventScheduleId`, 
`Filter1`.`FirstName`, 
`Filter1`.`HasChecked`, 
`Filter1`.`HasVoided`, 
`Filter1`.`IsAvailable`, 
`Filter1`.`LastName`, 
`Filter1`.`OrderId`, 
`Filter1`.`OrderTicketItemId`, 
`Filter1`.`PhoneNumber`, 
`Filter1`.`Remark`, 
`Filter1`.`TicketId`, 
`Filter1`.`UserId`, 
`Filter1`.`IsDeleted`, 
`Filter1`.`DeleterUserId`, 
`Filter1`.`DeletionTime`, 
`Filter1`.`LastModificationTime`, 
`Filter1`.`LastModifierUserId`, 
`Filter1`.`CreationTime`, 
`Filter1`.`CreatorUserId`, 
(SELECT
`Extent2`.`Id`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `ID1`, 
(SELECT
`Extent2`.`Amount`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `Amount`, 
(SELECT
`Extent2`.`AmountRefund`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `AmountRefund`, 
(SELECT
`Extent2`.`CardLast4`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `CardLast4`, 
(SELECT
`Extent2`.`ClientIP`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `ClientIP`, 
(SELECT
`Extent2`.`Currency`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `Currency`, 
(SELECT
`Extent2`.`CustomerEmailAddress`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `CustomerEmailAddress`, 
(SELECT
`Extent2`.`CustomerFirstName`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `CustomerFirstName`, 
(SELECT
`Extent2`.`CustomerId`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `CustomerId`, 
(SELECT
`Extent2`.`CustomerLastName`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `CustomerLastName`, 
(SELECT
`Extent2`.`CustomerPhoneNumber`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `CustomerPhoneNumber`, 
(SELECT
`Extent2`.`CustomerZipCode`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `CustomerZipCode`, 
(SELECT
`Extent2`.`DiscountAmount`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `DiscountAmount`, 
(SELECT
`Extent2`.`DonationAmount`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `DonationAmount`, 
(SELECT
`Extent2`.`EmailStatus`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `EMAILSTATUS1`, 
(SELECT
`Extent2`.`EventId`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `EVENTID1`, 
(SELECT
`Extent2`.`EventScheduleId`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `EVENTSCHEDULEID1`, 
(SELECT
`Extent2`.`MerchantDonationRevenue`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `MerchantDonationRevenue`, 
(SELECT
`Extent2`.`MerchantDonationRevenueRefund`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `MerchantDonationRevenueRefund`, 
(SELECT
`Extent2`.`MerchantFee`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `MerchantFee`, 
(SELECT
`Extent2`.`MerchantRevenue`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `MerchantRevenue`, 
(SELECT
`Extent2`.`MerchantRevenueRefund`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `MerchantRevenueRefund`, 
(SELECT
`Extent2`.`MerchantTicketRevenue`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `MerchantTicketRevenue`, 
(SELECT
`Extent2`.`MerchantTicketRevenueRefund`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `MerchantTicketRevenueRefund`, 
(SELECT
`Extent2`.`PaidTimeUtc`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `PaidTimeUtc`, 
(SELECT
`Extent2`.`PaymentMethod`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `PaymentMethod`, 
(SELECT
`Extent2`.`PaymentMethodAdditionalFee`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `PaymentMethodAdditionalFee`, 
(SELECT
`Extent2`.`PaymentMethodAdditionalFeeRefund`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `PaymentMethodAdditionalFeeRefund`, 
(SELECT
`Extent2`.`PaymentMethodAdditionalFixFee`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `PaymentMethodAdditionalFixFee`, 
(SELECT
`Extent2`.`PaymentMethodSettingSnapshotSerialized`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `PaymentMethodSettingSnapshotSerialized`, 
(SELECT
`Extent2`.`PaymentStatus`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `PaymentStatus`, 
(SELECT
`Extent2`.`PaymentTransactionToken`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `PaymentTransactionToken`, 
(SELECT
`Extent2`.`ReferralUserName`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `ReferralUserName`, 
(SELECT
`Extent2`.`SparxoFee`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `SparxoFee`, 
(SELECT
`Extent2`.`SparxoFeeRefund`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `SparxoFeeRefund`, 
(SELECT
`Extent2`.`Status`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `Status`, 
(SELECT
`Extent2`.`SubtotalAmount`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `SubtotalAmount`, 
(SELECT
`Extent2`.`Tax`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `Tax`, 
(SELECT
`Extent2`.`TicketAmount`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `TicketAmount`, 
(SELECT
`Extent2`.`TransactionFixFee`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `TransactionFixFee`, 
(SELECT
`Extent2`.`Type`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `Type`, 
(SELECT
`Extent2`.`IsDeleted`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `ISDELETED1`, 
(SELECT
`Extent2`.`DeleterUserId`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `DELETERUSERID1`, 
(SELECT
`Extent2`.`DeletionTime`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `DELETIONTIME1`, 
(SELECT
`Extent2`.`LastModificationTime`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `LASTMODIFICATIONTIME1`, 
(SELECT
`Extent2`.`LastModifierUserId`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `LASTMODIFIERUSERID1`, 
(SELECT
`Extent2`.`CreationTime`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `CREATIONTIME1`, 
(SELECT
`Extent2`.`CreatorUserId`
FROM `Orders` AS `Extent2`
 WHERE ((`Extent2`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AND (`Filter1`.`OrderId` = `Extent2`.`Id`) LIMIT 1) AS `CREATORUSERID1`
FROM (SELECT
`Extent1`.`Id`, 
`Extent1`.`Barcode`, 
`Extent1`.`CheckCode`, 
`Extent1`.`CheckinTimeUtc`, 
`Extent1`.`EmailAddress`, 
`Extent1`.`EmailStatus`, 
`Extent1`.`EventId`, 
`Extent1`.`EventScheduleId`, 
`Extent1`.`FirstName`, 
`Extent1`.`HasChecked`, 
`Extent1`.`HasVoided`, 
`Extent1`.`IsAvailable`, 
`Extent1`.`LastName`, 
`Extent1`.`OrderId`, 
`Extent1`.`OrderTicketItemId`, 
`Extent1`.`PhoneNumber`, 
`Extent1`.`Remark`, 
`Extent1`.`TicketId`, 
`Extent1`.`UserId`, 
`Extent1`.`IsDeleted`, 
`Extent1`.`DeleterUserId`, 
`Extent1`.`DeletionTime`, 
`Extent1`.`LastModificationTime`, 
`Extent1`.`LastModifierUserId`, 
`Extent1`.`CreationTime`, 
`Extent1`.`CreatorUserId`
FROM `Attendees` AS `Extent1`
 WHERE (`Extent1`.`IsDeleted` = @DynamicFilterParam_000003) OR (@DynamicFilterParam_000004 IS NOT NULL)) AS `Filter1`) AS `Apply1`
 WHERE (`Apply1`.`CustomerFirstName` = @p__linq__0) OR ((`Apply1`.`CustomerFirstName` IS  NULL) AND (@p__linq__0 IS  NULL))

[jcachat]

I'm not sure what "_attendeeRepository.GetAll()" does and also please include your filter(s).

[Caskia]

GetAll Method

public IQueryable<Attendee> GetAll()
{
     return DbContext.Set<Attendee>();
}

Filters

modelBuilder.Filter("SoftDelete", (ISoftDelete d) => d.IsDeleted, false);
modelBuilder.Filter("MustHaveTenant", (IMustHaveTenant t, int tenantId) => t.TenantId == tenantId || (int?)t.TenantId == null, 0); 
modelBuilder.Filter("MayHaveTenant", (IMayHaveTenant t, int? tenantId) => t.TenantId == tenantId, 0);

[jcachat]

I am not able to reproduce this. And I don't see anything unusual about what you are doing.

Is there any way you can distill this all down to a simple example and post the full code of that?

Also, what database are you using?

[Caskia]

The source code is very simple, nothing special, I'm using mysql.
Will the problem on mysql connector?

https://dba.stackexchange.com/questions/774/why-does-this-entity-framework-query-perform-so-bad-in-mysql
https://bugs.mysql.com/bug.php?id=75272

[jcachat]

I reproduced the problem when using MySQL. It is being caused by these lines in DynamicFilterQueryVisitorCSpace.Visit(DbPropertyExpression expression)

if (_DbContext.IsMySql())
    return newFilterExpression.Limit(DbConstantExpression.FromInt32(1)).Element();

That was needed when I first added support for navigation property filtering. At the time, I was testing with MySQL 5.5. I had to reload my machine recently so I installed the latest (5.7). So I'm wondering if it's a change in behavior from 5.5 -> 5.7.

What version of MySQL are you using?

[Caskia]

Thanks for your investigation.
Mysql version is 5.6.
Mysql connector version is 6.9.9.
Mysql entity framework is 6.9.9.

[Caskia]

@jcachat
How is it going?

[jcachat]

Sorry, I have not found a solution. Commenting out the lines I mentioned before causes some other unit tests to fail - even on MySQL 5.7. So I think it's more likely a difference in how the query is handled when it has the .Where() clause on it. It's definitely a difference (or possibly an issue) with how the MySQL EF provider works.

[Lefka]

I have the same issue, is there any plan to fix this?