search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.53k stars 3.13k forks source link

Ef core generates a query for each row after AddRange() then SaveChanges() #21544

Closed amro93 closed 1 year ago

amro93 commented 4 years ago

workflowStateRepository.CreateMany(wfStates); await workflowStateRepository.SaveAsync();

public virtual void CreateMany(IEnumerable entities) { if (entities is null) { throw new ArgumentNullException(nameof(entities)); } SetCreateEntityDetails(ref entities); Set.AddRange(entities); }

result query:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (78ms) [Parameters=[@p0='string' (Size = 4000), @p1='user.test' (Size = 100), @p2='2020-07-07T10:42:55' (Nullable = true), @p3=NULL (DbType = DateTime2), @p4='1st' (Size = 4000), @p5='41c2c594-0609-49fc-aa01-3f574803f3b1' (Nullable = false) (Size = 100), @p6='False', @p7='1st Step' (Nullable = false) (Size = 100), @p8='Automatic' (Nullable = false) (Size = 50), @p9='Start' (Nullable = false) (Size = 50), @p10=NULL (Size = 100), @p11=NULL (DbType = DateTime2), @p12=NULL (Size = 4000), @p13='Normal' (Nullable = false) (Size = 50), @p14='19'], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [WorkflowState] ([BusinessComments], [CreatedBy], [CreatedDate], [DeletedDate], [Description], [Guid], [IsDeleted], [Name], [PickUserMode], [Status], [UpdatedBy], [UpdatedDate], [WorkflowDiagram], [WorkflowStateType], [WorkflowVersionId])
      VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14);
      SELECT [Id]
      FROM [WorkflowState]
      WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (4ms) [Parameters=[@p0='string' (Size = 4000), @p1='user.test' (Size = 100), @p2='2020-07-07T10:42:55' (Nullable = true), @p3=NULL (DbType = DateTime2), @p4='2nd' (Size = 4000), @p5='a29d8d49-84d3-4514-a41a-da78e980714f' (Nullable = false) (Size = 100), @p6='False', @p7='2nd Step' (Nullable = false) (Size = 100), @p8='Automatic' (Nullable = false) (Size = 50), @p9='Intermediate' (Nullable = false) (Size = 50), @p10=NULL (Size = 100), @p11=NULL (DbType = DateTime2), @p12=NULL (Size = 4000), @p13='Normal' (Nullable = false) (Size = 50), @p14='19'], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [WorkflowState] ([BusinessComments], [CreatedBy], [CreatedDate], [DeletedDate], [Description], [Guid], [IsDeleted], [Name], [PickUserMode], [Status], [UpdatedBy], [UpdatedDate], [WorkflowDiagram], [WorkflowStateType], [WorkflowVersionId])
      VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14);
      SELECT [Id]
      FROM [WorkflowState]
      WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (7ms) [Parameters=[@p0='string' (Size = 4000), @p1='user.test' (Size = 100), @p2='2020-07-07T10:42:55' (Nullable = true), @p3=NULL (DbType = DateTime2), @p4='3rd' (Size = 4000), @p5='8530b3d5-4d0e-467d-ad1c-bbead04102ff' (Nullable = false) (Size = 100), @p6='False', @p7='3rd Step' (Nullable = false) (Size = 100), @p8='Automatic' (Nullable = false) (Size = 50), @p9='End' (Nullable = false) (Size = 50), @p10=NULL (Size = 100), @p11=NULL (DbType = DateTime2), @p12=NULL (Size = 4000), @p13='Normal' (Nullable = false) (Size = 50), @p14='19'], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [WorkflowState] ([BusinessComments], [CreatedBy], [CreatedDate], [DeletedDate], [Description], [Guid], [IsDeleted], [Name], [PickUserMode], [Status], [UpdatedBy], [UpdatedDate], [WorkflowDiagram], [WorkflowStateType], [WorkflowVersionId])
      VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14);
      SELECT [Id]
      FROM [WorkflowState]
      WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();

