private void RunBatchUpdate_UsingNavigationPropertiesThatTranslateToAnInnerQuery()
{
var testDbCommandInterceptor = new TestDbCommandInterceptor();
using (var context = new TestContext(ContextUtil.GetOptions(testDbCommandInterceptor)))
{
context.Parents.Where(parent => parent.ParentId < 5 && !string.IsNullOrEmpty(parent.Details.Notes))
.BatchUpdate(parent => new Parent {
Description = parent.Details.Notes ?? "Fallback"
});
var actualSqlExecuted = testDbCommandInterceptor.ExecutedNonQueryCommands?.LastOrDefault();
var expectedSql =
@"UPDATE p SET [p].[Description] = (
SELECT COALESCE([p1].[Notes], N'Fallback')
FROM [ParentDetail] AS [p1]
WHERE [p1].[ParentId] = [p].[ParentId])
FROM [Parent] AS [p]
LEFT JOIN [ParentDetail] AS [p0] ON [p].[ParentId] = [p0].[ParentId]
WHERE ([p].[ParentId] < 5) AND ([p0].[Notes] IS NOT NULL AND (([p0].[Notes] <> N'') OR [p0].[Notes] IS NULL))";
Assert.Equal(expectedSql.Replace("\r\n", "\n"), actualSqlExecuted.Replace("\r\n", "\n"));
context.Parents.Where(parent => parent.ParentId == 1)
.BatchUpdate(parent => new Parent {
Value = parent.Children.Where(child => child.IsEnabled).Sum(child => child.Value)
});
actualSqlExecuted = testDbCommandInterceptor.ExecutedNonQueryCommands?.LastOrDefault();
expectedSql =
@"UPDATE p SET [p].[Value] = (
SELECT SUM([c].[Value])
FROM [Child] AS [c]
WHERE ([p].[ParentId] = [c].[ParentId]) AND ([c].[IsEnabled] = CAST(1 AS bit)))
FROM [Parent] AS [p]
WHERE [p].[ParentId] = 1";
Assert.Equal(expectedSql.Replace("\r\n", "\n"), actualSqlExecuted.Replace("\r\n", "\n"));
var newValue = 5;
context.Parents.Where(parent => parent.ParentId == 1)
.BatchUpdate(parent => new Parent {
Description = parent.Children.Where(child => child.IsEnabled && child.Value == newValue).Sum(child => child.Value).ToString(),
Value = newValue
});
actualSqlExecuted = testDbCommandInterceptor.ExecutedNonQueryCommands?.LastOrDefault();
expectedSql =
@"UPDATE p SET [p].[Description] = (CONVERT(VARCHAR(100), (
SELECT SUM([c].[Value])
FROM [Child] AS [c]
WHERE ([p].[ParentId] = [c].[ParentId]) AND (([c].[IsEnabled] = CAST(1 AS bit)) AND ([c].[Value] = @__p_0))))) , [p].[Value] = @param_1
FROM [Parent] AS [p]
WHERE [p].[ParentId] = 1";
Assert.Equal(expectedSql.Replace("\r\n", "\n"), actualSqlExecuted.Replace("\r\n", "\n"));
}
}
public async Task BatchUpdateAsync_correctly_specifies_AnsiString_type_on_the_sql_parameter()
{
var dbCommandInterceptor = new TestDbCommandInterceptor();
var interceptors = new[] { dbCommandInterceptor };
using var testContext = new TestContext(ContextUtil.GetOptions <TestContext>(DbServer.SqlServer, interceptors));
string oldPhoneNumber = "7756789999";
string newPhoneNumber = "3606789999";
_ = await testContext.Parents
.Where(parent => parent.PhoneNumber == oldPhoneNumber)
.BatchUpdateAsync(parent => new Parent {
PhoneNumber = newPhoneNumber
})
.ConfigureAwait(false);
var executedCommand = dbCommandInterceptor.ExecutedNonQueryCommands.Last();
Assert.Equal(2, executedCommand.DbParameters.Count);
var oldPhoneNumberParameter = (Microsoft.Data.SqlClient.SqlParameter)executedCommand.DbParameters.Single(param => param.ParameterName == "@__oldPhoneNumber_0");
Assert.Equal(System.Data.DbType.AnsiString, oldPhoneNumberParameter.DbType);
Assert.Equal(System.Data.SqlDbType.VarChar, oldPhoneNumberParameter.SqlDbType);
var newPhoneNumberParameter = (Microsoft.Data.SqlClient.SqlParameter)executedCommand.DbParameters.Single(param => param.ParameterName == "@param_1");
Assert.Equal(System.Data.DbType.AnsiString, newPhoneNumberParameter.DbType);
Assert.Equal(System.Data.SqlDbType.VarChar, newPhoneNumberParameter.SqlDbType);
var expectedSql =
@"UPDATE p SET [p].[PhoneNumber] = @param_1
FROM [Parent] AS [p]
WHERE [p].[PhoneNumber] = @__oldPhoneNumber_0";
Assert.Equal(expectedSql.Replace("\r\n", "\n"), executedCommand.Sql.Replace("\r\n", "\n"));
}
