I am using JetEntityFrameworkProvider provider for my EF6.4 for .NET Framework v4.7.2. I recently come across this update query with subquery that fails silently. It produces
This fails:
ctx.Database.ExecuteSqlCommand(
"UPDATE table SET DTRID = @p0 WHERE UserID IN(SELECT UserID FROM UserGroup WHERE GroupID = @p1)"
, 1, 2)
This also fails:
ctx.Database.ExecuteSqlCommand(
"UPDATE table SET DTRID = ? WHERE UserID IN(SELECT UserID FROM UserGroup WHERE GroupID = ?)"
, 1, 2)
But this succeeds:
ctx.Database.ExecuteSqlCommand(
String.Format("UPDATE table SET DTRID = {0} WHERE UserID IN(SELECT UserID FROM UserGroup WHERE GroupID = {1})"
, 1, 2))
Log
Both statements generate the same SQL. Neither one produces an error. The failing statements still produce valid SQL, which I can confirm in the log:
UPDATE table SET DTRID = @p0 WHERE UserID IN(SELECT UserID FROM UserGroup WHERE GroupID = @p1)
-- p0: '1' (Type = Int32, IsNullable = false)
-- p1: '2' (Type = Int32, IsNullable = false)
Parameter Format Fail?
No, because this statement succeeded:
ctx.Database.ExecuteSqlCommand("UPDATE UserDef SET DTRID = @p0 WHERE UserID = @p1", 1, 2)
Subquery?
Failing query contains subquery. Where the subquery can matter is in parameter binding order.