Re-introduce detection for non-composable SQL in FromSql

[Julien-Marpault]

.Net core 3 preview 8

  HomeCarouselViewModel viewModel = await _context.Set<HomeCarouselViewModel>().FromSqlRaw("EXEC GetHomeCarousel").AsNoTracking().FirstOrDefaultAsync(); 

I get this exception:
Incorrect syntax near the keyword 'EXEC'

Worked perfectly with ef core 2.2

[ErikEJ]

Maybe need to use dbo.GetHomeCarousel

[Julien-Marpault]

No it doesn't change anything.

Thanks.

[ajcvickers]

Notes from triage: The code to detect non-composable SQL was removed in 3.0, since ideally we would not parse any SQL. However, if this catches enough people out, then we will consider re-introducing it. Putting this in backlog to gather votes/feedback.

[Julien-Marpault]

Thanks,
Since you do that, how to use stored procedure through EF ?

[ajcvickers]

Re-marking for triage. What is the composition here? I assume it's FirstOrDefault? But then I'm left thinking that FirstOrDefault is not an unreasonable thing to "client-eval" here, since it means take the first result from the reader, if there is one, and return it.

[ethos-tim]

I am porting and running into this with FirstOrDefault AND Select on a FromSqlRaw and FromSqlInterpolated it is trying to add a SELECT TOP(1) or change the column list ON TOP of my stored procedure. Adding an AsEnumerable or ToList before the FirstOrDefault or Select fixes the issue - however it was very frustrating to find and I had to run SQL Profiler to figure it out. The error it was throwing was Incorrect syntax near '@p0'. Perhaps have a flag that allows the user to indicate "Don't mess with my SQL - just run it as is". I can create a repo if needed.

[ethos-tim]

Thanks,
Since you do that, how to use stored procedure through EF ?

Try:

HomeCarouselViewModel viewModel = await _context.Set<HomeCarouselViewModel>()
.FromSqlRaw("EXEC GetHomeCarousel")
.AsNoTracking()
.AsEnumerable()
.FirstOrDefaultAsync(); 

[smitpatel]

"Don't mess with my SQL - just run it as is".

There is very easy way to do this.
_context.Set<T>().FromSqlRaw(.....).AsEnumerable()
Put AsEnumerable right after FromSqlRaw and EF will not try to do any composition.

[Julien-Marpault]

Thanks.
In effect it works:

 HomeCarouselViewModel viewModel =  _context.HomeCarouselViewModel.FromSqlRaw("EXECUTE GetHomeCarousel").AsNoTracking().AsEnumerable().FirstOrDefault();

But it's synchronous. In order to use Asynchronous operation I Thought I could use AsEnumerableAsync() but

    HomeCarouselViewModel viewModel = _context.HomeCarouselViewModel.FromSqlRaw("EXECUTE GetHomeCarousel").AsNoTracking().AsAsyncEnumerable().GetAsyncEnumerator().Current;

returns null.

[ErikEJ]

Did you forget await ?

[smitpatel]

Step-by-step

• AsAsyncEnumerable() gives you IAsyncEnumerable<>, an async enumerable
• GetAsyncEnumerator() gives you IAsyncEnumerator<>, an async enumerator over previous enumerable
• Calling into Current will give you current element in enumerator but before you call into Current you need to call MoveNextAsync.

You have initialized your enumerator but haven't moved it yet so Current is null (or default value).

[thassan1977]

this worked for me just fine

string sqlQuery = "EXEC [dbo].[PaperBatch_INSERT] @UserName,@SupplierID,@OrDerno,@RecvDat,@FROMno,@tono,@notes";

resp = this.SPResponse.FromSqlRaw(sqlQuery,
p_UserName, p_SupplierID,
p_OrderNo, p_RecvDat,
p_FromNo, p_ToNo, p_Notes)
.AsNoTracking().AsEnumerable().FirstOrDefault();

[lixaotec]

Re-marking for triage. What is the composition here? I assume it's FirstOrDefault? But then I'm left thinking that FirstOrDefault is not an unreasonable thing to "client-eval" here, since it means take the first result from the reader, if there is one, and return it.

It should works like that. It seem so odd to workaround with AsEnumarable.