Issue occur when dynamic filter is applied on eager loading with many-to-many relationship entities for MySQL

[Sankartest]

1. Description

When dynamic filter is applied on eager loading with many-to-many relationship entities, getting below error. And this error had been occurring on MySQL database and its working well on MSSQL database.

2. Exception

System.Data.Entity.Core.EntityCommandExecutionException
HResult=0x8013193C
Message=An error occurred while executing the command definition. See the inner exception for details.
Source=EntityFramework
StackTrace:
at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
at System.Data.Entity.Core.Objects.ObjectQuery1.<>c__DisplayClass41_0.<GetResults>b__1() at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
at System.Data.Entity.Core.Objects.ObjectQuery1.<>c__DisplayClass41_0.<GetResults>b__0() at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func1 operation)
at System.Data.Entity.Core.Objects.ObjectQuery1.GetResults(Nullable1 forMergeOption)
at System.Data.Entity.Core.Objects.ObjectQuery1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__31_0() at System.Data.Entity.Internal.LazyEnumerator1.MoveNext()
at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable1 source) at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.<>c__141.b__14_1(IEnumerable1 sequence) at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable1 query, Expression queryRoot)
at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)
at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)
at EntityFramework.DynamicFilters.Example.Program.Query(ExampleContext context, String userName, Int32 expected, Boolean blogFilterIsEnabled, Boolean reusedContext) in C:\New folder\new\EntityFramework.DynamicFilters\src\EntityFramework.DynamicFilters.Example\Program.cs:line 76
at EntityFramework.DynamicFilters.Example.Program.Main(String[] args) in C:\New folder\new\EntityFramework.DynamicFilters\src\EntityFramework.DynamicFilters.Example\Program.cs:line 33 This exception was originally thrown at this call stack:
[External Code]Inner Exception 1:
MySqlException: Unknown column 'Extent1.ID' in 'where clause'

3. Code Changes

1. EntityFramework.DynamicFilters\src\EntityFramework.DynamicFilters.Example\Model.cs

public class BlogEntry : ISoftDelete
{
[Key]
[Required]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public Guid ID { get; set; }
public Account Account { get; set; }
public Guid AccountID { get; set; }
public string Body { get; set; }
public bool IsDeleted { get; set; }
public int? IntValue { get; set; }
public string StringValue { get; set; }
public DateTime? DateValue { get; set; }
public bool IsActive { get; set; }
public int EntityBId { get; set; }
[ForeignKey("EntityBId")]
public EntityB EntityB { get; set; }
}

2. EntityFramework.DynamicFilters\src\EntityFramework.DynamicFilters.Example\ExampleContext.cs

a) cut the EntityAset from bottom to top
b) add the EntityBId value from Blogentries for both the UserName

var a1 = new EntityA { IsDeleted = false, Nav1 = new EntityB { IsDeleted = false }, Nav2 = new EntityB { IsDeleted = true } };
var a2 = new EntityA { IsDeleted = false, Nav1 = new EntityB { IsDeleted = false }, Nav2 = new EntityB { IsDeleted = true }, Nav3 = a1 };
var a3 = new EntityA { IsDeleted = true };
context.EntityASet.Add(a1);
context.EntityASet.Add(a2);
context.EntityASet.Add(a3);
context.SaveChanges(); var homer = new Account
{
UserName = "homer",
BlogEntries = new List
{
new BlogEntry { Body="Homer's first blog entry", IsDeleted=false, IsActive=true, StringValue="1", EntityBId = 1},
new BlogEntry { Body="Homer's second blog entry", IsDeleted=false, IsActive=true, StringValue="2", EntityBId = 1},
new BlogEntry { Body="Homer's third blog entry (deleted)", IsDeleted=true, IsActive=true, StringValue="3", EntityBId = 1 },
new BlogEntry { Body="Homer's fourth blog entry (deleted)", IsDeleted=true, IsActive=true, StringValue="4", EntityBId = 1},
new BlogEntry { Body="Homer's 5th blog entry (inactive)", IsDeleted=false, IsActive=false, StringValue="5", EntityBId = 1},
new BlogEntry { Body="Homer's 6th blog entry (deleted and inactive)", IsDeleted=true, IsActive=false, StringValue="6", EntityBId = 1},
}
};
context.Accounts.Add(homer); var bart = new Account
{
UserName = "bart",
BlogEntries = new List
{
new BlogEntry { Body="Bart's first blog entry", IsDeleted=false, IsActive=true, StringValue="7", EntityBId = 2},
new BlogEntry { Body="Bart's second blog entry", IsDeleted=false, IsActive=true, StringValue="8", EntityBId = 2},
new BlogEntry { Body="Bart's third blog entry", IsDeleted=false, IsActive=true, StringValue="9", EntityBId = 2},
new BlogEntry { Body="Bart's fourth blog entry (deleted)", IsDeleted=true, IsActive=true, StringValue="10", EntityBId = 2},
new BlogEntry { Body="Bart's fifth blog entry (deleted)", IsDeleted=true, IsActive=true, StringValue="11", EntityBId = 2},
new BlogEntry { Body="Bart's 6th blog entry (inactive)", IsDeleted=false, IsActive=false, StringValue="12", EntityBId = 2},
new BlogEntry { Body="Bart's 7th blog entry (deleted and inactive)", IsDeleted=true, IsActive=false, StringValue="13", EntityBId = 2},
}
};
context.Accounts.Add(bart); context.SaveChanges();

