I am newbie in .NET and EF Core 7 and can't understand this behaviour.
I have this .NET code:
...
List<UsersGroups> ugs = new List<UsersGroups>();
DateTime ddd = DateTime.Now;
foreach (string group in Groups)
{
if (some condition)
{
ADGroups adGroup = await _context.ADGroups.FirstAsync(group => group.Name.Contains(groupName));
ugs.Add(new UsersGroups{ DateCheck = ddd, UserId = ManId, Action = "Login", GroupId = adGroup.Id });
}
}
_context.UsersGroups.AddRange(ugs);
await _context.SaveChangesAsync();
The SQL Server table for _context.UsersGroups has a trigger after insert with notification:
DECLARE @message NVARCHAR(500) = (SELECT *, spid = @@SPID, t = SYSDATETIME() FROM INSERTED FOR JSON AUTO)
EXEC [dbo].[SendNotify] @message
The problem is: if there are less than 4 objects in List<UsersGroups> ugs, the call to await _context.SaveChangesAsync() makes one change on each object. So trigger fires on each object but not for array. But if there are 4 or more objects in the list, everything is OK - trigger fires once for the whole batch.
Results of inserts:
2 objects in List = 2 messages:
[ { "Id": 6566, "UserId": 163, "GroupId": 1, "spid": 66,"t": "2024-01-29T11:10:29.4294982" } ]
[ { "Id": 6567, "UserId": 163, "GroupId": 2, "spid": 66,"t": "2024-01-29T11:10:29.6303315" } ]
4 and more objects = BatchInsert
[
{ "Id": 6571, "UserId": 163, "GroupId": 3, "spid": 66,"t": "2024-01-29T11:11:05.3763680" },
{ "Id": 6570, "UserId": 163, "GroupId": 2, "spid": 66,"t":"2024-01-29T11:11:05.3763680" },
{ "Id": 6569, "UserId": 163, "GroupId": 1, "spid": 66, "t": "2024-01-29T11:11:05.3763680" },
{ "Id": 6568, "UserId": 163, "GroupId": 9, "spid": 66,"t":"2024-01-29T11:11:05.3763680" }
]
How to make _context.SaveChangesAsync() make one call to SQL Server for 2 and 3 objects?