EF & Automapper. Update nested collections

Question

I trying to update nested collection (Cities) of Country entity.

Just simple enitities and dto's:

// EF Models
public class Country
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<City> Cities { get; set; }
}

public class City
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int CountryId { get; set; }
    public int? Population { get; set; }

    public virtual Country Country { get; set; }
}

// DTo's
public class CountryData : IDTO
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<CityData> Cities { get; set; }
}

public class CityData : IDTO
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int CountryId { get; set; }
    public int? Population { get; set; }
}

And code itself (tested in console app for the sake of simplicity):

        using (var context = new Context())
        {
            // getting entity from db, reflect it to dto
            var countryDTO = context.Countries.FirstOrDefault(x => x.Id == 1).ToDTO<CountryData>();

            // add new city to dto 
            countryDTO.Cities.Add(new CityData 
                                      { 
                                          CountryId = countryDTO.Id, 
                                          Name = "new city", 
                                          Population = 100000 
                                      });

            // change existing city name
            countryDTO.Cities.FirstOrDefault(x => x.Id == 4).Name = "another name";

            // retrieving original entity from db
            var country = context.Countries.FirstOrDefault(x => x.Id == 1);

            // mapping 
            AutoMapper.Mapper.Map(countryDTO, country);

            // save and expecting ef to recognize changes
            context.SaveChanges();
        }

This code throws exception:

The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted.

even though entity after last mapping seems just fine and reflects all changes properly.

I've spent much time finding solution but got no result. Please help.

Inestimable

does country.cities[0].Id has value after mapping? if not EF tries to set null to foriegn key and that cause the issue – Dromedary

@esiprogrammer, yes it have. – Inestimable

Answer 1

The problem is the country you are retrieving from database already has some cities. When you use AutoMapper like this:

// mapping 
AutoMapper.Mapper.Map(countryDTO, country);

AutoMapper is doing something like creating an IColletion<City> correctly (with one city in your example), and assigning this brand new collection to your country.Cities property.

The problem is EntityFramework doesn't know what to do with the old collection of cities.

• Should it remove your old cities and assume only the new collection?
• Should it just merge the two lists and keep both in database?

In fact, EF cannot decide for you. If you want to keep using AutoMapper, you can customize your mapping like this:

// AutoMapper Profile
public class MyProfile : Profile
{

    protected override void Configure()
    {

        Mapper.CreateMap<CountryData, Country>()
            .ForMember(d => d.Cities, opt => opt.Ignore())
            .AfterMap(AddOrUpdateCities);
    }

    private void AddOrUpdateCities(CountryData dto, Country country)
    {
        foreach (var cityDTO in dto.Cities)
        {
            if (cityDTO.Id == 0)
            {
                country.Cities.Add(Mapper.Map<City>(cityDTO));
            }
            else
            {
                Mapper.Map(cityDTO, country.Cities.SingleOrDefault(c => c.Id == cityDTO.Id));
            }
        }
    }
}

The Ignore() configuration used for Cities makes AutoMapper just keep the original proxy reference built by EntityFramework.

Then we just use AfterMap() to invoke an action doing exactly what you thought:

• For new cities, we map from DTO to Entity (AutoMapper creates a new instance) and add it to country's collection.
• For existing cities, we use an overload of Map where we pass the existing entity as the second parameter, and the city proxy as first parameter, so AutoMapper just updates the existing entity's properties.

Then you can keep your original code:

using (var context = new Context())
    {
        // getting entity from db, reflect it to dto
        var countryDTO = context.Countries.FirstOrDefault(x => x.Id == 1).ToDTO<CountryData>();

        // add new city to dto 
        countryDTO.Cities.Add(new CityData 
                                  { 
                                      CountryId = countryDTO.Id, 
                                      Name = "new city", 
                                      Population = 100000 
                                  });

        // change existing city name
        countryDTO.Cities.FirstOrDefault(x => x.Id == 4).Name = "another name";

        // retrieving original entity from db
        var country = context.Countries.FirstOrDefault(x => x.Id == 1);

        // mapping 
        AutoMapper.Mapper.Map(countryDTO, country);

        // save and expecting ef to recognize changes
        context.SaveChanges();
    }

Rummy

it's still not clear for me, sorry. If after making changes with countryDTO.Cities I create newCities just like you wrote it have all cities, and adding them in foreach loop in country.Cities makes dublicates. If I clear cities after retrieving original entity and run foreach afterward I get same The operation failed: error. What I've missed? – Inestimable

@AkmalSalikhov I edit my code providing another way to configure automapper to do what you want. So the code that you get working correctly, I encapsulated in automapper, I think this is what you wanted. Now EF knows what to add, and what to update. – Rummy

Alisson, incapsulating function in automapper config is very helpful, thanks! But there is one thing. Your code is works well if I use .ForMember(dest => dest.Cities, src => src.Ignore()) , instead of UseDestinationValue. Using UseDestinationValue causing the same The relationship could not be changed error – Inestimable

hmm that's kind strange, but makes sense, since Ignore() makes your entities keep their original Cities property. I'm editing my answer as you mentioned, thanks. Also, you should make one answer as accepted, so future users could directly look into that answer. – Rummy

@Alisson proper solution for lack of EF core support – Loculus

@Alisson Is it also possible to delete entity from child collection in afterMap callback? – Chevalier

@Alisson Thanks, but after that I use Ignore(), Suddenly cities nested property be empty in AddOrUpdateCities method. Do you have any suggestion? – Zeidman

@ArashKarami add missed properties to dto? – Uproarious

when i try this i get a compilation error An object reference is required for the non-static field, method, or property 'Mapper.Map<... Does the add or update cities need to be a seperatec class with the mapping DI injected? – Only