3).EntityFramework.DynamicFilters\src\EntityFramework.DynamicFilters.Example\Program.cs

var account = context.Accounts
.Include(a => a.BlogEntries)
.Include(a => a.BlogEntries.Select(x => x.EntityB)).FirstOrDefault();

Note : we have use the same filter provided in the solution.

Query output

SELECT
Project3.C1,
Project3.Discriminator,
Project3.ID,
Project3.UserName,
Project3.RemappedDBProp,
Project3.C2,
Project3.EntityBId,
Project3.ID1,
Project3.AccountID,
Project3.Body,
Project3.IsDeleted,
Project3.IntValue,
Project3.StringValue,
Project3.DateValue,
Project3.IsActive,
Project3.ID2,
Project3.IsDeleted1
FROM (SELECT
Apply2.ID,
Apply2.UserName,
Apply2.RemappedDBProp,
Apply2.Discriminator,
Apply2.C1,
Apply2.ID AS ID1,
Apply2.AccountID,
Apply2.Body,
Apply2.IsDeleted,
Apply2.IntValue,
Apply2.StringValue,
Apply2.DateValue,
Apply2.IsActive,
Apply2.EntityBId,
Apply2.Id AS ID2,
Apply2.IsDeleted AS IsDeleted1,
CASE WHEN (Apply2.ID IS NOT NULL) THEN (1) ELSE (NULL) END AS C2
FROM (SELECT
Limit1.DynamicFilterParam_000001,
Limit1.DynamicFilterParam_000002,
Limit1.ID,
Limit1.UserName,
Limit1.RemappedDBProp,
Limit1.Discriminator,
Limit1.C1,
(SELECT
Project2.ID
FROM (SELECT
Extent2.ID,
Extent2.AccountID,
Extent2.Body,
Extent2.IsDeleted,
Extent2.IntValue,
Extent2.StringValue,
Extent2.DateValue,
Extent2.IsActive,
Extent2.EntityBId
FROM BlogEntries AS Extent2
WHERE Extent1.ID = Extent2.AccountID) AS Project2) AS ID1,
(SELECT
Project2.AccountID
FROM (SELECT
Extent2.ID,
Extent2.AccountID,
Extent2.Body,
Extent2.IsDeleted,
Extent2.IntValue,
Extent2.StringValue,
Extent2.DateValue,
Extent2.IsActive,
Extent2.EntityBId
FROM BlogEntries AS Extent2
WHERE Extent1.ID = Extent2.AccountID) AS Project2) AS AccountID,
(SELECT
Project2.Body
FROM (SELECT
Extent2.ID,
Extent2.AccountID,
Extent2.Body,
Extent2.IsDeleted,
Extent2.IntValue,
Extent2.StringValue,
Extent2.DateValue,
Extent2.IsActive,
Extent2.EntityBId
FROM BlogEntries AS Extent2
WHERE Extent1.ID = Extent2.AccountID) AS Project2) AS Body,
(SELECT
Project2.IsDeleted
FROM (SELECT
Extent2.ID,
Extent2.AccountID,
Extent2.Body,
Extent2.IsDeleted,
Extent2.IntValue,
Extent2.StringValue,
Extent2.DateValue,
Extent2.IsActive,
Extent2.EntityBId
FROM BlogEntries AS Extent2
WHERE Extent1.ID = Extent2.AccountID) AS Project2) AS IsDeleted,
(SELECT
Project2.IntValue
FROM (SELECT
Extent2.ID,
Extent2.AccountID,
Extent2.Body,
Extent2.IsDeleted,
Extent2.IntValue,
Extent2.StringValue,
Extent2.DateValue,
Extent2.IsActive,
Extent2.EntityBId
FROM BlogEntries AS Extent2
WHERE Extent1.ID = Extent2.AccountID) AS Project2) AS IntValue,
(SELECT
Project2.StringValue
FROM (SELECT
Extent2.ID,
Extent2.AccountID,
Extent2.Body,
Extent2.IsDeleted,
Extent2.IntValue,
Extent2.StringValue,
Extent2.DateValue,
Extent2.IsActive,
Extent2.EntityBId
FROM BlogEntries AS Extent2
WHERE Extent1.ID = Extent2.AccountID) AS Project2) AS StringValue,
(SELECT
Project2.DateValue
FROM (SELECT
Extent2.ID,
Extent2.AccountID,
Extent2.Body,
Extent2.IsDeleted,
Extent2.IntValue,
Extent2.StringValue,
Extent2.DateValue,
Extent2.IsActive,
Extent2.EntityBId
FROM BlogEntries AS Extent2
WHERE Extent1.ID = Extent2.AccountID) AS Project2) AS DateValue,
(SELECT
Project2.IsActive
FROM (SELECT
Extent2.ID,
Extent2.AccountID,
Extent2.Body,
Extent2.IsDeleted,
Extent2.IntValue,
Extent2.StringValue,
Extent2.DateValue,
Extent2.IsActive,
Extent2.EntityBId
FROM BlogEntries AS Extent2
WHERE Extent1.ID = Extent2.AccountID) AS Project2) AS IsActive,
(SELECT
Project2.EntityBId
FROM (SELECT
Extent2.ID,
Extent2.AccountID,
Extent2.Body,
Extent2.IsDeleted,
Extent2.IntValue,
Extent2.StringValue,
Extent2.DateValue,
Extent2.IsActive,
Extent2.EntityBId
FROM BlogEntries AS Extent2
WHERE Extent1.ID = Extent2.AccountID) AS Project2) AS EntityBId,
(SELECT
(SELECT
Extent3.Id
FROM EntityBs AS Extent3
WHERE ((Extent3.IsDeleted = @DynamicFilterParam_000001) OR (@DynamicFilterParam_000002 IS NOT NULL)) AND (Project2.EntityBId = Extent3.Id) LIMIT 1) AS ID1
FROM (SELECT
Extent2.ID,
Extent2.AccountID,
Extent2.Body,
Extent2.IsDeleted,
Extent2.IntValue,
Extent2.StringValue,
Extent2.DateValue,
Extent2.IsActive,
Extent2.EntityBId
FROM BlogEntries AS Extent2
WHERE Extent1.ID = Extent2.AccountID) AS Project2) AS ID2,
(SELECT
(SELECT
Extent3.IsDeleted
FROM EntityBs AS Extent3
WHERE ((Extent3.IsDeleted = @DynamicFilterParam_000001) OR (@DynamicFilterParam_000002 IS NOT NULL)) AND (Project2.EntityBId = Extent3.Id) LIMIT 1) AS ISDELETED1
FROM (SELECT
Extent2.ID,
Extent2.AccountID,
Extent2.Body,
Extent2.IsDeleted,
Extent2.IntValue,
Extent2.StringValue,
Extent2.DateValue,
Extent2.IsActive,
Extent2.EntityBId
FROM BlogEntries AS Extent2
WHERE Extent1.ID = Extent2.AccountID) AS Project2) AS ISDELETED1
FROM (SELECT
@DynamicFilterParam_000001 AS DynamicFilterParam_000001,
@DynamicFilterParam_000002 AS DynamicFilterParam_000002,
Extent1.ID,
Extent1.UserName,
Extent1.RemappedDBProp,
Extent1.Discriminator,
1 AS C1
FROM Accounts AS Extent1
WHERE (Extent1.Discriminator = @gp1) OR (Extent1.Discriminator = @Gp2) LIMIT 1) AS Limit1) AS Apply2) AS Project3
ORDER BY
Project3.ID ASC,
Project3.C2 ASC

