Auto increment property not key

Question

Is it possible to auto increment a property which is not the primary key in Entity Framework? What I have is a class which has an ID, but also a serial number:

public class Maintenance
{
    [Key]
    public int ID { get; set; }
    public int GroupID { get; set; }
    public int SerialNo { get; set; } 
}

I have an index on GroupID and SerialNo with a unique relation, making it impossible for a group to have the same serial number twice.

What I'm currently doing is checking .Max() for SerialNo within that group. I would however like it if the serial number got auto incremented. Is this possible?

I have been searching for a solution, but as far as I understand it, it's not possible to have 2 auto incrementing columns with entity framework, and that the auto incrementing column will always be the PK column.

Is there a way to set auto increment, or is there a better solution to the problem than using Max()? Using Max() may theoretically produce 2 values that are identical and will result in crashing the program while trying to insert (because of the unique index).

I'm using code first.

To clarify: I want to keep the ID as primary key, and compute the serial number while inserting.

UPDATE:

I've tried using [DatabaseGenerated(DatabaseGeneratedOption.Identity)] on SerialNo which results in entity framework wanting to change the primary key to this property. I also tried [DatabaseGenerated(DatabaseGeneratedOption.Computed)], which tries to insert NULL into the SerialNocolumn, and [DatabaseGenerated(DatabaseGeneratedOption.None)] which tries to insert 0, basically doing 'nothing'.

REQUESTED INFO:

This is an example of how the table could look:

|_ID_|_GroupID_|_SerialNo_|
|  1 |    1    |    1     |
|  2 |    1    |    2     |
|  3 |    2    |    1     |
|  4 |    1    |    3     |
|  5 |    2    |    2     |

Answer 1

Yes, you can use the following attribute:

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int SerialNo { get; set; } 

See HERE for a list of possible data annotations.

Answer 2

If you are using EF Core you should use the Sequence for the solution.

    public class Project
    {
      [Key]
      [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
      public int Id { get; set; }
      public int ProjectId { get; set; }
    }

And than you configure your onModelBuilder:

modelBuilder.HasSequence<int>("ProjectId").StartsAt(100).IncrementsBy(1);
modelBuilder.Entity< Project>().Property(o => o.ProjectId).HasDefaultValueSql("NEXT VALUE FOR ProjectId");

or you can read more on the docs.

Answer 3

If you are using sql server than also read this carefully

Can we have more than one identity columns in a table.

---

So i think below code compile but will give you an error when trying to migrate / create database.

you can also use fluent api approch like this

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Maintenance>().Property(a => a.GroupID 
                          ).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
    modelBuilder.Entity<Maintenance>().Property(a => a.SerialNo 
                          ).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
}