Open Closed

Many to Many Relation -> Error While Inserting New Entry #179


0
[email protected] created

Hi,

  1. 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;

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

            });
  1. 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.
  2. 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
    liangshiwei created
    Support Team

    Try use AddSubEvent method to replace the following

    await _alertSubEventTypeRepository.InsertAsync(new AlertSubEventType(alert.Id, eventId.Id));

  • 0
    [email protected] created

    Hi,

    I don't see Add() method in the _alertSubEventTypeRepository If you are referring to use Add() method instead of InsertAsync().

  • 0
    liangshiwei created
    Support Team

    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
    [email protected] created

    Tried but no luck. Failed with same FOREIGN KEY constraint error, now from the newly added line

  • 0
    [email protected] created

    Any update on this please.

  • 0
    alper created
    Support Team

    I'll try to reproduce it

  • 0
    liangshiwei created
    Support Team

    I tried to use your code, but did not reproduce the problem.

    I didn't customize the alert repository for simplicity, After inserting the alert entity, I called the save change method of the unit of work, because the code you provided uses the repository to query the alert entity.

  • 0
    roop.yeko[email protected] created

    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
    liangshiwei created
    Support Team

    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 is 0. 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 a combined 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
    [email protected] created

    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
    liangshiwei created
    Support Team

    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
    [email protected] created

    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
    cotur created
    Support Team

    Hi @[email protected],

    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
    liangshiwei created
    Support Team

    Hi @[email protected]

    I suggest you delete the repository on github, because your commercial license is included in the project.

  • 0
    liangshiwei created
    Support Team

    Hi @[email protected]

    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
    [email protected] created

    Thanks team. Let me try both of your suggestions today. @liangshiwei I also made the repository private.

  • 0
    [email protected] created

    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 adding

    Thanks for your help.