Entity Framework to Auto Increment Non Identity Column / Concurrency Issue

Question

I have two tables:

CREATE TABLE [dbo].[Customers] (
    [CustomerId]  INT            IDENTITY (1, 1) NOT NULL,
    CONSTRAINT [PK_dbo.Customers] PRIMARY KEY CLUSTERED ([CustomerId] ASC)
);

CREATE TABLE [dbo].[Campaigns] (
    [Id]           INT            IDENTITY (1, 1) NOT NULL,
    [CustomerId]   INT            NULL,
    [CampaignId]   INT            NULL,
    CONSTRAINT [PK_dbo.Campaigns] PRIMARY KEY CLUSTERED ([Id] ASC)
);

Campaigns.CampaignId is unique to every CustomerId; therefore, it cannot be an identity. So from my web app, I need to auto increment the CampaignId upon Campaign creation. In the past, I've had to obtain a lock in a single transaction to obtain the next highest and issue an insert. How would I accomplish the same thing in EF without having to worry about or effectively manage concurrency?

In the Campaign controller, I have this (UserContext is a static helper class that retrieves the user's current CustomerId and db is my DbContext):

public ActionResult Create(Campaign campaign)
{
    if (ModelState.IsValid)
    {
        int customerId = UserContext.customerId;
        int maxCampaignId = db.Campaigns.Where(c => c.CustomerId == customerId).Max(c => c.CampaignId);
        campaign.CampaignId = maxCampaignId + 1;
        db.Campaigns.Add(campaign);
        db.SaveChanges();
        return RedirectToAction("Index");
    }
    return View(campaign);
}

But wouldn't this risk duplicate values per CustomerId in a high concurrency environment?

EDIT:

I forgot to mention that a guid was not an option. The Ids have to be integers.

EDIT 2:

I forgot to mention that there is also a Users table which can have the same CustomerId. Users can create multiple Campaigns with the same CustomerId which causes the potential for concurrency issues.

score 0 · Answer 1 · edited May 23 '17 at 10:32

0

You may want to look into a HiLo pattern, or just use Guid.NewGuid() instead of incrementing.

See: HiLO for the Entity Framework

What's the Hi/Lo algorithm?

edited May 23 '17 at 10:32

Community

1
1

answered Jul 12 '13 at 19:59

Dodecapus

391
3
10

I'll check that out. I forgot to mention that a guid was not an option. The Ids have to be integers. – Jeff Jul 12 '13 at 20:04
I'm not sure if the HiLo algorithm will work for me. Few questions: Is the `CustomerId` basically the Hi parameter, and the `CampaignId` the Lo parameter? Will multiple Customers be able to have duplicate `CampaignId`s? If I don't set the max Lo value high enough, does it mean that Customers have a limited amount of `CampaignId`s they can have assigned before a new `CustomerId` is issued? – Jeff Jul 12 '13 at 20:27
Well you might be able to use the CustomerId as a Hi parameter, but campaignId is the id you are trying to create with the HiLo algorithm. After reading your question again and thinking more about it, it doesn't look like you will have to worry about concurrency in your situation since hopefully the single customer is only going to create one campaign at a time. I'm assuming here that it's ok if customer1 and customer2 both have a campaignid of 1. – Dodecapus Jul 12 '13 at 20:47
I forgot to mention that there's a separate `Users` table. `Customers` to `Users` is a 1 to many relationship. Multiple `Users` can add `Campaign`s with the same `CustomerId`. That is where the concurrency problem lies. Upon further research, I think encapsulating the EF calls within a transaction scope essentially solves the problem since the default isolation level for a transaction scope is serializable. – Jeff Jul 12 '13 at 20:56
And you are correct - it is ok if Customer 1 and Customer 2 have a CampaignId of 1. But Customer 1 and Customer 2 could have multiple users each creating campaigns. – Jeff Jul 12 '13 at 21:00
http://stackoverflow.com/questions/8309088/increment-field-in-entity-framework-depending-on-value-of-another-field may also be an option for you. – Dodecapus Jul 12 '13 at 21:06

Entity Framework to Auto Increment Non Identity Column / Concurrency Issue

1 Answers1