Composite keys with ORMLite and Identity column

1

Hi,

i have composite primary keys today which is not supported by ORMLite.
I have added a new identity column as part of the primary key to solve this Unique Constraint issue.
But the problem is that when i try to insert a new item i get this errormessage

An explicit value for the identity column in table ‘dbo.RegisteredUsers’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

This happens even if the identity column is null (IdentityId). Also tried to add [AutoIncrement] but without any success

     [ServiceStack.DataAnnotations.AutoIncrement]
    public int IdentityId { get; set; }

The other columns as are part of the primary key (ApplicationId and CustomerId is set as guid and are filled with data).

I have tried now for 2 days to get around this problem with any success so please guide me.

2

The error message is indicating you need to turn on IDENTITY_INSERT:

SET IDENTITY_INSERT tableA ON
3

Hi mythz,
but the thing is that i dont want to use Identity_insert.
This is only used when you want to put your own value in the identity column. I want it to be auto incremented.
Thats why i set null as my value on the identity column.

Regards Kristian

4

Please provide a complete code example that repro’s the issue. Can you also turn on Debug Console Logging so you can provide the generated SQL:

LogManager.LogFactory = new ConsoleLogFactory(debugEnabled:true);
5

Hi, i have created a stand alone example now… WHere should i upload it?

6

Upload it to a GitHub project.

7

Hi,
here is the repo

https://github.com/bokamera/APITestRepo/

To test it post this

{
    "CompanyId": "00000000-0000-0000-0000-000000000001",
    "Firstname": "Will",
    "Lastname": "Smith",
    "Email": "demo9999@bokamera.se",
    "Phone":"0761173363"
  
}

header
x-api-key : 53eeca6f-9eda-49cf-8832-7def10e355cf

thx!

8

Please provide an isolated stand-alone example of the OrmLite query that reproduces the issue, not an entire Application where the query is hiding behind an API. It shouldn’t need to be anything more than the table class definition and the client code that throws the Exception.

When creating the example please run it with:

LogManager.LogFactory = new ConsoleLogFactory(debugEnabled:true);

And provide the generated SQL.

9

Hi,
i have now added a console application inside the project whice do the save and generates the error.
Im not sure how to use the LogManager but added that line aswell in the console app.

Regards Kristian

11

If the Issue is the [AutoIncrement] column not being populated, that can only be applied to the primary key which is the Id property not the IdentityId property.

So you need to use the [PrimaryKey] property to make it the Primary Key:

public partial class Customer 
{
    [Alias("RegisteredUserId")]
    public Guid Id
    {
        get; set;
    }

    [AutoIncrement]
    [PrimaryKey]
    [Alias("Id")]
    public int IdentityId { get; set; }
}

The [AutoIncrement] property on a non-primary key is not supported.

12

Thx Mythz,

that works but when i do this cgabge the problems with selecting the customers with autoquery i get this error message

Operand type clash: uniqueidentifier is incompatible with int

This worked before i change the IdentityId column to the primary key

The Id property is Guid and the identity column is int. I guess it’s trying to map those together somehow.

Any suggestions on this?

13

The names used are counter intuitive as you’re using Id for a column that’s not the PrimaryKey, try using just using the DB names:

public partial class Customer 
{
    [AutoIncrement]
    public int Id { get; set; }

    public Guid RegisteredUserId { get; set; }
}
14

I will try that.
I can see it’s trying to join identity column with registereduserid column.

FROM "RegisteredUsers" LEFT JOIN "CompanyRegisteredUsers" ON("RegisteredUsers"."IdentityId" = "CompanyRegisteredUsers"."RegisteredUserId")WHERE "RegisteredUsers"."ApplicationId" = @0 AND ("CompanyRegisteredUsers"."ApplicationId" = @1)

I will test it and get back to you

15

Didnt help to change the column back to

public Guid RegisteredUserId { get; set; }

it is still trying to join to the identity column for some reason (maybe because it’s primary key).
Can i change this behavior in autoquery?

16

AutoQuery uses OrmLite’s JOIN conventions, that’s not overridable.