CODEWITHSUNDEEP
c#entity-frameworkentity-framework-6
gabosl
gabosl

Reputation: 41

Auto Increment ID in Composite key Entity Framework

I have two class like these

public partial class Master
{
        [Key, Column(Order = 0)]
        public int idmaster { get; set; }

       /*More fields*/
}


public partial class Detail
{
        [Key, Column(Order = 0)]
        public int idmaster { get; set; }

        [Key, Column(Order = 1)]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int id { get; set; }
}

I want to auto-increment the ID in this way:

|----------|-----------|
|MasterID  | ID        |
------------------------
|         1|          1|
|         1|          2|
|         1|          3|
|         1|          4|
|         2|          1|
|         2|          2|
|         2|          3|
|         2|          4|
------------------------

How I can do this in Entity Framework

A lot of thanks for your help

Edit: Actually I have this but I want only increment ID as I mentioned:

|----------|-----------|
|MasterID  | ID        |
------------------------
|         1|          1|
|         1|          2|
|         1|          3|
|         1|          4|
|         2|          5|
|         2|          6|
|         2|          7|
|         2|          8|
------------------------

I know that I can do via code but I wanted to know if there are another way to do it.

Upvotes: 4

Views: 1332

Answers (1)

Meola
Meola

Reputation: 68

If you can, use a trigger in your database.

-- TSQL in SQL Server 2008+
CREATE TRIGGER tr_Detail_autoidentity ON Detail
INSTEAD OF
INSERT
AS
BEGIN
  INSERT INTO Detail(idmaster, id) 
  SELECT inserted.idmaster, isnull((SELECT max(id) FROM Detail WHERE idmaster = inserted.idmaster), 0) +  ROW_NUMBER() OVER (PARTITION BY idmaster ORDER BY id) 
FROM inserted
END
--I Love this one, it work for multiline insert. May need a lock for multiuser, I'm not sure.

Upvotes: 2

Related Questions