```### Steps to reproduce

<!--
What steps can we follow to reproduce the issue?

We ❤ code! Include a complete code listing or attach a simplified project

``` C#
public async Task<WorkflowVersionDto> CreateAsync(CreateWorkflowVersionDto dto)
        {
            var entity = workflowVersionDtoMap.DtoToEntity(dto);
            // TODO: entity mapping validation
            // TODO: enhance query below
            var wfStates = entity.WorkflowStates?.ToList();
            entity.WorkflowStates = null;

            await workflowVersionRepository.CreateAsync(entity);
            await workflowVersionRepository.SaveAsync();

            if (wfStates != null)
            {
                List<WorkflowDueActionType> actions = new List<WorkflowDueActionType>();
                List<WorkflowDueTime> dueTimes = new List<WorkflowDueTime>();
                List<WorkflowTransition> workflowTransitions = new List<WorkflowTransition>();
                List<WorkflowStateGroup> workflowStateGroups = new List<WorkflowStateGroup>();
                List<WorkflowStateUser> workflowStateUsers = new List<WorkflowStateUser>();

                // separate nested object to be saved on another object
                foreach (var state in wfStates)
                {
                    if (state.WorkflowDueActionTypes != null)
                    {
                        state.WorkflowDueActionTypes.ToList().ForEach(d => d.WorkflowState = state);
                        actions.AddRange(state.WorkflowDueActionTypes);
                        state.WorkflowDueActionTypes = null;
                    }
                    if (state.WorkflowDueTime != null)
                    {
                        state.WorkflowDueTime.WorkflowState = state;
                        dueTimes.Add(state.WorkflowDueTime);
                        state.WorkflowDueTime = null;
                    }

                    if (state.BeginWorkflowTransitions != null)
                    {
                        state.BeginWorkflowTransitions.ToList().ForEach(t => t.BeginWorkflowState = state);
                        workflowTransitions.AddRange(state.BeginWorkflowTransitions);
                        state.BeginWorkflowTransitions = null;
                    }

                    if (state.WorkflowStateGroups != null)
                    {
                        state.WorkflowStateGroups.ToList().ForEach(g => g.WorkflowState = state);
                        workflowStateGroups.AddRange(state.WorkflowStateGroups);
                        state.WorkflowStateGroups = null;
                    }

                    if (state.WorkflowStateUsers != null)
                    {
                        state.WorkflowStateUsers.ToList().ForEach(u => u.WorkflowState = state);
                        workflowStateUsers.AddRange(state.WorkflowStateUsers);
                        state.WorkflowStateUsers = null;
                    }
                }

               // Here =>>> expecting 100s of WfState objects
                workflowStateRepository.CreateMany(wfStates);
                await workflowStateRepository.SaveAsync(); // save each row in a single query

                // then
                workflowDueActionTypeRepository.CreateMany(actions); // save all rows in a single query

                workflowDueTimeRepository.CreateMany(dueTimes); // save all rows in a single query

                workflowTransitionRepository.CreateMany(workflowTransitions); // save all rows in a single query

                workflowStateGroupRepository.CreateMany(workflowStateGroups); // save all rows in a single query

                workflowStateUserRepository.CreateMany(workflowStateUsers); // save all rows in a single query

                await workflowStateRepository.SaveAsync(); // save all rows in a single query
            }
            return await GetWithStatesAsync(entity.Id);
        }

Got Exceptions? Include both the message and the stack trace

For dotnet ef and PMC, share the --verbose output -->

Further technical details

EF Core version: 3.1.5 Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer) Target framework: (e.g. .NET Core 3.0) Operating system: IDE: (e.g. Visual Studio 2019 16.3)

roji commented 4 years ago

@amro93 although a separate INSERT is sent for each added row, these INSERTs are batched in a single round-trip, providing good performance. It's also necessary to have separate INSERTs in order to get any database-generated values (e.g. identity) back from the database, and populate them in the inserted entities. Do you have a particular problem or alternative implementation in mind here?

amro93 commented 4 years ago

@amro93 although a separate INSERT is sent for each added row, these INSERTs are batched in a single round-trip, providing good performance. It's also necessary to have separate INSERTs in order to get any database-generated values (e.g. identity) back from the database, and populate them in the inserted entities. Do you have a particular problem or alternative implementation in mind here?

@roji thank you for the fast response,

its OK if the query is sent on a single round trip with multiple inserts, but I'd expect the logger to log this row "info: Microsoft.EntityFrameworkCore.Database.Command[20101]" once, which indicates that the transaction has been started. but if you have a deep look on the included query you will notice that the transaction initialized multiple times for each single row, which I believe its not a single round trip

