1

I noticed this bug the other day, and reconfirmed it today. Also found others having this issue.

Entity Framework will return no results (when it ought to) and will not throw an exception in the following scenario:

  • Execute a stored proc using the SqlQuery method
  • Provide at least one optional parameter
  • Skip at least one optional parameter

By "Skip" I'm referring to the order of the parameters in the proc's signature. As long as you don't skip any optional parameters there is no problem; it's only when you've provided an optional parameter that comes after another one that you didn't include.

There could be other variants. For example, I suspect the same issue would occur if you provide a required parameter that comes after an optional parameter that you've skipped... not sure about that though.

Now consider this scenario:

I add a new optional parameter to the end of a proc's signature. Now I go to update a SqlQuery() call and add the new parameter but b/c of aforementioned bug I get no results. I have two options: 1) I can add all the optional parameters that I had left out. Or 2) I can move the new parameter to be further to the left in the signature so that I'm no longer skipping any parameters in my SqlQuery() call.

The problem with 2), of course, is that this could break existing code that is now skipping that parameter. So, really, my only option seems to be to just include all parameters all the time which totally defeats the entire purpose of having optional parameters in the first place.

Is there any way to remedy this or am I just stuck having to always pass in all parameters if I use Entity Framework to call stored procs?

EDIT: Another potential problem I see with this is that it disallows me from letting SQL use a default value because there is no 'value' you can pass to SQL to tell it to use the default. You tell it to use the default by NOT passing a value... which EF doesn't not seem to handle properly.

EDIT 2:

Minimal reproducable code:

CREATE PROC MyProc @p1 int, @p2 int = 0, @p3 int = 0
as
select * from MyTable 
where  p1 = @p1 
       and (@p2 = 0 or p2 = @p2)
       and (@p3 = 0 or p3 = @p3)

C# Code:

