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);
}
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.
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
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);
}
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.