however the next lines of the code will be translated into the query below which make sense for a single round trip

info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (46ms) [Parameters=[@p0='SendMailDueActionType' (Nullable = false) (Size = 100), @p1='54', @p2='this is over due test mail body' (Nullable = false) (Size = 4000), @p3='False', @p4='AssignedUser' (Nullable = false) (Size = 100), @p5='over due Task ' (Nullable = false) (Size = 4000), @p6='MoveToStateDueActionType' (Nullable = false) (Size = 100), @p7='54', @p8='41c2c594-0609-49fc-aa01-3f574803f3b1' (Size = 4000), @p9='53', @p10='54', @p11='Hours' (Nullable = false) (Size = 20), @p12='2', @p13='user.test' (Size = 100), @p14='2020-07-07T11:08:21' (Nullable = true), @p15='1', @p16='53', @p17='user.test' (Size = 100), @p18='2020-07-07T11:08:21' (Nullable = true), @p19='2', @p20='54', @p21='53', @p22='user.test' (Size = 100), @p23='2020-07-07T11:08:20' (Nullable = true), @p24='54', @p25='54', @p26='user.test' (Size = 100), @p27='2020-07-07T11:08:20' (Nullable = true), @p28='55'], CommandType='Text', CommandTimeout='30']

SET NOCOUNT ON; INSERT INTO [WorkflowDueActionType] ([Discriminator], [WorkflowStateId], [Body], [IsBodyHtml], [MailReceiverType], [Subject]) VALUES (@p0, @p1, @p2, @p3, @p4, @p5); SELECT [Id] FROM [WorkflowDueActionType] WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();

  INSERT INTO [WorkflowDueActionType] ([Discriminator], [WorkflowStateId], [NextWorkflowStateGuid], [NextWorkflowStateId])
  VALUES (@p6, @p7, @p8, @p9);
  SELECT [Id]
  FROM [WorkflowDueActionType]
  WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();

  INSERT INTO [WorkflowDueTime] ([WorkflowStateId], [DueTimeType], [DueTimeValue])
  VALUES (@p10, @p11, @p12);
  DECLARE @inserted3 TABLE ([Id] bigint, [_Position] [int]);
  MERGE [WorkflowStateGroup] USING (
  VALUES (@p13, @p14, @p15, @p16, 0),
  (@p17, @p18, @p19, @p20, 1)) AS i ([CreatedBy], [CreatedDate], [GroupId], [WorkflowStateId], _Position) ON 1=0
  WHEN NOT MATCHED THEN
  INSERT ([CreatedBy], [CreatedDate], [GroupId], [WorkflowStateId])
  VALUES (i.[CreatedBy], i.[CreatedDate], i.[GroupId], i.[WorkflowStateId])
  OUTPUT INSERTED.[Id], i._Position
  INTO @inserted3;

  SELECT [t].[Id] FROM [WorkflowStateGroup] t
  INNER JOIN @inserted3 i ON ([t].[Id] = [i].[Id])
  ORDER BY [i].[_Position];

  DECLARE @inserted5 TABLE ([Id] bigint, [_Position] [int]);
  MERGE [WorkflowTransition] USING (
  VALUES (@p21, @p22, @p23, @p24, 0),
  (@p25, @p26, @p27, @p28, 1)) AS i ([BeginWorkflowStateId], [CreatedBy], [CreatedDate], [EndWorkflowStateId], _Position) ON 1=0
  WHEN NOT MATCHED THEN
  INSERT ([BeginWorkflowStateId], [CreatedBy], [CreatedDate], [EndWorkflowStateId])
  VALUES (i.[BeginWorkflowStateId], i.[CreatedBy], i.[CreatedDate], i.[EndWorkflowStateId])
  OUTPUT INSERTED.[Id], i._Position
  INTO @inserted5;

  SELECT [t].[Id] FROM [WorkflowTransition] t
  INNER JOIN @inserted5 i ON ([t].[Id] = [i].[Id])
  ORDER BY [i].[_Position];
amro93 commented 4 years ago

I wrote a unit test which simulates the same exact code and it generated the expected query successfully

unit test

