InMemory provider v7.x.x: FromSqlRaw throws InvalidOperationException: Query root of type 'FromSqlQueryRootExpression' wasn't handled by provider code.

[PrefixAM]

After upgrading to EFCore 7, Microsoft.EntityFrameworkCore.InMemory provider is throwing an error for 'FromSqlRaw' \ 'FromSqlInterpolated' expressions.

The same scenario is working properly in EF Core 3\5\6 and does not throw the error.

var dbContextOptions = new DbContextOptionsBuilder<SampleDbDataContext>()
	.UseInMemoryDatabase("SampleDb_in-memory-database")
	.Options;

using var dataContext = new SampleDbDataContext(dbContextOptions);

var fooBars = await dataContext.FooBars
	.FromSqlRaw
	(
		@"
		-- Real queries are more complicated, i.e. include JSON \ XML parsing,
		-- calls of aggregate or user-defined functions etc.

		SELECT
			[Id],
			[Value]
		FROM [dbo].[FooBar]
		"
	).ToListAsync();

Zip archive with a solution that reproduces the issue: https://1drv.ms/u/s!AiXcux4pieqkhfMOviZNjCeS96ZJLQ?e=1ge3cE

The code above throws the error:

System.InvalidOperationException: Query root of type 'FromSqlQueryRootExpression' wasn't handled by provider code. This issue happens when using a provider specific method on a different provider where it is not supported.
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryQueryableMethodTranslatingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)

EF Core version: v7
Database provider: Microsoft.EntityFrameworkCore.InMemory v7.x.x
Target framework: .NET 6.0
IDE: Visual Studio 2022 17.2.8

[davidroth]

The InMemoryProvider cannot evaluate sql queries, which makes sense because by definition it's not a real database.

So the exception makes total sense here and I don't know what your are expecting to change?

Did EF6 silently return an empty result in such a scenario?
If so, I'd argue the new exception behavior is favourable because it's indicating you that you are doing something wrong.

[PrefixAM]

Documentation does not mention any breaking changes in this area for In-Memory provider. So my expectation is that the behavior stays more consistent with the previous 3+ versions of the product where the error is not thrown.

Did EF6 silently return an empty result in such a scenario?

For that specific code with no entries in the context, previous versions of the provider would return an empty result.

But when needed, context can be set up, and having 'Where' conditions as expressions, the result can be controlled even though it's processed by linq2objects.

var dbContextOptions = new DbContextOptionsBuilder<SampleDbDataContext>()
	.UseInMemoryDatabase("SampleDb_in-memory-database")
	.Options;

using var dataContext = new SampleDbDataContext(dbContextOptions);

#region Set up

var existingFooBars = new[]
{
	new FooBar{ Value = "Foo"},
	new FooBar{ Value = "Bar"}
};

await dataContext.FooBars.AddRangeAsync(existingFooBars);

await dataContext.SaveChangesAsync();

#endregion

var fooBars = await dataContext.FooBars
	.FromSqlRaw
	(
		@"
		-- Real queries are more complicated, i.e. include JSON \ XML parsing,
		-- calls of aggregate or user-defined functions etc.

		SELECT
			[Id],
			[Value]
		FROM [dbo].[FooBar]
		"
	)
	.Where(f => f.Value == "Bar")
	.ToListAsync();

// Returns 1 entry

return fooBars;

In my particular case, In-Memory provider overrides a default Sql Server provider for a specific set of integration tests with TestServer \ TestClient. They check that all expected endpoints of Rest Api are available, accept properly constructed requests (no issues with DI container) and reply with the expected status codes.

So it's a quick way to execute certain tests without involving database or mocking DAL when not needed. There is no intent to test the functionality of DAL with In-Memory provider.

In my experience, 'FromSqlRaw' \ 'FromSqlInterpolated' is present in almost any product that uses EF Core, which means that starting from v7.x.x In-Memory provider loses its main benefits (in my eyes):

• ability to quickly override the main data provider
• while not throwing the errors
• and still allowing to set up some test data

If so, I'd argue the new exception behavior is favourable because it's indicating you that you are doing something wrong.

That would imply that there is a "right way" of dealing with it to avoid the error. But as of now, it seems it should be interpreted as "it's not supported anymore", which then raise a concern about mentioning this breaking change and opening a discussion about pros \ cons of a decision similarly like a discussion #18457

[ajcvickers]

@PrefixAM This probably should be documented as a breaking change. That being said, it didn't work before, was never intended to work, and I agree with @davidroth that throwing an exception is better here than silent incorrect behavior. With regard to testing, see Testing EF Core Applications.

[PrefixAM]

That being said, it didn't work before, was never intended to work, and I agree with @davidroth that throwing an exception is better here than silent incorrect behavior.

I want to quote the most upvoted comment from #18457 (comment)

We're all aware of the issues the InMemory-provider brings and because of that, we can handle them properly. The awareness of the issues is a pretty important part. But the benefits it brings to us are so much bigger - I definitly have to vote for a continued support on that side :)

I think that awareness is broken:

• historical behavior is the expressions produced by 'FromSqlRaw' \ 'FromSqlInterpolated' are ignored
• new behavior is the expressions produced by 'FromSqlRaw' \ 'FromSqlInterpolated' are throwing an exception

Would've been nice to have the ability to ignore such expressions as it was in the past, perhaps by specifying the corresponding option through InMemoryDbContextOptionsBuilder.

For example, in EF 6 In-Memory provider there was a breaking change that was solved by providing the corresponding configuration option: #10613 , Documentation

[ajcvickers]

Note from triage: the documentation for this break does not include the in-memory provider. Opened dotnet/EntityFramework.Docs#4257 to add in-memory.

[tdeepak789]

@ajcvickers can you elaborate how to fix this

Query root of type 'FromSqlQueryRootExpression' wasn't handled by provider code. This issue happens when using a provider specific method on a different provider where it is not supported."

Do I need to downgrade the EF core package version to 5 or 6 ?

If i want to continue to use EF core 7 or 8 , what could be the possible fix that I should be change.

[roji]

@tdeepak789 please see this comment. FromSqlRaw cannot work in InMemory, and has in fact never actually worked - InMemory simply isn't a SQL database.

[tdeepak789]

@roji thanks for pointing me to the right comment.

I found that exception was raised because of upgrading EF core and npgsql from version 5.0.7 to version 8.

I have downgraded back to the 5.0.7, the excpetion was not triggered.

in earlier version In memory database has ignored the exception and returned empty results.

I am okay with using older version of the EF core.

[roji]

@tdeepak789 I think you're not fully reading what I wrote (and the comment): you should not be downgrading to an older version of EF to avoid the exception, but rather, you should not be using FromSqlRaw with the InMemory provider.