Hi,
- I have these 3 Tables
public class Alert : FullAuditedEntity<long>
{
[NotNull]
public virtual string FlightRegistrationNumber { get; set; }
[NotNull]
public virtual string Subject { get; set; }
public ICollection<AlertSubEventType> SubEvents { get; set; }
public Alert()
{
}
public Alert(long id, string flightRegistrationNumber, string subject, string details)
{
Id = id;
Check.NotNull(flightRegistrationNumber, nameof(flightRegistrationNumber));
Check.Length(flightRegistrationNumber, nameof(flightRegistrationNumber), AlertConsts.FlightRegistrationNumberMaxLength, 0);
Check.NotNull(subject, nameof(subject));
Check.Length(subject, nameof(subject), AlertConsts.SubjectMaxLength, 0);
FlightRegistrationNumber = flightRegistrationNumber;
Subject = subject;
Details = details;
SubEvents = new Collection<AlertSubEventType>();
}
public virtual void AddSubEvent(int eventId)
{
Check.NotNull(eventId, nameof(eventId));
if (IsInSubEvents(eventId))
{
return;
}
SubEvents.Add(new AlertSubEventType(Id, eventId));
}
public virtual void RemoveSubEvent(int eventId)
{
Check.NotNull(eventId, nameof(eventId));
if (!IsInSubEvents(eventId))
{
return;
}
SubEvents.RemoveAll(s => s.EventTypeId == eventId);
}
public bool IsInSubEvents(int eventId)
{
Check.NotNull(eventId, nameof(eventId));
return SubEvents.Any(s => s.EventTypeId == eventId);
}
}
public class AlertSubEventType : FullAuditedEntity
{
public long AlertId { get; set; }
public int EventTypeId { get; set; }
public AlertSubEventType()
{
}
public AlertSubEventType(long alertId, int eventTypeId)
{
AlertId = alertId;
EventTypeId = eventTypeId;
}
public override object[] GetKeys()
{
return new object[] { AlertId, EventTypeId };
}
}
public class EventType : FullAuditedEntity<int>
{
[NotNull]
public virtual string Name { get; set; }
public AlertEventType()
{
}
public EventType(int id, string name)
{
Id = id;
Check.NotNull(name, nameof(name));
Check.Length(name, nameof(name), EventTypeConsts.NameMaxLength, 0);
Name = name;
}
}
- Table Mappings are sep up like below.
builder.Entity<Alert>(b =>
{
b.ToTable(VgccConsts.DbTablePrefix + "Alerts", VgccConsts.DbSchema);
b.ConfigureByConvention();
b.Property(x => x.FlightRegistrationNumber).HasColumnName(nameof(Alert.FlightRegistrationNumber)).IsRequired().HasMaxLength(AlertConsts.FlightRegistrationNumberMaxLength);
b.Property(x => x.Subject).HasColumnName(nameof(Alert.Subject)).IsRequired().HasMaxLength(AlertConsts.SubjectMaxLength);
b.Property(x => x.Details).HasColumnName(nameof(Alert.Details)).HasMaxLength(AlertConsts.DetailsMaxLength);
b.HasMany(a => a.SubEvents).WithOne().HasForeignKey(ae => ae.AlertId).IsRequired();
});
builder.Entity<AlertSubEventType>(b =>
{
b.ToTable(VgccConsts.DbTablePrefix + "AlertSubEventTypes", VgccConsts.DbSchema);
b.ConfigureByConvention();
b.HasKey(ae => new { ae.AlertId, ae.EventTypeId });
b.HasOne<EventType>().WithMany().HasForeignKey(ae => ae.EventTypeId).IsRequired();
b.HasOne<Alert>().WithMany(a => a.SubEvents).HasForeignKey(ae => ae.AlertId).IsRequired();
b.HasIndex(ae => new { ae.EventTypeId, ae.AlertId });
});
builder.Entity<EventType>(b =>
{
b.ToTable(VgccConsts.DbTablePrefix + "EventType", VgccConsts.DbSchema);
b.ConfigureByConvention();
b.Property(x => x.Name).HasColumnName(nameof(EventType.Name)).IsRequired().HasMaxLength(EventTypeConsts.NameMaxLength);
});
- Now in Alert API service I'm trying to create a new entry. at the same time I'm trying to add AlertSubEvents too. but I am getting the EF Core error. Could you suggest what am I missing.
- API call
private readonly IAlertRepository _alertRepository;
private readonly IRepository<AlertSubEventType> _alertSubEventTypeRepository;
public virtual async Task<AlertDto> CreateAsync(AlertCreateDto input)
{
var newAlert = ObjectMapper.Map<AlertCreateDto, Alert>(input);
var alert = await _alertRepository.InsertAsync(newAlert);
await UpdateAlertByInput(alert, input); // inserting subevents here
await CurrentUnitOfWork.SaveChangesAsync(); //Error occured here
return ObjectMapper.Map<Alert, AlertDto>(alert);
}
protected virtual async Task UpdateAlertByInput(Alert alert, AlertCreateOrUpdateDtoBase input)
{
alert.Subject = input.Subject;
alert.FlightRegistrationNumber = input.FlightRegistrationNumber;
alert.PrimaryEventTypeId = input.PrimaryEventTypeId;
alert.ScopeId = input.ScopeId;
alert.SeverityId = input.SeverityId;
if (input.SubEventNames != null)
{
await SetSubEventsAsync(alert, input.SubEventNames);
}
}
private async Task SetSubEventsAsync([NotNull] Alert alert, [NotNull] IEnumerable<string> subEventNames)
{
Check.NotNull(alert, nameof(alert));
Check.NotNull(subEventNames, nameof(subEventNames));
var currentSubEventNames = await _alertRepository.GetSubEventTypes(alert);
var result = await RemoveFromSubEventsAsync(alert, currentSubEventNames.Except(subEventNames).Distinct());
result = await AddToSubEventsAsync(alert, subEventNames.Except(currentSubEventNames).Distinct());
}
private async Task<bool> RemoveFromSubEventsAsync(Alert alert, IEnumerable<string> subEvents)
{
Check.NotNull(alert, nameof(alert));
Check.NotNull(subEvents, nameof(subEvents));
var EventTypesMasterList = await _alertEventTypeRepository.GetListAsync();
foreach (var subEvent in subEvents)
{
var eventId = EventTypesMasterList.FirstOrDefault(e => e.Name == subEvent);
await _alertSubEventTypeRepository.DeleteAsync(new AlertSubEventType(alert.Id, eventId.Id));
}
return true;
}
private async Task<bool> AddToSubEventsAsync(Alert alert, IEnumerable<string> subEvents)
{
Check.NotNull(alert, nameof(alert));
Check.NotNull(subEvents, nameof(subEvents));
var EventTypesMasterList = await _alertEventTypeRepository.GetListAsync();
foreach (var subEvent in subEvents)
{
var eventId = EventTypesMasterList.FirstOrDefault(e => e.Name == subEvent);
await _alertSubEventTypeRepository.InsertAsync(new AlertSubEventType(alert.Id, eventId.Id));
}
return true;
}
ERROR Details:
2020-05-19 11:43:44.874 -04:00 [ERR] An error occurred while updating the entries. See the inner exception for details.
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
---> Microsoft.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_AppAlertSubEventTypes_AppAlerts_AlertId". The conflict occurred in database "Vgcc", table "dbo.AppAlerts", column 'Id'.
The statement has been terminated.
at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__164_0(Task`1 result)
NOTE: I have followed ABP Identity module IdentityUser, IdentityRole & IdentityUserRole implementation. Let me know if any additionall details required.
17 Answer(s)
-
0
Try use
AddSubEvent
method to replace the followingawait _alertSubEventTypeRepository.InsertAsync(new AlertSubEventType(alert.Id, eventId.Id));
-
0
Hi,
I don't see Add() method in the _alertSubEventTypeRepository If you are referring to use Add() method instead of InsertAsync().
-
0
I mean using the AddSubEvent method of the Alert entity.
private async Task<bool> AddToSubEventsAsync(Alert alert, IEnumerable<string>subEvents) { Check.NotNull(alert, nameof(alert)); Check.NotNull(subEvents, nameof(subEvents)); var EventTypesMasterList = await _alertEventTypeRepository.GetListAsync(); foreach (var subEvent in subEvents) { var eventId = EventTypesMasterList.FirstOrDefault(e => e.Name == subEvent); alert.AddSubEvent(eventId); // This line } return true; }
-
0
-
0
Any update on this please.
-
0
I'll try to reproduce it
-
0
-
0
Hi liangshiwei,
Thanks for the response. I didn't want to save bits and pieces. Alert & Alert Sub events should get inserted at a time. Could you see why AlertSubEvents are inserted first before having an entry in Alert table?
-
0
I see that you use alertRepository query in SetSubEventsAsync method,I don't know your implementation, I assume it queries from the database.so changes need to be saved.
var currentSubEventNames = await _alertRepository.GetSubEventTypes(alert);
I see that you call the
RemoveFromSubEventsAsync
method to delete the existing record, but the id value of the current alert entity is0
. I think you should make the following changes:public virtual async Task<AlertDto> CreateAsync(AlertCreateDto input) { var newAlert = new Alert() { Subject = input.Subject, FlightRegistrationNumber = input.FlightRegistrationNumber }; var alert = await _alertRepository.InsertAsync(newAlert); await UpdateAlertByInput(alert, input); await CurrentUnitOfWork.SaveChangesAsync(); return ObjectMapper.Map<Alert, AlertDto>(alert); } protected virtual async Task UpdateAlertByInput(Alert alert, AlertCreateOrUpdateDtoBase input) { alert.Subject = input.Subject; alert.FlightRegistrationNumber = input.FlightRegistrationNumber; if (input.SubEventNames != null) { await SetSubEventsAsync(alert, input.SubEventNames); } } private async Task SetSubEventsAsync([NotNull] Alert alert, [NotNull] IEnumerable<string> subEventNames) { Check.NotNull(alert, nameof(alert)); Check.NotNull(subEventNames, nameof(subEventNames)); if (alert.Id != 0) { await RemoveFromSubEventsAsync(alert); } await AddToSubEventsAsync(alert, subEventNames.Distinct()); } private async Task<bool> RemoveFromSubEventsAsync(Alert alert) { Check.NotNull(alert, nameof(alert)); alert.SubEvents.Clear(); return true; } private async Task<bool> AddToSubEventsAsync(Alert alert, IEnumerable<string> subEvents) { Check.NotNull(alert, nameof(alert)); var enumerable = subEvents as string[] ?? subEvents.ToArray(); Check.NotNull(enumerable, nameof(subEvents)); var EventTypesMasterList = await _alertEventTypeRepository.GetListAsync(); foreach (var subEvent in enumerable) { var eventType = EventTypesMasterList.FirstOrDefault(e => e.Name == subEvent); alert.AddSubEvent(eventType.Id); } return true; }
In addition, you configured a soft delete for the
AlertSubEventType
entity, but the entity uses acombined index
, you need to remove soft delete:public class AlertSubEventType : Entity { public long AlertId { get; set; } public int EventTypeId { get; set; } public AlertSubEventType() { } public AlertSubEventType(long alertId, int eventTypeId) { AlertId = alertId; EventTypeId = eventTypeId; } public override object[] GetKeys() { return new object[] {AlertId, EventTypeId}; } }
-
0
Is the Foreign Key voilation error totally because of AlertSubEvent table being extended from IFullAuditObject? we wanted to have soft delete on AlertSubEvent to track when a subEvent removed. Coming to remove logic I'll add the condition (when alert.Id > 0).
My Remove and Addition functionality only adds/removes new subEvents. I'm not cleaning all previous assigned subevents and readding. Say there were 3 subEvents before and a new one is added, I don't need to delete all 3 and add 4 again right. I should just add the new one.
-
0
The cause of the error is not because of the AlertSubEventType entity, I think your code has wrong.
If you want to implement soft delete for AlertSubEventType entity, you should use
b.HasIndex(ae => new { ae.EventTypeId, ae.AlertId }).HasFilter("[IsDeleted] != 1");
see https://github.com/abpframework/abp/issues/1181.If you can provide a sample project that reproduce this problem, I can quickly help you find the problem.
-
0
thank you. I have uploaded the project to GitHub. Can you fork it and see to create an alert using Swagger.
https://github.com/yrk1436/AtlasAir.VGCC
public virtual async Task<AlertDto> CreateAsync(AlertCreateDto input) // Service method
-
0
Hi @roop.yekollu@atlasair.com,
I think this problem occurs because of you don't inserting your first object instantly.
I have created a simple application with abp suite to test and solve your problem and I had no errors.
This is my AlertCreateDto, I just get one EventId because of fast testing. You can get a collection of event ids.
public class AlertCreateDto { [Required] [StringLength(AlertConsts.FlightRegistrationNumberMaxLength)] public string FlightRegistrationNumber { get; set; } [Required] [StringLength(AlertConsts.SubjectMaxLength)] public string Subject { get; set; } [Required] public Guid EventId { get; set; } }
And here my CreateAsync method. As you see I sent true for
autoSave
parameter at inserting new alert. The rest of code of applicaiton is just auto generated code by abp-suite.public virtual async Task<AlertDto> CreateAsync(AlertCreateDto input) { var alertEvent = await _eventTypeRepository.GetAsync(input.EventId); // To Check event var newAlert = ObjectMapper.Map<AlertCreateDto, Alert>(input); var alert = await _alertRepository.InsertAsync(newAlert, true); // AutoSave parameter is true var mmRelation = await _alertEventRepository.InsertAsync(new AlertEvent { AlertId = alert.Id, EventId = alertEvent.Id }); // Added to M-T-M relation await CurrentUnitOfWork.SaveChangesAsync(); return ObjectMapper.Map<Alert, AlertDto>(alert); }
And the result
Tables (I've removed some properties like
isDeleted etc.
from select query to get clear result) -
0
Hi @roop.yekollu@atlasair.com
I suggest you delete the repository on github, because your commercial license is included in the project.
-
0
Hi @roop.yekollu@atlasair.com
I use the project you provided, but can't reproduce this problem. Just one thing, you use automapper map object, but you did not initialize the collection in the parameterless constructor.null reference exception will be thrown when adding.
In addition, some boundary condition checks are missing in your code, like:
private async Task<bool> AddToSubEventsAsync(Alert alert, IEnumerable<string> subEvents) { Check.NotNull(alert, nameof(alert)); Check.NotNull(subEvents, nameof(subEvents)); var EventTypesMasterList = await _eventTypeRepository.GetListAsync(); foreach (var subEvent in subEvents) { var eventType = EventTypesMasterList.FirstOrDefault(e => e.Name == subEvent); alert.AddSubEvent(eventType.Id); // If eventType object is null, you will get null reference exception. } return true; }
-
0
Thanks team. Let me try both of your suggestions today. @liangshiwei I also made the repository private.
-
0
Thanks @Cotur and @liangshiwei. with your suggestions I'm able to save subEvents.
changes worked 1.
var alert = await _alertRepository.InsertAsync(newAlert, true); // AutoSave parameter is true
2. you use automapper map object, but you did not initialize the collection in the parameterless constructor.null reference exception will be thrown when addingThanks for your help.