[TestClass]
    public class WorkflowVersionServiceTest
    {

        readonly IServiceProvider serviceProvider;
        readonly IConfiguration configuration;

        public WorkflowVersionServiceTest()
        {
            var myConfiguration = new Dictionary<string, string>
            {
                {"ConnectionStrings:DefaultConnection", "Server=DESKTOP-3DR4F68;Database=WorkflowDev;user id=sa;password=P@$$w0rd;MultipleActiveResultSets=true;Initial Catalog=WorkflowDev1"}
            };
            var config = new ConfigurationBuilder().AddInMemoryCollection(myConfiguration).Build();
            configuration = config;
            serviceProvider = IoC.IoCServices.AddAppServices(new ServiceCollection(), config, LoggerFactory.Create(a =>
            {
                a.AddDebug();
                a.AddConsole();
            })).AddLogging(o => {
                o.AddConsole(c => c.DisableColors = false);
                o.AddDebug();
            }).BuildServiceProvider();
        }

        [TestMethod]
        public async Task CreateMany()
        {
            var ctx = serviceProvider.GetRequiredService<ApplicationDbContext>();

            var repo = new WorkflowStateRepository(
                ctx,
                serviceProvider
                );
            var wfStates = new List<WorkflowState>();
            for (int i = 0; i <= 10; i++)
            {
                wfStates.Add(
                new WorkflowState
                {
                    Guid = Guid.NewGuid().ToString(),
                    WorkflowVersionId = 10,
                    Name = $"state {i}",
                });
            }

            repo.CreateMany(wfStates);
            await repo.SaveAsync();
        }
    }

