Sql translation issue in FromSqlInterpolated with CTE and where clause.

[michaeljfarr]

When running a CTE in FromSqlInterpolated or FromSqlRaw with a filter with efcore 3.0.0 we receive an SQL syntax error. It appears the translated SQL uses the CTE as a subquery (which is not supported on SqlServer).

The release notes indicated that efcore 3.0 FromSql methods might not translate as many things correctly. It provided some workarounds which we are currently using. Please let us know if we can expect this CTE translation to be supported again in the future or if an even better CTE solution will become available.

Steps to reproduce

Run the code here, it should throw a SqlClient.SqlExceptionon on 'var all = fromSql.Where(a=>a.Id == childId).ToList();'
https://github.com/michaeljfarr/GuidInProjection

Got Exceptions? Include both the message and the stack trace
-->
Microsoft.Data.SqlClient.SqlException
HResult=0x80131904
Message=Incorrect syntax near the keyword 'with'.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Incorrect syntax near ')'.
Source=Core Microsoft SqlClient Data Provider
StackTrace:
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader() at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject) at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.QueryingEnumerable1.Enumerator.MoveNext()
at System.Collections.Generic.List1..ctor(IEnumerable1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at GuidInProjection.Program.Main(String[] args) in C:\Users\Micha\Source\Repos\GuidInProjection2\GuidInProjection\Program.cs:line 47

Further technical details

EF Core version:
Database provider: (Microsoft.EntityFrameworkCore.SqlServer)
Target framework: (.NET Core 3.0)
Operating system: Windows 10 Pro
IDE: (Visual Studio 2019 16.3)

[ajcvickers]

@smitpatel to find duplicate

[smitpatel]

EF Core does not detect composability of raw sql and tries to compose over. See https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/breaking-changes#fromsqlsproc

As for specific support to translate SQL with CTE to server, see #4976