How to get entity save order from Entity Framework Core

Question

When Entity Framework Core bulk saves all your changes, it uses an internal priority computed by all your foreign keys to make sure everything is saved in the right order to prevent foreign key errors.

How can I get this priority list from Entity Framework?

I'm constructing a synchronizer that moves a lot of entities between servers. I need to send the entities in the right order because I cannot save everything in one go due to memory usage. I want to bulk save entities from the stream, but at the moment I got issues with foreign keys. It would be nice if I could use the work done by Entity Framework to get the correct save order.

Why not to disable fk check while syncing? For example in MySql it can be done by this command `SET SQL_SAFE_UPDATES = 0;` — Artur, Dec 22 '20 at 20:34
Wouldn't [this](https://stackoverflow.com/questions/159038/how-can-foreign-key-constraints-be-temporarily-disabled-using-t-sql) work for you? — Artur, Dec 23 '20 at 20:00
I think you need to provide some context of what you're currently doing — dcg, Dec 23 '20 at 20:19
Artur: No, I don't want to change the whole database every time I save. I want to save it in the correct order. Not sure if that would work properly with transactions eighter. — Atle S, Dec 24 '20 at 10:13
dbcontext.SomeEntity.Add(...) x 1000, dbcontext.SaveChanges(), dbcontext.ChangeTracker.Clear(), dbcontext.SomeOtherEntity.Add(...) x 1000, dbcontext.SaveChanges() etc. And since the entities have references to entities with foreign keys, I get an error. All data is sendt over REST on a stream, unpacked in small parts, saved and cleared to prevent using memory. So the server needs to know what entities to send first to the client based on references. Entity Framework computes this internally from the model when you save. But since I don't want to save everything in one go I have a problem — Atle S, Dec 24 '20 at 22:40

score 2 · Accepted Answer · answered Jan 14 '21 at 14:53

I've done some digging inside EF Core, and found that the sorting is done inside CommandBatchPreparer. This class has a protected method called TopologicalSort that sorts all queued commands for execution order. I could inherit from CommandBatchPreparer, but this is very specialized for ModificationCommand. So in my case it's not possible.

I've prepared my own entity sorter based on that information. Keep in mind that entities that have internal self referencing properties with ForeignKey need extra handling to sort based on internal references.

public class DbContextEntityProperty
{
    public PropertyInfo Property { get; set; }
    public Type Type { get; set; }
    public bool SelfReferencing { get; set; }
    public IEntityType EntityType { get; set; }
}

public List<DbContextEntityProperty> SortedEntityProperties()
{
    var result = new List<DbContextEntityProperty>();
    var properties = GetType().GetProperties()
        .Where(o => o.PropertyType.IsGenericType && (o.PropertyType.GetGenericTypeDefinition() == typeof(DbSet<>)))
        .Select(o =>
        {
            var t = o.PropertyType.GetGenericArguments()[0];
            return new DbContextEntityProperty { Property = o, Type = null, SelfReferencing = false,EntityType = Model.FindEntityType(t) };
        })
        .ToDictionary(o => o.EntityType);
    Action<DbContextEntityProperty> recursiveDependencyTraverser = null;
    recursiveDependencyTraverser = (p) =>
    {
        if (p.Type == null)
        {
            p.Type = p.EntityType.ClrType;
            foreach (var et in p.EntityType.GetDeclaredForeignKeys().Select(o => o.PrincipalEntityType))
                if (et.ClrType == p.Type)
                    p.SelfReferencing = true;
                else if (properties.TryGetValue(et, out var nextP))
                    recursiveDependencyTraverser(nextP);
            result.Add(p);
        }
    };
    foreach (var p in properties.Values)
        recursiveDependencyTraverser(p);
    return result;
}

score 0 · Answer 2 · answered Dec 26 '20 at 23:26

To programmatically determine the foreign keys in your context you can use something like

        Dictionary<string, Dictionary<string, IEnumerable<IForeignKey>>> foreignKeys = new Dictionary<string, Dictionary<string, IEnumerable<IForeignKey>>>();
        IEnumerable<IEntityType> ets = dbContext.Model.GetEntityTypes();
        foreach (var (et, prop) in from IEntityType et in ets
                                   let entityProps = et.GetProperties()
                                   from IProperty prop in entityProps
                                   select (et, prop))
        {
            IEnumerable<IForeignKey> fks = et.FindForeignKeys(prop);
            if(fks.Any())
            {
                if (!foreignKeys.Keys.Contains(et.DisplayName()))
                {
                    foreignKeys.Add(et.DisplayName(), new Dictionary<string, IEnumerable<IForeignKey>>());
                }

                foreignKeys[et.DisplayName()].Add(prop.Name, fks);
            }
        }

This will create a dictionary of tables names, each dictionary entry will be a dictionary of fields and the foreign keys attached to the field i.e.

IEnumerable<IForeignKey> fks = foreignKeys["TableName"]["FieldName"];

From this you should be able to work out what data is required for you to insert your records into the database.

Entity Framework already have an internal routine that sorts your entity saves based on this information. Is there a way to get hold of that computed information, not the model. I want to save in the same order as Entity Framework itself finds suitable. — Atle S, Dec 29 '20 at 12:16

How to get entity save order from Entity Framework Core

2 Answers2