Question

When we try to insert items into table with index after deleting the same items with the same index value and try to do it in the same transaction, insert command will be executed before delete command. But in the code we delete items before creation their copies. I provide test code for this issue below.

using System.Linq;
using Xtensive.Orm;
using Xtensive.Orm.Configuration;

internal class Program
{
    private static void Main(string[] args)
    {
        var dc = new DomainConfiguration("sqlserver",
            "Data Source=.; Initial Catalog=DO40-Tests; Integrated Security=True;");

        dc.Types.Register(typeof(TestEntity));

        dc.UpgradeMode = DomainUpgradeMode.Recreate;

        using (var d = Domain.Build(dc))
        {
            using (var s = d.OpenSession())
            using (s.Activate())
            using (var t = s.OpenTransaction())
            {
                for (int i = 0; i < 18; i++)
                {
                    new TestEntity(s) { Name = $"test{i}", SysName = "test" };
                }

                t.Complete();
            }

            using (var s = d.OpenSession())
            using (s.Activate())
            using (var t = s.OpenTransaction())
            {
                RemoveEntities(d);

                CreateEntities(d, s);

                t.Complete();
            }
        }
    }

    private static void RemoveEntities(Domain d)
    {
            var entities = Query.All<TestEntity>().ToList();

            Session.Current.Remove(entities);
    }

    private static void CreateEntities(Domain d, Session s)
    {
            for (int i = 0; i < 18; i++)
            {
                new TestEntity(s) { Name = $"test{i}", SysName = "test" };
            }
    }

    [HierarchyRoot]
    [Index(nameof(Name), Unique = true)]
    public class TestEntity : Entity
    {
        /// <summary>Initializes a new instance of this class.</summary>
        /// <param name="session">The session.</param>
        public TestEntity(Session session) : base(session)
        {
        }

        [Key]
        [Field]
        public int Id { get; set; }

        /// <summary>
        /// Name
        /// </summary>
        [Field]
        public string Name { get; set; }

        /// <summary>
        /// System name
        /// </summary>
        [Field]
        public string SysName { get; set; }
    }
}

Answer 1

Hello boldhen,

This is not a bug. Some storages require such way on operation execution. Otherwise it may does't work at all. we have two ways to persist unsaved changes:

1) Delete -> Update -> Insert. We do this for curtain storages like PostgreSQL or Oracle because they provide deferrable foreign keys. (why it is important I'll describe later)

2) Insert-> Update-> Delete. This is sequence for the storages which does not support deferrable foreign keys.

Delete and Update (Entity remove and entity property change operation respectively) performs operation with existing rows. Insert (new Entity instance creation) adds new row to database. Stand-alone entities without any inbound or outbound references are extremely rare so and when somebody creates an entity he wants the entity to have reference to another entity or he wants the entity to be referenced from another entity. This is the most general situation when working with entities so it should work any time.

Situation with INSERTs looks like:

If storage supports deferrable foreign keys we can update rows before inserting new rows to which updated rows have a reference. So we are free to insert before updates or after
If storage has no such feature we have to insert rows before updates because some updates probably have reference to the new rows. In this case we are bound to INSERT-then-UPDATE way of saving changes.

Now DETELE. Basically, removing entity is removing entity itself and in most cases cleaning up references to it by setting them to null. Generally, remove is one DELETE for the entity and up to several UPDATEs of the references.

Having deferrable foreign keys supported gives us ability to preform deletion as DELETE-then-UPDATE operation. For the rest of storage we had to null all the reference to the row we are about to delete by UPDATE and then delete the row. So we are bound to UPDATE-then-DELETE way to saving changes.

These two cases combination shows that in case of deferrable foreign keys we are free to save changes in any sequence of INSERT, UPDATE, DELETE but DELETE-UPDATE-INSERT gives more because it covers the case you've mentioned. When foreign keys has no such advantage we need in INSERT-UPDATE and UPDATE-DELETE, a combine of them gives us the only way - INSERT-UPDATE-DELETE.

That was why it designed curtain way, pretty long though but informative. Now what you can do for the cases when you basically recreate some entity with new key and same unique field value. We provide manual way to perform saving changes - Session.SaveChanges() method. When you now that you are recreating a row just save deletions manually and then let the rest be automatic.

Subscription:

Related questions