Forum:  Bugs & Issues

Thread:  SaveEntity returning false for sequence key


jovball (User)   Posted on: 19-Sep-2016 22:45:48.
I am testing with LLBLGen v5.05. When I insert an entity that uses a sequence, I am getting false for the return value of SaveEntity even though the database insert is successful.

DDL for a test table.
Code:


CREATE SEQUENCE [dbo].[sq_AppRole]
AS [int]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2147483647
NO CACHE
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AppRole](
    [AppRoleID] [int] NOT NULL,
    [RoleName] [varchar](50) NOT NULL,
CONSTRAINT [PK_AppRole] PRIMARY KEY CLUSTERED
(
    [AppRoleID] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[AppRole] ADD CONSTRAINT [DF_AppRole_AppRoleID] DEFAULT (NEXT VALUE FOR [dbo].[sq_AppRole]) FOR [AppRoleID]
GO



Code for doing the insert.
Code:

            var result = false;

            var customer = new CustomerEntity();
            customer.CustomerId = "XXXXX";
            customer.CompanyName = "X Factor";

            using (var dataAdapter = new DataAccessAdapter())
            {
                result = dataAdapter.SaveEntity(customer, true);
            }

            Console.WriteLine(result); //shows true


            var appRole = new AppRoleEntity();
            appRole.RoleName = "AAAA";
            using (var dataAdapter = new DataAccessAdapter())
            {
                result = dataAdapter.SaveEntity(appRole, true);
            }

            Console.WriteLine(result); // shows false even though the insert was successful


I'm attaching a screenshot of the designer showing the sequence assignment to the field.
daelmo (Support Team)   Posted on: 20-Sep-2016 08:12:57.
You are using SQLServer 2012/2014. Did you set the compatibility level in the .config file?

Please check this documentation link to know more about how the compatibility level affects the way LLBLGen Framework fetches the generated ID depending on the SQLServer version you are using, and how to configure it.

By default the compatibility level is set to SqlServerCompatibilityLevel.SqlServer2005. So I think that the insert is performed ok, but then the fetch of the ID fails, so that's why the adapter returns false.


jovball (User)   Posted on: 20-Sep-2016 12:45:46.
The compatibility level is set correctly. Have you tried reproducing this with what I provided?

Code:

    <add key="SqlServerDQECompatibilityLevel" value="6" />
Otis (LLBLGen Pro Team)   Posted on: 20-Sep-2016 15:19:33.
Hmm. We'll look into it.

Otis (LLBLGen Pro Team)   Posted on: 21-Sep-2016 15:22:34.
I can't reproduce it in our tests (which had a test for this already, works fine) ->

Code:

[Test]
public void InsertEmployee()
{
    var toInsert = new EmployeeEntity();
    toInsert.Name = "John D.";
    toInsert.DateOfBirth = new DateTime(1970, 1, 1);
    toInsert.StartTime = new DateTimeOffset(new DateTime(2014, 1, 1), new TimeSpan(11, 0, 0));
    using(var adapter = new DataAccessAdapter())
    {
        Assert.IsTrue(adapter.SaveEntity(toInsert));
    }
    Assert.IsTrue(toInsert.Id > 0);
}


SQL:
Code:

SELECT @p2=NEXT VALUE FOR [SequenceDB].[dbo].[SEQ_Central]; INSERT INTO [SequenceTester].[dbo].[Employee] ([DateOfBirth], [ID], [Name], [StartTime]) VALUES (@p1, @p2, @p3, @p4)


Will test your test table now, perhaps it's something else...
Otis (LLBLGen Pro Team)   Posted on: 21-Sep-2016 15:40:01.
Your table works too:

Code:
[Test]
public void InsertAppRole()
{
    var toInsert = new AppRoleEntity() {RoleName = "AAAA"};
    using(var adapter = new DataAccessAdapter())
    {
        Assert.IsTrue(adapter.SaveEntity(toInsert, true));
    }
    Assert.IsTrue(toInsert.AppRoleId > 0);
}


SQL:

Code:

SELECT @p1=NEXT VALUE FOR [SequenceTester].[dbo].[sq_AppRole]; INSERT INTO [SequenceTester].[dbo].[AppRole] ([AppRoleID], [RoleName]) VALUES (@p1, @p2)

SELECT [SequenceTester].[dbo].[AppRole].[AppRoleID] AS [AppRoleId],
     [SequenceTester].[dbo].[AppRole].[RoleName]
FROM [SequenceTester].[dbo].[AppRole]
WHERE (([SequenceTester].[dbo].[AppRole].[AppRoleID] = @p1))


So what causes it to return false in your case is a bit odd. It might be the fetch fails (as you ordered it to refetch). Could you check the sql that's being executed and whether these queries work?


jovball (User)   Posted on: 22-Sep-2016 03:17:24.
It's a problem on my end. Confused

I'm not sure what happened or how but the PersistenceInfoProvider.cs file was not correct and did not have the sequence assignment.

Once that was fixed, everything was fine. I'm _almost_ certain that I generated the code several times while I was testing but I can't really prove it.