-- DynamicFilterParam_000001: 'null' (Type = Byte, IsNullable = false)

-- DynamicFilterParam_000002: 'null' (Type = Byte)

-- @gp1: 'DerivedAccount' (Type = String, IsNullable = false, Size = 14)

-- @Gp2: 'Account' (Type = String, IsNullable = false, Size = 7)

-- Executing at 11-11-2021 16:07:16 +05:30

-- Failed in 12 ms with error: Unknown column 'Extent1.ID' in 'where clause'

Closed connection at 11-11-2021 16:07:16 +05:30

[Sankartest]

It will be really helpful if we get a solution for this issue. We are eagerly looking forward for your reply.

[JonathanMagnan]

Hello @Sankartest ,

My developer will try to reproduce the issue with your code.

Best Regards,

Jon

---

Sponsorship
Help us improve this library

Performance Libraries
context.BulkInsert(list, options => options.BatchSize = 1000);
Entity Framework Extensions • Bulk Operations • Dapper Plus

Runtime Evaluation
Eval.Execute("x + y", new {x = 1, y = 2}); // return 3
C# Eval Function • SQL Eval Function

[rshaiju]

I have also seen the same issue in my project. It has been working perfectly with SQL Server but is pretty much unusable with MySQL due to this.

[Sankartest]

@JonathanMagnan ,

It would be helpful if we get any update on this.

Thanks
Sankar S

[rshaiju]

I see the error vanish on commenting out the following lines in DynamicFilterQueryVisitorCSpace

[JonathanMagnan]

Thank you @rshaiju ,

We will add an option that will remove those lines if we find out that it makes sense. Not sure how we will name it but at least it will apply your fix and maybe some other fix you find without impacting other people.

[rshaiju]

Sorry. Please keep this open for now. It worked in my POC project but gave the same Unable to cast object of type 'MySql.Data.Entity.SelectStatement' to type 'MySql.Data.Entity.LiteralFragment' with in my real application. still investigating...

[JonathanMagnan]

Sure, we will wait for you