I'm using this exact same solution but the "Add" is not being well tracked from EF Core. It's becoming State.Modified and not State.Added, so when it goes to the database, it's trying to run an UPDATE and not an INSERT. Any ideia what's wrong is happening and how to fix it? – Preference

Answer 2

This is not an answer per se to the OP, but anyone looking at a similar problem today should consider using AutoMapper.Collection. It provides support for these parent-child collection issues that used to require a lot of code to handle.

I apologize for not including a good solution or more detail, but I am only coming up to speed on it now. There is an excellent simple example right in the README.md displayed on the link above.

Using this requires a bit of a rewrite, but it drastically cuts down on the amount of code you have to write, especially if you're using EF and can make use of AutoMapper.Collection.EntityFramework.

Willams

And if you are using .NET Core, use: github.com/AutoMapper/AutoMapper.Collection.EFCore – Haworth

I just used the core AutoMapper.Collection package and it worked nicely! – Moy

doesn't support AutoMapper 11+ – Avocation

Answer 3

when save changes all cities are considered as added becasue EF didn't now about them till saving time. So EF tries to set null to foreign key of old city and insert it instead of update.

using ChangeTracker.Entries() you will find out what changes CRUD is going to be made by EF.

If you want just update existing city manually, you can simply do :

foreach (var city in country.cities)
{
    context.Cities.Attach(city); 
    context.Entry(city).State = EntityState.Modified;
}

context.SaveChanges();

Dromedary

Answer 4

It seems like I found solution:

var countryDTO = context.Countries.FirstOrDefault(x => x.Id == 1).ToDTO<CountryData>();
countryDTO.Cities.Add(new CityData { CountryId = countryDTO.Id, Name = "new city 2", Population = 100000 });
countryDTO.Cities.FirstOrDefault(x => x.Id == 11).Name = "another name";

var country = context.Countries.FirstOrDefault(x => x.Id == 1);

foreach (var cityDTO in countryDTO.Cities)
{
    if (cityDTO.Id == 0)
    {
        country.Cities.Add(cityDTO.ToEntity<City>());
    }
    else
    {
        AutoMapper.Mapper.Map(cityDTO, country.Cities.SingleOrDefault(c => c.Id == cityDTO.Id)); 
    }
}

AutoMapper.Mapper.Map(countryDTO, country);

context.SaveChanges();

this code updates edited items and add new ones. But maybe there are some pitfalls I cant detect for now?

Inestimable

why you are converting DTO to entity and than Entity to DTO in the same context? just simply edit your entity object and than convert the final result to DTO – Dromedary

@Dromedary I think he just simplified the code to illustrate his problem. He might convert to DTO in some Get method, then a post method receveis edited/new cities as DTOs and convert to Entity to save. – Rummy

@Alisson I don't think so, as he mentioned in his question "tested in console app for the sake of simplicity" . it's the actual code that he uses – Dromedary

@Alisson is right. Actually I work with an MVC 5 app. – Inestimable

Answer 5

Very Good solution of Alisson. Here is my solution... As We know EF does not know whether the request is for update or insert so what I would do is delete first with RemoveRange() method and send the collection to insert it again. In background this is how database works then we can emulate this behavior manually.

Here is the code:

//country object from request for example

var cities = dbcontext.Cities.Where(x=>x.countryId == country.Id);

dbcontext.Cities.RemoveRange(cities);

/* Now make the mappings and send the object this will make bulk insert into the table related */

Applegate

Answer 6

I spent some time coming up with a better solution for AutoMapper 11+, since currently there is no solution for EF Core and mapping relationship collections without the use of AfterMap(). This isn't as efficient as it could be (requires multiple enumeration), but it saves a lot of templating in mapping a lot of child relationships and supports conditions if the source and destination collections are not of the same order:

// AutoMapper Profile
public class MyProfile : Profile
{
  protected override void Configure()
  {
    Mapper.CreateMap<CountryData, Country>()
      .ForMember(d => d.Id, opt => opt.MapFrom(x => x.Id))
      // relationship collections must be ignored, CountryDataMappingAction will take care of it
      .ForMember(d => d.Cities, opt => opt.Ignore())
      .AfterMap<CountryDataMappingAction>();
  }

  public class CountryDataMappingAction : BaseCollectionMapperAction<CountryData, Country>
  {
    public override void Process(CountryData source, Country destination, ResolutionContext context)
    {
      MapCollection(source.Cities, destination.Cities, (x, y) => x.Id == y.Id, context);
    }
  }
}

public class BaseCollectionMapperAction<TSource, TDestination> : IMappingAction<TSource, TDestination>
{
    public void MapCollection<TCollectionSource, TCollectionDestination>(IEnumerable<TCollectionSource> sourceCollection, IEnumerable<TCollectionDestination> destCollection, Func<TCollectionSource, TCollectionDestination, bool> predicate, ResolutionContext context)
    {
        MapCollection(sourceCollection.ToList(), destCollection.ToList(), predicate, context);
    }

    public void MapCollection<TCollectionSource, TCollectionDestination>(IList<TCollectionSource> sourceList, IList<TCollectionDestination> destList, Func<TCollectionSource, TCollectionDestination, bool> predicate, ResolutionContext context)
    {
        for (var sourceIndex = 0; sourceIndex < sourceList.Count; sourceIndex++)
        {
            for (var destIndex = 0; sourceIndex < destList.Count; destIndex++)
            {
                var result = predicate(sourceList[sourceIndex], destList[destIndex]);
                if (result)
                {
                    destList[destIndex] = context.Mapper.Map(sourceList[sourceIndex], destList[destIndex]);
                    break;
                }
            }
        }
    }

    public virtual void Process(TSource source, TDestination destination, ResolutionContext context)
    {
        throw new NotImplementedException("You must provide a mapping implementation!");
    }
}

Avocation