Search code examples
postgresqlentity-framework-corenpgsql

Database operation expected to affect 1 row(s) but actually affected 0 row(s) with entity framework


I have the following table:

enter image description here

And I have the following trigger:

CREATE TRIGGER check_insertion_to_pushes_table
    BEFORE INSERT
    ON "Pushes"
    FOR EACH ROW
EXECUTE PROCEDURE trg_insert_failed_push();

CREATE or replace FUNCTION trg_insert_failed_push()
    RETURNS trigger AS
$func$
BEGIN
    IF (NEW."Sent" = false) THEN
        IF EXISTS(
                SELECT *
                FROM "Pushes"
                where "Sent" = false
                  and "CustomerId" = NEW."CustomerId"
                  and "PushTemplateId" = NEW."PushTemplateId"
            )
        THEN
            RETURN NULL;
        END IF;
        RETURN NEW;
    ELSE
        RETURN NEW;
    end if;
END
$func$ LANGUAGE plpgsql;

If there is row in the DB where CustomerId and PushTemplateId and Sent are equal to new row and Sent is false I would like to pass insertion.

And I have the following test to check how it works:

    public class Tests
    {
        private IPushRepository _pushRepository;
        [NUnit.Framework.SetUp]
        public void Setup()
        {
            var confBuilder = new ConfigurationBuilder();
            var configuration = confBuilder.AddJsonFile("/home/aleksej/projects/makeapppushernet/TestProject/appsettings.LocalToProdDb.json").Build();
            _pushRepository = new PushRepository(new ApplicationDbContext(configuration));

        }

        [Test]
        public async Task Test1()
        {
            var push = new Push
            {
                CustomerId = 69164,
                Sent =  false,
                PackageId = "com.kek.lol",
                Category = "betting",
                Advertiser = "Advertiser",
                TemplateType = "opened_and_not_registration",
                IntervalType = "minutes",
                BottomDateTimeBorder =  90,
                TopDateTimeBorder = 60,
                ClientStartDateTime = DateTime.Now,
                FCMResponse = "hello",
                CreatedAt = DateTime.Now,
                LangCode = "En",
                PushBody = "Hello",
                PushTitle = "Hello",
                PushTemplateId = 15 
            };

            var pushesList = new List<Push>
            {
                push
            };

            await _pushRepository.SaveAsync(pushesList);

            Assert.Pass();
        }
    }

If I set false for Sent in the test I have the following exception:

Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.

If I set true I have nothing. It just passes insertion.

Update

Ok, with the help of Shay Rojansky's answer I have the following trigger code:

CREATE TRIGGER check_insertion_to_failed_pushes_table
    BEFORE INSERT
    ON "FailedPushes"
    FOR EACH ROW
EXECUTE PROCEDURE trg_insert_failed_push();

CREATE or replace FUNCTION trg_insert_failed_push()
    RETURNS trigger AS
$func$
DECLARE
    push        "FailedPushes"%ROWTYPE;
    old_push_id numeric;
BEGIN
    old_push_id = (SELECT "FailedPushId"
                   FROM "FailedPushes"
                   where "CustomerId" = NEW."CustomerId"
                     and "PushTemplateId" = NEW."PushTemplateId");
    push := new;

    IF (old_push_id != 0)
    THEN
        push."FailedPushId" = old_push_id;

        DELETE
        FROM "FailedPushes"
        where "CustomerId" = NEW."CustomerId"
          and "PushTemplateId" = NEW."PushTemplateId";

        return push;
    END IF;

    push."FailedPushId" = (SELECT count(*) FROM "FailedPushes")::numeric + 1;

    return push;
END
$func$ LANGUAGE plpgsql;

Maybe not very elegant but it works.


Solution

  • You are in effect configuring PostgreSQL to ignore the INSERT under certain conditions, but EF Core isn't aware of this in any way. When you tell EF Core to add a new row, it expects for that to actually happen in the database. If the entity has any database-generated columns (identity, serial), EF Core also expects to receive the their values for the newly-inserted row (and will populate them back into the entity's CLR instance).

    So AFAIK you can't just tell the database to ignore the INSERT and expect everything to work...

    See this issue on EF Core upsert support which is somewhat related.