Insert multiple data in child table in ASP.NET Boilerplate

asp.net-core aspnetboilerplate c# entity-framework-core object-object-mapping

Question

Background

I have two tables Actions and ActionsDetails.

Actions:

CREATE TABLE [dbo].[Actions]
(
    [ActionId] [BIGINT] IDENTITY(1,1) NOT NULL,
    [DeviceId] [NVARCHAR](125) NOT NULL,
    [TenantId] [INT] NOT NULL,

    CONSTRAINT [PK_ActionId]
        PRIMARY KEY CLUSTERED ([ActionId] ASC)
        WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

ActionsDetails:

CREATE TABLE [dbo].[ActionsDetails]
(
    [ActionsDetailsId] [BIGINT] IDENTITY(1,1) NOT NULL,
    [ActionId] [BIGINT] NOT NULL,
    [ActionName] [NVARCHAR](125) NOT NULL,
    [Description] [NVARCHAR](800) NOT NULL,

    CONSTRAINT [PK_ActionsDetailsId]
        PRIMARY KEY CLUSTERED ([ActionsDetailsId] ASC)
        WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ActionsDetails] WITH CHECK
    ADD CONSTRAINT [FK_ActionsDetails_ActionsId]
        FOREIGN KEY([ActionId]) REFERENCES [dbo].[Actions] ([ActionId])
GO

ALTER TABLE [dbo].[ActionsDetails] CHECK CONSTRAINT [FK_ActionsDetails_ActionsId]
GO

What I am trying to do is to insert a row of data in Actions and then capture ActionId for the inserted record, and then insert multiple values for that ActionId in ActionsDetails table. Very simple case of inserting into parent-child table using EF Core, just that child table can have multiple records for a parent row.

This is the piece of code which does that in ActionsAppService class:

public async Task CreateActions(CreateActionDto input)
{
    Actions actions = new Actions();
    ActionsDetails actionsdetails = new ActionsDetails();

    MapToEntity(input, actions);

    // Insert into Actions and return last generated ActionId

    long actionid = await _actionmanager.CreateActionsAsync(actions);

    // looping here since child table can have multiple record to be
    // inserted

    foreach (var actionname in input.ActionDetails)
    {
        // TODO: This can go to a mapping method as well
        actionsdetails.ActionName = actionname.ActionName;
        actionsdetails.Description = actionname.Description;
        actionsdetails.ActionId = actionid;

        // Inserting details into ActionsDetails table for the
        // corresponding ActionId

        await _actionmanager.CreateActionsDetailsAsync(actionsdetails);
    }
}

Data mapping between input DTO and entity:

private void MapToEntity(CreateActionDto actionsdto, Actions actions)
{
    actions.DeviceId = actionsdto.DeviceId;
    actions.TenantId = (int)(AbpSession.TenantId);
}

In ActionManager class:

public async Task<long> CreateActionsAsync(Actions input)
{
    return await _actionsRepository.InsertAndGetIdAsync(input);
}

public async Task CreateActionsDetailsAsync(ActionsDetails input)
{
    await _actionsdetailsRepository.InsertAsync(input);
}

Question

The issue that I am seeing is that ActionDetails list only contains last value of collection which is inserted. For example, if I pass this value:

{
  "deviceId": "zainnewdevice",
  "actionDetails": [
    {
      "actionId": 0,
      "actionName": "switchtube1",
      "description": "this would swtich on Tube1"
    },
    {
      "actionId": 0,
      "actionName": "switchtube2",
      "description": "This would switch on Tube2"
    }
  ]
}

The last value of collection, i.e. "switchtube2" and "This would switch on Tube2", is inserted in ActionDetails table.

Perhaps my way of getting things done is all wrong, or might be just my lack of understanding of Entity Framework and how it works, but I got a sense that the actionsdetails object needs to be added in context before calling the CreateActionsDetailsAsync in ActionManager. I couldn't figure out how to do it since all context is set in DbContext class.

Also, I was thinking maybe adding actionsdetails to a List and then passing that List object to CreateActionsDetailsAsync in ActionManager class would help. Perhaps I can loop through the list object there and call await _actionsdetailsRepository.InsertAsync(input) in a loop, but that strategy also failed.

DTO:

public class CreateActionDto
{
    [Required]
    public string DeviceId { get; set; }

    public ICollection<ActionsDetailsDto> ActionDetails { get; set; }
}

Entities:

public class Actions : Entity<long>, IMustHaveTenant
{
    [Column("ActionId")]
    [Key]
    public override long Id { get; set; }

    [Required]
    public string DeviceId { get; set; }

    [Required]
    public int TenantId { get; set; }

    public virtual ICollection<ActionsDetails> ActionsDetails { get; set; }

    public Actions()
    {
        ActionsDetails = new List<ActionsDetails>();
    }
}

public class ActionsDetails : Entity<long>
{
    [ForeignKey("ActionId")]
    public virtual Actions Actions { get; set; }

    [Column("ActionsDetailsId")]
    [Key]
    public override long Id { get; set; }

    [Required]
    public long ActionId { get; set; }

    [Required]
    public string ActionName { get; set; }

    [Required]
    public string Description { get; set; }
}

All rows in collection should appear in ActionsDetails table.

1
0
8/5/2019 3:40:19 PM

Accepted Answer

Explicit mapping

The issue what I am seeing is that ActionDetails list only last value of collection which is inserted.

This is because you are mapping every DTO to the same entity.

// ActionsDetails actionsdetails = new ActionsDetails(); // Move this...

foreach (var actionname in input.ActionDetails)
{
    ActionsDetails actionsdetails = new ActionsDetails(); // ...here

    actionsdetails.ActionName = actionname.ActionName;
    actionsdetails.Description = actionname.Description;
    actionsdetails.ActionId = actionid;

    await _actionmanager.CreateActionsDetailsAsync(actionsdetails);
}

Related: MapTo() inside of LINQ returning multiple iterations of same data

AutoMapper

Another way is to leverage on ABP's Object To Object Mapping.

Configuration:

[AutoMapTo(typeof(Actions))] // Add this
public class CreateActionDto
{
    [Required]
    public string DeviceId { get; set; }       

 // public ICollection<ActionsDetailsDto> ActionDetails { get; set; } // Rename this
    public ICollection<ActionsDetailsDto> ActionsDetails { get; set; }
}

[AutoMapTo(typeof(ActionsDetails))] // Add this
public class ActionsDetailsDto
{
    // ...
}

Usage:

public async Task CreateActions(CreateActionDto input)
{
    Actions actions = new Actions();

    MapToEntity(input, actions);

    long actionId = await _actionmanager.CreateActionsAsync(actions);
}

private void MapToEntity(CreateActionDto actionsdto, Actions actions)
{
    _objectMapper.Map(actionsdto, actions);
}
1
8/6/2019 3:16:22 PM

Popular Answer

You don't have to insert the Action and the ActionDetails separately. EF will take care of the insert, just pass the full entity to the insert method:

public async Task CreateActions(CreateActionDto input)
{
    Actions actions = new Actions();            
    ActionsDetails actionsdetails = new ActionsDetails();

    // Do your mapping
    MapToEntity(input, actions);

    // Add the details
    foreach (var actionname in input.ActionDetails)
    {
        actionsdetails.ActionName = actionname.ActionName;
        actionsdetails.Description = actionname.Description;

        // Add it to the collection
        actions.ActionsDetails.Add(actionsdetails);
    }

    // Save
    await _actionmanager.CreateActionsAsync(actions);        
}

The best way to avoid problems created by two separate insert statements is to wrap everything in a transaction and because you're using EF that's the default mechanism for how it works.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow