CODEWITHSUNDEEP
c#sqlsql-serverentity-frameworkentity-framework-6
Juan Salvador Portugal
Juan Salvador Portugal

Reputation: 1329

Dependant increment primary key with Entity Framework

I'm working with C#, Entity Framework 6.1.3 code-first, SQL Server for the first time, and I'm having problems creating a dependent autoincrementing key.

I have this:

class One
{
        [Key]
        [Required]
        string exampleKey { get; set; }
        string otherProperty { get; set; }
}

class Two
{
        [Required]
        [Key]
        [Column(Order = 0 )]
        public string exampleKey { get; set; }
        [ForeignKey("exampleKey")]
        public virtual One one { get; set; }

        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Column(Order = 1)]
        [Required]
        public long Version { get; set; }

        public string otherProperty { get; set; }        
}

I'm interested in the Version property of class Two when you insert data it make ids like

Class One

|exampleKey|otherProperty|
|"test"    |"random data"|
|"test2"   |"more random"|

Class Two

|exampleKey|Version|otherProperty|
|"test"    |1      |"random data"|
|"test"    |2      |"more random"|
|"test2"   |3      |"random data"|
|"test2"   |4      |"more random"|

But I'm looking for something like this

|exampleKey|Version|otherProperty|
|"test"    |1      |"random data"|
|"test"    |2      |"more random"|
|"test2"   |1      |"random data"|
|"test2"   |2      |"more random"|

I am looking for a solution to this problem a long time ago, is it possible?

Thanks so, so much!

Upvotes: 0

Views: 43

Answers (1)

Xedni
Xedni

Reputation: 4715

Not easily, no. But you can derive which is the most recent by a datestamp or an identity column. Then whenever you retrieve your data, simply get the row with the most recent date/identity value.

You could also write a view which surfaces the behavior I just mentioned.

Something like this:

Fake Data

if object_id('dbo.Data') is not null drop table dbo.Data
create table dbo.Data
(
    RID int identity(1,1) primary key clustered,
    ExampleKey varchar(10),
    OtherProperty varchar(100)
)

-- initial insert
insert into dbo.Data (ExampleKey, OtherProperty)
values ('test', 'Random data'), ('test2', 'more random')

-- Second insert
insert into dbo.Data (ExampleKey, OtherProperty)
values ('test', 'Random data'), ('test2', 'more random')

View Approach

if object_id('dbo.vData') is not null drop view dbo.vData
go

create view dbo.vData
as

select 
    Version = row_number() over (partition by ExampleKey order by RID desc),
    ExampleKey,
    OtherProperty
from dbo.Data
go

select top 1000 *
from dbo.vData

Alternatives

If you NEED to persist it in the table when it's inserted, You'll probably need a trigger (which I wouldn't recommend).

Upvotes: 1

Related Questions