1

Is it possible to have a primary key and another field which is not the primary key with the "auto increment" function in Entity Framework?

I found this on the web, tried it, but it does not work:

public int Id { get; set; }

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

In this case, the Id is always 0.

So now i'm back to this: Id is the primary key and i use MAX(Id) + 1 to increment the ImageId integer.

Update: I was even thinking of creating another table just for the ImageId's. But i'm not sure it this would be an overkill or not.

abatishchev
  • 98,240
  • 88
  • 296
  • 433
Jo Smo
  • 6,923
  • 9
  • 47
  • 67
  • 1
    How the schema looks like? – abatishchev Oct 23 '15 at 18:48
  • The MSSQL schema? Now the `Id` is the primary key and the `ImageId` is an integer. I can't figure out how to make the `ImageId` field to auto increment other then `MAX(Id) + 1` which i don't know if it's a good idea. – Jo Smo Oct 23 '15 at 18:57
  • MS SQL only allows a single identity column per table. – afrazier Oct 23 '15 at 18:58
  • @afrazier I know... I just wanted to list what i have already tried so that it wouldn't become an answer as i already know that it can't solve the issue. – Jo Smo Oct 23 '15 at 19:03
  • 1
    I'd use after insert trigger or make it just a part of the insert/update. Or better - a microservice to keep track of it. – abatishchev Oct 23 '15 at 19:14
  • @abatishchev the trigger sounds good too. – Jo Smo Oct 23 '15 at 19:47

2 Answers2

1

I Tried this sometime ago. MSSQL does support it. From memory EF didnt allow the definition either.

My solution: I created a secondary table I called it IDPool. With the sole purpose of generating a unique sequence of ids. I used that value in the main table. It was a scenario where i could use GUID either. Otherwise Guid is the obvious alternative.

EDIT: Tip To make things easier/safer, Use a second Context in parallel. This second context is for getting Ids, You can Commit without fear of interfering with current update in the main context.

      var miniRep = luw.GetRepositoryMini<IdPool>();  // mini context managed here.
      var nextrec = new IdPool()
      miniRep.Add(nextrec);
      miniRep.SaveChanges();
      return nextrec.Id
phil soady
  • 11,043
  • 5
  • 50
  • 95
  • So far this looks the best solution to me as well... If no one else will come up with a better solution, i will accept your answer as my solution... Will give it a day or two. – Jo Smo Oct 23 '15 at 19:07
0

Jo Smo,

Try this:

public static class ID
{
    // Enumeration for parameter in NewID() method.
    public enum Type { Customer, Vendor, Product, Transaction };
}

public class MyClass
{
    // Variables hold the last ID. This will need to be serialized
    // into your database.
    public int lastCustomerID;
    public int lastVendorID;
    public int lastProductID;
    public int lastTransactionID;

    // Updates last-ID variable and returns its value.
    public int NewID(ID.Type type)
    {
        switch (type)
        {
            case ID.Type.Customer:
                lastCustomerID++;
                return lastCustomerID;

            case ID.Type.Vendor:
                lastVendorID++;
                return lastVendorID;

            case ID.Type.Product:
                lastProductID++;
                return lastProductID;

            case ID.Type.Transaction:
                lastTransactionID++;
                return lastTransactionID;

            default:
                throw new ArgumentException("An invalid type was passed: " + type);
        }
    }

    private void AnyMethod()
    {
        // Generate new customer ID for new customer.
        int newCustomerID = NewID(ID.Type.Customer);

        // Now the ID is in a variable, and your last-ID variable is updated.
        // Be sure to serialize this data into your database, and deserialize
        // it when creating new instances.
    }
}
Tyler Pantuso
  • 835
  • 8
  • 16