Result query

Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (92ms) [Parameters=[@p0=NULL (Size = 4000), @p1=NULL (Size = 100), @p2='2020-07-07T18:27:38' (Nullable = true), @p3=NULL (DbType = DateTime2), @p4=NULL (Size = 4000), @p5='c45d87b0-792b-4eef-95bb-ac2efe2bd549' (Nullable = false) (Size = 100), @p6='False', @p7='state 0' (Nullable = false) (Size = 100), @p8='Manual' (Nullable = false) (Size = 50), @p9='Start' (Nullable = false) (Size = 50), @p10=NULL (Size = 100), @p11=NULL (DbType = DateTime2), @p12=NULL (Size = 4000), @p13='Normal' (Nullable = false) (Size = 50), @p14='10', @p15=NULL (Size = 4000), @p16=NULL (Size = 100), @p17='2020-07-07T18:27:38' (Nullable = true), @p18=NULL (DbType = DateTime2), @p19=NULL (Size = 4000), @p20='28a1faa1-c170-453f-b56d-c1ebb51b8584' (Nullable = false) (Size = 100), @p21='False', @p22='state 1' (Nullable = false) (Size = 100), @p23='Manual' (Nullable = false) (Size = 50), @p24='Start' (Nullable = false) (Size = 50), @p25=NULL (Size = 100), @p26=NULL (DbType = DateTime2), @p27=NULL (Size = 4000), @p28='Normal' (Nullable = false) (Size = 50), @p29='10', @p30=NULL (Size = 4000), @p31=NULL (Size = 100), @p32='2020-07-07T18:27:38' (Nullable = true), @p33=NULL (DbType = DateTime2), @p34=NULL (Size = 4000), @p35='14c8fbcb-09b6-4d31-bc61-24b9e992b047' (Nullable = false) (Size = 100), @p36='False', @p37='state 2' (Nullable = false) (Size = 100), @p38='Manual' (Nullable = false) (Size = 50), @p39='Start' (Nullable = false) (Size = 50), @p40=NULL (Size = 100), @p41=NULL (DbType = DateTime2), @p42=NULL (Size = 4000), @p43='Normal' (Nullable = false) (Size = 50), @p44='10', @p45=NULL (Size = 4000), @p46=NULL (Size = 100), @p47='2020-07-07T18:27:38' (Nullable = true), @p48=NULL (DbType = DateTime2), @p49=NULL (Size = 4000), @p50='c414c398-54e0-4627-8630-6c86b64e0055' (Nullable = false) (Size = 100), @p51='False', @p52='state 3' (Nullable = false) (Size = 100), @p53='Manual' (Nullable = false) (Size = 50), @p54='Start' (Nullable = false) (Size = 50), @p55=NULL (Size = 100), @p56=NULL (DbType = DateTime2), @p57=NULL (Size = 4000), @p58='Normal' (Nullable = false) (Size = 50), @p59='10', @p60=NULL (Size = 4000), @p61=NULL (Size = 100), @p62='2020-07-07T18:27:38' (Nullable = true), @p63=NULL (DbType = DateTime2), @p64=NULL (Size = 4000), @p65='8fa009aa-e375-4a7b-8117-8cbd3de36083' (Nullable = false) (Size = 100), @p66='False', @p67='state 4' (Nullable = false) (Size = 100), @p68='Manual' (Nullable = false) (Size = 50), @p69='Start' (Nullable = false) (Size = 50), @p70=NULL (Size = 100), @p71=NULL (DbType = DateTime2), @p72=NULL (Size = 4000), @p73='Normal' (Nullable = false) (Size = 50), @p74='10', @p75=NULL (Size = 4000), @p76=NULL (Size = 100), @p77='2020-07-07T18:27:38' (Nullable = true), @p78=NULL (DbType = DateTime2), @p79=NULL (Size = 4000), @p80='80caa179-f575-49f6-bf18-d6c68017132b' (Nullable = false) (Size = 100), @p81='False', @p82='state 5' (Nullable = false) (Size = 100), @p83='Manual' (Nullable = false) (Size = 50), @p84='Start' (Nullable = false) (Size = 50), @p85=NULL (Size = 100), @p86=NULL (DbType = DateTime2), @p87=NULL (Size = 4000), @p88='Normal' (Nullable = false) (Size = 50), @p89='10', @p90=NULL (Size = 4000), @p91=NULL (Size = 100), @p92='2020-07-07T18:27:38' (Nullable = true), @p93=NULL (DbType = DateTime2), @p94=NULL (Size = 4000), @p95='5c4933e2-d79c-476c-b799-65ff89fb15f6' (Nullable = false) (Size = 100), @p96='False', @p97='state 6' (Nullable = false) (Size = 100), @p98='Manual' (Nullable = false) (Size = 50), @p99='Start' (Nullable = false) (Size = 50), @p100=NULL (Size = 100), @p101=NULL (DbType = DateTime2), @p102=NULL (Size = 4000), @p103='Normal' (Nullable = false) (Size = 50), @p104='10', @p105=NULL (Size = 4000), @p106=NULL (Size = 100), @p107='2020-07-07T18:27:38' (Nullable = true), @p108=NULL (DbType = DateTime2), @p109=NULL (Size = 4000), @p110='555c1625-8fcf-4890-b5ae-de6157bfd7c9' (Nullable = false) (Size = 100), @p111='False', @p112='state 7' (Nullable = false) (Size = 100), @p113='Manual' (Nullable = false) (Size = 50), @p114='Start' (Nullable = false) (Size = 50), @p115=NULL (Size = 100), @p116=NULL (DbType = DateTime2), @p117=NULL (Size = 4000), @p118='Normal' (Nullable = false) (Size = 50), @p119='10', @p120=NULL (Size = 4000), @p121=NULL (Size = 100), @p122='2020-07-07T18:27:38' (Nullable = true), @p123=NULL (DbType = DateTime2), @p124=NULL (Size = 4000), @p125='4c07b2f0-a91a-4b1e-a4cc-47275f0a7caa' (Nullable = false) (Size = 100), @p126='False', @p127='state 8' (Nullable = false) (Size = 100), @p128='Manual' (Nullable = false) (Size = 50), @p129='Start' (Nullable = false) (Size = 50), @p130=NULL (Size = 100), @p131=NULL (DbType = DateTime2), @p132=NULL (Size = 4000), @p133='Normal' (Nullable = false) (Size = 50), @p134='10', @p135=NULL (Size = 4000), @p136=NULL (Size = 100), @p137='2020-07-07T18:27:38' (Nullable = true), @p138=NULL (DbType = DateTime2), @p139=NULL (Size = 4000), @p140='69d42d91-3d95-4944-b78f-729ccf3a0c3c' (Nullable = false) (Size = 100), @p141='False', @p142='state 9' (Nullable = false) (Size = 100), @p143='Manual' (Nullable = false) (Size = 50), @p144='Start' (Nullable = false) (Size = 50), @p145=NULL (Size = 100), @p146=NULL (DbType = DateTime2), @p147=NULL (Size = 4000), @p148='Normal' (Nullable = false) (Size = 50), @p149='10', @p150=NULL (Size = 4000), @p151=NULL (Size = 100), @p152='2020-07-07T18:27:38' (Nullable = true), @p153=NULL (DbType = DateTime2), @p154=NULL (Size = 4000), @p155='fd4fdb28-c638-49b9-92b7-baaa90e09f7c' (Nullable = false) (Size = 100), @p156='False', @p157='state 10' (Nullable = false) (Size = 100), @p158='Manual' (Nullable = false) (Size = 50), @p159='Start' (Nullable = false) (Size = 50), @p160=NULL (Size = 100), @p161=NULL (DbType = DateTime2), @p162=NULL (Size = 4000), @p163='Normal' (Nullable = false) (Size = 50), @p164='10'], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
DECLARE @inserted0 TABLE ([Id] bigint, [_Position] [int]);
MERGE [WorkflowState] USING (
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, 0),
(@p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, 1),
(@p30, @p31, @p32, @p33, @p34, @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, 2),
(@p45, @p46, @p47, @p48, @p49, @p50, @p51, @p52, @p53, @p54, @p55, @p56, @p57, @p58, @p59, 3),
(@p60, @p61, @p62, @p63, @p64, @p65, @p66, @p67, @p68, @p69, @p70, @p71, @p72, @p73, @p74, 4),
(@p75, @p76, @p77, @p78, @p79, @p80, @p81, @p82, @p83, @p84, @p85, @p86, @p87, @p88, @p89, 5),
(@p90, @p91, @p92, @p93, @p94, @p95, @p96, @p97, @p98, @p99, @p100, @p101, @p102, @p103, @p104, 6),
(@p105, @p106, @p107, @p108, @p109, @p110, @p111, @p112, @p113, @p114, @p115, @p116, @p117, @p118, @p119, 7),
(@p120, @p121, @p122, @p123, @p124, @p125, @p126, @p127, @p128, @p129, @p130, @p131, @p132, @p133, @p134, 8),
(@p135, @p136, @p137, @p138, @p139, @p140, @p141, @p142, @p143, @p144, @p145, @p146, @p147, @p148, @p149, 9),
(@p150, @p151, @p152, @p153, @p154, @p155, @p156, @p157, @p158, @p159, @p160, @p161, @p162, @p163, @p164, 10)) AS i ([BusinessComments], [CreatedBy], [CreatedDate], [DeletedDate], [Description], [Guid], [IsDeleted], [Name], [PickUserMode], [Status], [UpdatedBy], [UpdatedDate], [WorkflowDiagram], [WorkflowStateType], [WorkflowVersionId], _Position) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([BusinessComments], [CreatedBy], [CreatedDate], [DeletedDate], [Description], [Guid], [IsDeleted], [Name], [PickUserMode], [Status], [UpdatedBy], [UpdatedDate], [WorkflowDiagram], [WorkflowStateType], [WorkflowVersionId])
VALUES (i.[BusinessComments], i.[CreatedBy], i.[CreatedDate], i.[DeletedDate], i.[Description], i.[Guid], i.[IsDeleted], i.[Name], i.[PickUserMode], i.[Status], i.[UpdatedBy], i.[UpdatedDate], i.[WorkflowDiagram], i.[WorkflowStateType], i.[WorkflowVersionId])
OUTPUT INSERTED.[Id], i._Position
INTO @inserted0;

SELECT [t].[Id] FROM [WorkflowState] t
INNER JOIN @inserted0 i ON ([t].[Id] = [i].[Id])
ORDER BY [i].[_Position];
The thread 0x37bc has exited with code 0 (0x0).
The thread 0x5e54 has exited with code 0 (0x0).
The program '[23056] testhost.x86.exe: Program Trace' has exited with code 0 (0x0).
The program '[23056] testhost.x86.exe' has exited with code 0 (0x0).
amro93 commented 4 years ago

When I added large number of rows 1000+ ef core generated a huge query for a single round trip. it seems that ef core will decide when to generate a single query for each row if the number of rows is less than a specific value which intended to improve the performance after all.

roji commented 4 years ago

@amro93 yeah, the SQL Server provider has a minimum below which it doesn't batch, because that has been shown to be less efficient - see #9270 for some more info on this. FWIW the PostgreSQL providers starting from two commands, so this is a provider-specific thing.