IncludeFilter does not work in EF Core and Oracle

[KKica]

Description

I have a model "Checklist" and "ChecklistQuestion" where 1 Checklist has many ChecklistQuestions. I am using EF Core and Oracle. I want to use IncludeFilter to filter ChecklistQuestions. The code I am using context.Checklists.IncludeFilter(a => a.ChecklistQuestions).ToList();

Exception

Exception message: Unable to cast object of type 'Z.EntityFramework.Plus.CreateEntityCommand' to type 'Oracle.ManagedDataAccess.Client.OracleCommand'

Further technical details

• EF version: EDCORE 2.2.6. Oracle.EntityFrameworkCore 2.19.30
• EF Plus version: 2.0.8
• Database Provider: Oracle

Fiddle is not working. Code Below.

EDIT: This code uses SqlServer and EF. The problem is with Oracle and EF Core

// @nuget: EntityFramework
// @nuget: Z.EntityFramework.Plus.EF6

using System;
using System.Linq;
using System.Data.Entity;
using Z.EntityFramework.Plus;
using System.Collections.Generic;

public class Program
{
	public static void Main()
	{	
		// ADD seed data here
		using (var context = new EntityContext())
		{
			var checklist= new Checklist();
			
			var checklistQuestions= new ChecklistQuestion(){Checklist=checklist };
			context.Checklists.Add(checklist);
			context.SaveChanges();
			
			FiddleHelper.WriteTable("1 - Before", context.Checklists);
		}
		
		// ADD code to reproduce the issue here
		using (var context = new EntityContext())
		{
			var result= context.Checklists.IncludeFilter(a => a.ChecklistQuestions).ToList();
			//Console.Writeline(result.Id);
		}
		
		using(var context = new EntityContext())
		{
			FiddleHelper.WriteTable("2 - After", context.Checklists);
		}
	}
		 
	public class EntityContext : DbContext
	{
		public EntityContext() : base(FiddleHelper.GetConnectionStringSqlServer())
		{

		}
		
		public DbSet<Checklist> Checklists { get; set; }
		public DbSet<ChecklistQuestion> ChecklistQuestions { get; set; }
	}
	
	
	public class Checklist
	{
		public int ID { get; set; }
		public List<ChecklistQuestion> ChecklistQuestions { get; set; }
	}

    public class ChecklistQuestion
	{
		public int ID { get; set; }
		public int CheklistId { get; set; }
		public Checklist Checklist { get; set; }
	}
}

[JonathanMagnan]

Related to: https://stackoverflow.com/questions/58224449/invalid-parameter-binding-with-entity-framork-core-plus-in-includefilter-for-ora

[JonathanMagnan]

Hello @KKica ,

Thank you for reporting,

We will look at it.

Best Regards,

Jonathan

---

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

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

[JonathanMagnan]

Hello @KKica ,

We successfully reproduced the issue but unfortunately didn't find a fix yet.

However, you can avoid this issue by disabling the QueryBatch (so multiple queries will be send) with the following code:

QueryIncludeFilterManager.AllowQueryBatch = false;

Your code should now work with the query batch disabled.

Let me know if this workaround solution is a good one for you.

Best Regards,

Jonathan

[KKica]

Hello @JonathanMagnan Thank you for the quick response,

Is this fixable in a short amount of time? Can you provide me with some more information rearding the performance loss? Will it be like: 1- Send an extra join statement, with a filter and then attach the data to the result of the first query without include? (So 1 extra query in the db in total) 2- For each checklist, you send a query to get all the checklistQuestions and then attach. (so n extra queries in the db in total, where n is the number of checklists)

Furthermore, will I be able to use these data to update or add in the result by using

checklist.Add(newCheklistQuestion);
checklist.Property= newValue;
context.Checklists.Update(checklist);
context.SaveChanges();

In the same way I am able to do it without EF-Plus?

[JonathanMagnan]

Hello @KKica ,

Is this fixable in a short amount of time?

If it was, we would have fixed it yesterday ;) We took some hour during the weekend but didn't find how to fix it correctly.

One problem is the library is free, so paid requests always take the priority over it.

Finding a fix can take 5min or 5h, this problem is not an easy one to find how to solve it.

Can you provide me with some more information rearding the performance loss?

This is more the situation 1, for example if you use one IncludeFilter, 2 queries is created to matter the amount of checklist or checklistquestions. When batching is enabled, only one SQL is executed (with both queries), when disabled, two queries is executed.

So the performance lost is very low but still exists.

Furthermore, will I be able to use these data to update or add in the result by using

Of course, just try it and you will see ;)

[JonathanMagnan]

Hello @KKica ,

Unfortunately, unless we re-write this feature in another way or add a dependencies to Oracle (which cannot happen), that's currently impossible to support it for Oracle.

To make this feature work, we need to inject our command.

However, in the Oracle library, in the CreateCommand method, the following line causes all this trouble:

(OracleCommand)command).BindByName = true;

It tries to cast our injected command into an OracleCommand which throws the error.

We currently have no solution for this at this moment.