List<MyObject> SomeMethod(int p1, int p3)
{
    return db.Database.SqlQuery<MyObject>("dbo.MyProc @p1, @p3",
         new SqlParameter("@p1", p1),
         new SqlParameter("@p3", p3)
     ).ToList();
}
BVernon
  • 3,205
  • 5
  • 28
  • 64
  • Please add a [mcve] which shows the problem when executed. – Progman Jun 07 '21 at 22:52
  • @Progman See edit. – BVernon Jun 07 '21 at 23:13
  • And note that if you swap the positions of the `@p2` and `@p3` parameters in the signature then the code will work as expected... which is an incredibly clear indication that this is a bug. – BVernon Jun 07 '21 at 23:27
  • 2
    What do you see in the SQL Server profiler? I'm doubtful this is an EF bug, but more to do with how SQL Server handles parameters with `EXEC` (**which you should not be using** - you should be using `sp_executesql` for dynamic SQL, or just the raw sproc name with `SqlQuery`). See here: https://stackoverflow.com/questions/4873607/how-to-use-dbcontext-database-sqlquerytelementsql-params-with-stored-proced - you only need `EXEC` if you're targeting SQL Server 2005. – Dai Jun 08 '21 at 16:11
  • @BVernon Are there any differences when you use `ExecuteFunction<>` instead of `SQLQuery<>`? – Progman Jun 08 '21 at 16:13
  • @Dai I appreciate your comments on how I should be doing it, however the person I'm working for is a little particular about writing code the way he does and that's just how he does it. That's not really a discussion I care to have as it's a waste of time (for me personally in my current environment) that keeps me from being productive. While it might not be the "proper" way to do it, it _should_ work. It's not syntactically wrong or anything like that. – BVernon Jun 08 '21 at 17:04
  • @Dai There is one simple change that will cause EF to return results. If I just swap the order of the optional parameters in the signature so that my call isn't skipping over any parameters then it works. How can you possibly say that's not a bug? – BVernon Jun 08 '21 at 17:07
  • @Dai Actually I don't want the "exec" to be a distraction for you and it still doesn't work without it, so I've removed it from the example. – BVernon Jun 08 '21 at 17:09
  • 1
    @BVernon Heh - after having worked for some pretty awful people myself, you have my sympathies :) - but anyway, we really need those SQL Profiler traces in order to get to the bottom of this. – Dai Jun 08 '21 at 17:10
  • @Dai Okay... I'll get them when I can, thanks. – BVernon Jun 08 '21 at 17:11
  • 2
    What you have there is a call using *positional parameters*. `dbo.MyProc @p1, @p3` calls `MyProc` while supplying values for the *first two parameters*. If you wanted a call by *name*, you would have to write `dbo.MyProc @p1 = @p1, @p3 = @p3`. Note that the names of the sproc parameters and the names used in the parameterized call bear *no relationship* to each other. This is the difference between calling an sproc as an RPC call (using `SqlCommand.CommandType = StoredProcedure`) and using an `EXEC` statement to call it (as done here). – Jeroen Mostert Jun 08 '21 at 17:33
  • 2
    "EDIT: Another potential problem I see with this is that it disallows me from letting SQL use a default value because there is no 'value' you can pass to SQL to tell it to use the default." No, but there is a keyword: `default`. `dbo.MyProc @p1, default, @p3` achieves the same effect (but, again, it would work the same if it was `dbo.MyProc @banana, default, @apple`). – Jeroen Mostert Jun 08 '21 at 17:37
  • 2
    And just to be clear: this is not a bug in EF, this is how T-SQL handles parameters, and it works this way regardless of what ORM you use (including none; you will be able to replicate this with raw `SqlCommand`s if you use `CommandType.Text`). The person who likes to do things a particular way will have to accept that however they thought parameters work is not the way parameters work, so the approach will need to change one way or another. – Jeroen Mostert Jun 08 '21 at 17:50
  • @JeroenMostert I know about the "default" keyword. Can you tell me how to pass that keyword in using Entity Framework? If not then my point remains, right? – BVernon Jun 08 '21 at 18:39
  • @JeroenMostert I can run it in Management Studio and get results just fine. I haven't tried, but I bet I could do it with straight ADO.net code also. Regardless of where the bug is in the chain though, it is absolutely a bug. If it's a rule that you must not skip parameters, then an exception should occur. No exception occurs, therefore I expected proper results. – BVernon Jun 08 '21 at 18:45
  • @JeroenMostert I understand, however, that we may have a difference in opinion as to what constitutes a bug. If you prefer, we can call it a "bug by design," or even a really crappy "feature" as some developers like to say of their bugs. Whatever it is, there is absolutely no good reason you can say it "ought" to work that way. You can only say that it "does" work that way. I still contend that it's a straight up, regular old bug though. – BVernon Jun 08 '21 at 18:49
  • 2
    I think we're talking past each other -- the scenario I'm talking about is demonstrated [here](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=6ac374603faa256c2b6eade964ac8077), with the difference being that I'm using T-SQL variables instead of parameters, and an explicit `EXEC` (the `EXEC` is allowed to be implicit if it's the only statement in a batch). How do you pass `default` using EF? Why, you just *do*: `return db.Database.SqlQuery("dbo.MyProc @p1, default, @p3", new SqlParameter("@p1", p1), SqlParameter("@p3", p3)).ToList();`. – Jeroen Mostert Jun 08 '21 at 18:57
  • 1
    You are of course free to call it a bug -- but then you'll have to call it a bug in SQL Server, not EF. The rule definitely is *not* "you must not skip parameters", the rule is "the syntax `proc [value], [value]` assigns parameter values by position and `proc @param1 = [value], @param2 = [value]` assigns parameter values by name". Your call is perfectly valid either way so an exception would not be expected; it's just not assigning parameters the way you think it is. – Jeroen Mostert Jun 08 '21 at 18:59
  • 3
    If you take away anything from the above, it arguably should be this: if you're not using the RPC mechanism to call stored procedures (`SqlCommand.CommandType = CommandType.StoredProcedure`, or whatever the equivalent of the ORM is if it has one), always be explicit and use both `EXEC` and parameter names, even if it looks redundant (`EXEC proc @p1 = @p1, @p2 = @p2, ...`) Do not ever rely on the positional version, because it's needlessly brittle -- I suspect the above only does so by accident, not by design. – Jeroen Mostert Jun 08 '21 at 19:55
  • @JeroenMostert Oh Gees Louise... I see it now. The data access api I used to use didn't require writing the parameters out in the exec string. I just passed the proc name and SqlParameter's and it took care of the rest. This whole time I'm thinking the SqlParameter names indicated the parameter I wanted to pass in,,and having to write them in the exec string seemed redundant. Now I see they're arbitrary names and I'm passing them positionally and **THAT** is the whole problem. I didn't even realize I was passing them in positionally. Hope that makes sense, but in any case thanks for your help. – BVernon Jun 09 '21 at 01:09

0 Answers0