Throw when using relational FromSqlRaw with Cosmos, or vice-versa #26502
Comments
|
Yeah, that's intentional - both Relational and Cosmos have their own versions of FromSqlRaw, and they don't recognize each other's version (so e.g. the relational FromSqlRaw wouldn't be recognized by Cosmos). Making them ambiguous as above forces the user to explicitly specify which one they want, which prevents confusion etc. |
|
So are you saying that a relational database should use the var options = this.GetCosmosDbOptions<CosmosDbContext>();
using var context = new CosmosDbContext(options);
//... other code left out
var list = await CosmosQueryableExtensions.FromSqlRaw(context.Books,
"SELECT * FROM c ORDER BY c.BookId").ToListAsync();Stacktrace for the failed versionWhen using The information is the same at the initial issue comment. |
Yes.
Can you share your full model configuration (or ideally the project)? |
Don't think that is valid SQL for cosmos unless the container name is "c". The error message also indicates that it was bad query sent to CosmosDb. |
|
Thanks @smitpatel, I didn't look close enough into the error. I took out the 'ORDER BY c.BookId However an Cosmos DB query of SELECT c
FROM (
SELECT * FROM c ORDER BY c.BookId
) c
WHERE (c["Discriminator"] = "CosmosBook")I then added the SELECT c
FROM (
SELECT * FROM c ORDER BY c.YearPublished
) cOne last thing: If I use NOTE: You can find the unit test TestFromSqlRaw, with the |
|
This line should do exact type match. |
|
Note from triage: using the relational method should throw when used on Cosmos. However, not sure if this is safe to patch. |
ajcvickers
changed the title
|
This will be breaking change not just for this but anything which is custom query root and being used with diff provider. e.g. Using temporal AsOf operator in query doesn't throw currently if used on non-SqlServer provider. |
Query root design Core exposes QueryRootExpression which is base class for any query root so we can access EntityType out of it. This is needed for nav expansion and any potential future use case. Since any derived query root would derive from it, core assembly translates it only when type is exact match to QueryRootExpression. Relational layer also processes QueryRootExpression when they are mapped to default SqlQuery, which also uses exact type match now. Apart from QueryRootExpression, no other kind of query root which can appear in different providers should be derivable (else they need to use exact type match too). This rule makes relational ones sealed class. In case any one needs to derive from it, they need to add additional processing anyway. Provider specific derived query roots can be non-sealed. If anyone is deriving from it then they should be using their derived provider which process those nodes too and if the derived provider wasn't used and shipped provider is used then it is an error from user perspective. If derived query root is used on other provider (targeting diff database) then it will fail since even the base shipped query root is unknown. Resolves #26502
smitpatel
added
the
closed-fixed
Query root design Core exposes QueryRootExpression which is base class for any query root so we can access EntityType out of it. This is needed for nav expansion and any potential future use case. Since any derived query root would derive from it, core assembly translates it only when type is exact match to QueryRootExpression. Relational layer also processes QueryRootExpression when they are mapped to default SqlQuery, which also uses exact type match now. Apart from QueryRootExpression, no other kind of query root which can appear in different providers should be derivable (else they need to use exact type match too). This rule makes relational ones sealed class. In case any one needs to derive from it, they need to add additional processing anyway. Provider specific derived query roots can be non-sealed. If anyone is deriving from it then they should be using their derived provider which process those nodes too and if the derived provider wasn't used and shipped provider is used then it is an error from user perspective. If derived query root is used on other provider (targeting diff database) then it will fail since even the base shipped query root is unknown. Resolves #26502
Query root design Core exposes QueryRootExpression which is base class for any query root so we can access EntityType out of it. This is needed for nav expansion and any potential future use case. Since any derived query root would derive from it, core assembly translates it only when type is exact match to QueryRootExpression. Relational layer also processes QueryRootExpression when they are mapped to default SqlQuery, which also uses exact type match now. Apart from QueryRootExpression, no other kind of query root which can appear in different providers should be derivable (else they need to use exact type match too). This rule makes relational ones sealed class. In case any one needs to derive from it, they need to add additional processing anyway. Provider specific derived query roots can be non-sealed. If anyone is deriving from it then they should be using their derived provider which process those nodes too and if the derived provider wasn't used and shipped provider is used then it is an error from user perspective. If derived query root is used on other provider (targeting diff database) then it will fail since even the base shipped query root is unknown. Resolves #26502
|
I would also suggest that using Not sure what you can do but the current Cosmos |
Query root design Core exposes QueryRootExpression which is base class for any query root so we can access EntityType out of it. This is needed for nav expansion and any potential future use case. Since any derived query root would derive from it, core assembly translates it only when type is exact match to QueryRootExpression. Relational layer also processes QueryRootExpression when they are mapped to default SqlQuery, which also uses exact type match now. Apart from QueryRootExpression, no other kind of query root which can appear in different providers should be derivable (else they need to use exact type match too). This rule makes relational ones sealed class. In case any one needs to derive from it, they need to add additional processing anyway. Provider specific derived query roots can be non-sealed. If anyone is deriving from it then they should be using their derived provider which process those nodes too and if the derived provider wasn't used and shipped provider is used then it is an error from user perspective. If derived query root is used on other provider (targeting diff database) then it will fail since even the base shipped query root is unknown. Resolves #26502
How do I explicitly specify CosmosQueryableExtensions in my project that uses both the SQL Server provider and the CosmosDB provider in the same project. Is there something in my using clause I can do to make it pick the right one? |
|
FromSql is an extension method in RelationalQueryableExtensions and CosmosQueryableExtensions, so it can be invoked explicitly as follows: var result = RelationalQueryableExtensions.FromSqlRaw(ctx.Blogs, "SELECT ...").ToList();
var result = CosmosQueryableExtensions.FromSqlRaw(ctx.Blogs, "SELECT ...").ToList(); |
Thanks so much! I was getting tripped up on calling it from DBSet and forgot you can pass it into the menthod as well. |
This comment was marked as off-topic.
This comment was marked as off-topic.
This comment was marked as off-topic.
This comment was marked as off-topic.
In NET6 EF Core I get an compile error if I use the method
FromSqlRaw\FromSqlRawAsyncwhen both of the following NuGet Packages are installed:The error is:
Example code
Its easy to get around by adding the namespace before the
FromSqlRaw, e.g.Include provider and version information
EF Core version: 6.0.0-rc.2.21480.5
Database provider: (various, Microsoft.EntityFrameworkCore.Sqlite)
Target framework: (e.g. .NET 6.0.rc2)
Operating system: Window
IDE: (e.g. Version 17.0.0 Preview 7.0)
The text was updated successfully, but these errors were encountered: