Unable to cast object of type 'BlazarTech.QueryableValues.DeferredInt32Values' to type 'System.String'.

[jasenf]

Hi,

Started using this library, thank you for writing it!

When we have non-performant queries our production logging services try to record the query using EFCore's .ToQueryString() method. Once we add QueryableValues to the query however, any attempt to view the generated SQL fail with this exception. This fails for .ToQueryString() and within the debugger's DebugView.Query property as well.

take care
jasen

[yv989c]

Hi @jasenf, I'm happy that it's helpful!

I remember encountering this issue while working on this project, unfortunately I believe that error is on EF side.

If I'm not mistaken, the ToQueryString method fails anytime we make use of FromSqlRaw, which this library uses behind the scenes.

If what I said above is correct, you could file an issue in the EF repository to get a fix for it.

Please let me know your thoughts.

[jasenf]

Asking the EFCore team to fix a v6 issue so a third party library isn't going to give me any kind of timely (if any) solution.

I guess if an app needs to get the sql string at any time this library isn't an option unfortunately. I had hoped there would just be a way to provide the proper explicit casting for the type, but I couldn't get it to work in your code, was thinking maybe I was doing something wrong.

[yv989c]

So you are using EF Core v6? have you checked if this is fixed in v7?

Unfortunately, it's not about this library but their own API (ToQueryString) that refuses to work with a query that makes use of FromSqlRaw. You will get the same outcome with or without this library if you happen to use FromSqlRaw (take this with a grain of salt. I'll have to check to confirm).

I see that for good reason I made DeferredInt32Values an internal class. I'm wondering if making it public will make ToQueryString happy.

[jasenf]

Hi there,

First - thanks for the quick convo :-)

We can't use 7 quite yet. It's a huge production app and aren't upgrading until Q2.

We use ToQueryString all the time to record any bad performing queries, so I'm not sure that's the problem (i.e. this just started and only happens with LINQ queries where we use the Blazar methods. I was going to just add the implicit type conversion and submit a PR for you but like I said, that didn't work. It didn't occur to me that the method was an internal class, so perhaps it is an access issue.

If the error weren't specifically referencing this class, I would perhaps agree that maybe its a EFCore thing, but because the exception is an attempt to grab a string value from DeferredInt32Values I honestly think it's some lack of support from within here. Wish I could have had the time to just fix it for you, this is a great library and fixes a huge problem we have in production ATM.

[yv989c]

Sure no problem. I would also like ToQueryString to work because I see how valuable it's.

If the error weren't specifically referencing this class, I would perhaps agree that maybe its a EFCore thing, but because the exception is an attempt to grab a string value from DeferredInt32Values I honestly think it's some lack of support from within here. Wish I could have had the time to just fix it for you, this is a great library and fixes a huge problem we have in production ATM.

Thanks. I didn't see it that way.

Would you mind confirming my suspicions? To see if I can really do anything about this just try this:
In your DbContext, take an existing entity, and write a select SQL statement retrieving some of its properties from the matching table, then pass that SQL query to the FromSqlRaw method, and then do ToQueryString. If it works then something can be done on this side, otherwise 😔 (or maybe there's still hope).

var queryText = dbContext
    .Set<MyEntity>()
    .FromSqlRaw("SELECT TOP 10 Field1 FROM dbo.MyTable")
    .ToQueryString();

[jasenf]

Ok, sooooo..

evaluating

_context.Set().FromSqlRaw("select top 10 id from Post").ToQueryString()

works, no problem, I get the proper SQL string in the result.

[yv989c]

Great. So it can be fixed 🙂. I'll keep you posted.

Thanks a lot for the feedback.

[jasenf]

I should have posted this before, but I don't get a great stack trace (given that it's going thru EF and then to the library), but what I do get is this:

   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerStringTypeMapping.GenerateNonNullSqlLiteral(Object value)
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerQueryStringFactory.Create(DbCommand command)
   at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.ToQueryString()

Hope that helps!

[yv989c]

👋 @jasenf. After some exploration and debugging I ended up with a workaround. It's a compromise but may be ok in your use case(s). The current reality is that EF is doing some gymnastics in order to give us a picture of the SQL that will end up being sent to the server. There are some assumptions made by the underlying logic of ToQueryString that are not compatible with the strategy I'm using to leverage deferred enumeration, causing this problem.

For details please take a look at the release notes. NuGet should be updated in a few minutes.

Please let me know how it goes.

[jasenf]

Love it man, thanks!

I messed around with it for another hour or so yesterday, taking your lead and hoping just changing all your classes/methods to public would do the trick. No love.

This worked great, just tested it this morning! Thanks for the quick turnaround time, wasn't expected but much appreciated.

jasen

[yv989c]

I'm glad! 🙂

When I get a chance I'm going to create a detailed issue in the efcore repo so they are aware of this kinda corner case.

Have a good one.

[yv989c]

Caused by dotnet/efcore#30515