Efcore: FromSqlRaw / FromSqlInterpolated with stored procs

[RoySalisbury]

In EF Core 2.1, you could use the Query< T > and FromSql to return results form stored procedures. I used it as follows:

public virtual async Task<RawModels.WeatherRecordHighLowSummary> GetWeatherRecordHighLowSummary(DateTimeOffset startRecordDateTime, DateTimeOffset endRecordDateTime)
{
    var p = new System.Data.SqlClient.SqlParameter[]
    {
        new System.Data.SqlClient.SqlParameter("@iStartRecordDateTime", System.Data.SqlDbType.DateTimeOffset) { Value = startRecordDateTime },
        new System.Data.SqlClient.SqlParameter("@iEndRecordDateTime", System.Data.SqlDbType.DateTimeOffset) { Value = endRecordDateTime }
    };

    return await this.Query<RawModels.WeatherRecordHighLowSummary>().FromSql("sp__GetWeatherRecordHighLowSummary @iStartRecordDateTime, @iEndRecordDateTime", p)
                .AsNoTracking()
                .SingleOrDefaultAsync();
}

However, in EF Core 3.0, the Query was replaced with Entity< T > and marking them with "HasNoKey()". Then instead of using Query< T >, you use Set< T > just like any other entity. So the code above would translate to:

return await this.Set<RawModels.WeatherRecordHighLowSummary>().FromSqlRaw("sp__GetWeatherRecordHighLowSummary @iStartRecordDateTime, @iEndRecordDateTime", p)
                .AsNoTracking()
                .SingleOrDefaultAsync();

That does not work, as I get SqlParamaterCollection issues. So changing it to:

return await this.Set<RawModels.WeatherRecordHighLowSummary>().FromSqlRaw("sp__GetWeatherRecordHighLowSummary @p0, @p1", startRecordDateTime, endRecordDateTime)
                .AsNoTracking()
                .SingleOrDefaultAsync();

That gets past the SqlParameter issue, but now there is an error of:

Microsoft.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '@p0

So I figured perhaps I need the 'EXEC' keyword (not needed before). That just gives invalid T-SQL. It seems the query is turned into the following invalid T-SQL:

SELECT TOP(2) [w].[BarometerHigh], [w].[BarometerHighDateTime], [w].[BarometerLow]
FROM (
EXEC sp__GetWeatherRecordHighLowSummary @p0, @p1
) AS [w]

Thats just wrong T-SQL. You can't select from a SP like that.

So, the question is, how do I get the functionality from EF 2.1 in EF 3.0?

[smitpatel]

In 2.1 SingleOrDefault was evaluated on client. 3.0 does not support client evaluation exception in top level projection. See https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/breaking-changes#linq-queries-are-no-longer-evaluated-on-the-client

Because there is not client eval, it would throw error. Since, parsing SQL is error-prone, as per https://github.com/aspnet/EntityFrameworkCore/issues/15392 we decided stop trying to identify SQL composability. Now we generate SQL translate for whole expression tree. If it generates invalid SQL then your query cannot be translated to server.

The breaking change documentation links has few work-arounds.

[RoySalisbury]

OK.. I must have missed that. Spent too much time thinking it was related to the FromSqlRaw/FromSqlInterpolated changes.

Thanks.

FYI.. This is what I ended up with:

public virtual async Task<RawModels.WeatherRecordHighLowSummary> GetWeatherRecordHighLowSummary(DateTimeOffset startRecordDateTime, DateTimeOffset endRecordDateTime)
{
    var result = await this.Set<RawModels.WeatherRecordHighLowSummary>().FromSqlInterpolated($"sp__GetWeatherRecordHighLowSummary {startRecordDateTime}, {endRecordDateTime}")
                .AsNoTracking()
                .ToListAsync();

    return result.SingleOrDefault();
}

[MicroAsif]

@RoySalisbury I was also migrating 2.2 stored procedure to 3.0 and faced the same problem.
Thanks for saving me.

[robinwilson16]

@RoySalisbury Thanks so much for posting the solution. Wish the migration guide covered this stuff.
It's especially confusing that this now generates invalid SQL.

I have put together a guide (mainly for myself) of all the issues I have hit trying to migrate here (and added this one): aspnet/AspNetCore.Docs#14817

[smitpatel]

@robinwilson16 - The breaking changes already have been updated. https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/breaking-changes#fromsqlsproc

[shunjid]

OK.. I must have missed that. Spent too much time thinking it was related to the FromSqlRaw/FromSqlInterpolated changes.

Thanks.

FYI.. This is what I ended up with:

public virtual async Task<RawModels.WeatherRecordHighLowSummary> GetWeatherRecordHighLowSummary(DateTimeOffset startRecordDateTime, DateTimeOffset endRecordDateTime)
{
    var result = await this.Set<RawModels.WeatherRecordHighLowSummary>().FromSqlInterpolated($"sp__GetWeatherRecordHighLowSummary {startRecordDateTime}, {endRecordDateTime}")
                .AsNoTracking()
                .ToListAsync();

    return result.SingleOrDefault();
}

Thank you so much. Take love brother 💘

[BBanoth]

I got into the same problem even after following the MS Docs. Check if you have query filters at DbSet/Global level. If yes then add IgnoreQueryFilters() after FromSqlRaw/FromSqlInterpolated