Many to Many Relation -> Error While Inserting New Entry #179 | Support Center | ABP Commercial

roop.yekollu@atlasair.com created 4 years ago

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

liangshiwei created 4 years ago
Support Team Fullstack Developer

Try use AddSubEvent method to replace the following

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

roop.yekollu@atlasair.com created 4 years ago

Hi,

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

liangshiwei created 4 years ago

Support Team Fullstack Developer

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

roop.yekollu@atlasair.com created 4 years ago

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

roop.yekollu@atlasair.com created 4 years ago

Any update on this please.
0

alper created 4 years ago
Support Team Director

I'll try to reproduce it
0

liangshiwei created 4 years ago
Support Team Fullstack Developer

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.yekollu@atlasair.com created 4 years ago

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?

liangshiwei created 4 years ago

Support Team Fullstack Developer

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

roop.yekollu@atlasair.com created 4 years ago

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 4 years ago
Support Team Fullstack Developer

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
roop.yekollu@atlasair.com created 4 years ago
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

cotur created 4 years ago

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

liangshiwei created 4 years ago
Support Team Fullstack Developer

Hi @roop.yekollu@atlasair.com

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

liangshiwei created 4 years ago

Support Team Fullstack Developer

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

roop.yekollu@atlasair.com created 4 years ago

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

roop.yekollu@atlasair.com created 4 years ago

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.

Have an answer to this question? Log in and write your answer.