none
How to implement Lock in "Entity Framework" RRS feed

  • Question

  • Hi All,

    My query is about locking in Entity Framework (We are using EF 6.0 (runtime version is v4.0.30319) for our web development)

    I have a ref column other than a primary column in table, which need to update from One for every new insert.

    My Query is: Need to prevent to increment Ref from same number at same time from different user, is the below solution is fine or is there any better way to implement this?

    Please guide us.

    Thanks in advance.

    using

    (var scope = new TransactionScope(TransactionScopeOption.Required,

    new TransactionOptions { IsolationLevel = IsolationLevel.Snapshot }))

    {

    // do something with EF here

    int maxRef = MainDB.FactorLists.Max(f => f.FactorRef);

    FactorList factorListNew1 = new FactorList();

    factorListNew1.FactorRef = ++maxRef;

    MainDB.SaveChanges();

    scope.Complete();

    }


    Thanks Utshek Jain

    Friday, July 8, 2016 10:48 AM

Answers

  • Now situation is while adding new rows from new UI that is web application. Now we need to make this as unique while every insertion and also don't want to use any SP as we are totally working though Entity Framework.

    The approach I have given you will work, as I have used the approach using EF for various regions that had a running region count for a record added to the application for each region, which was a Web based solution

    BTW, EF's engine  uses the internal MS SQL Server Stored Procedure  to submit T-SQL to be executed by the DB engine.

    • Marked as answer by Utshek Tuesday, July 12, 2016 6:32 AM
    Monday, July 11, 2016 12:46 PM

All replies

  • Why wouldn't just use a separate table that has one column in it call it Count using an auto-incremented primary-key? You add a record to the table, the new record gets  the next sequential count key number assigned to it persisted to the DB, and the key is populated back to its EF object's property by EF after the insert of the record.

    Therefore,  you can address the EF object right there in memory and get the count-key and apply it to the other object's property about some next sequential count thing.

    You can wrap it all in a System.Transaction in case it blows up where the count record and all are rolled back to their previous state.

    Saturday, July 9, 2016 3:10 PM
  • Hi Utshek,

    >> Need to prevent to increment Ref from same number at same time from different user, is the below solution is fine or is there any better way to implement this?

    We could use IDENTITY to implement it. it will Increment in your database.

    CREATE TABLE [dbo].[tbl_SystemAccessList] (
        [Id]        INT          NOT NULL,
        [StudentID] VARCHAR (50) NULL,
        [ClassID]   VARCHAR (50) NULL,
        [FactorRef]    INT          IDENTITY (1, 1) NOT NULL,
        PRIMARY KEY CLUSTERED ([Id] ASC)
    );

    For more information about IDENTITY, please refer to:

    https://msdn.microsoft.com/en-us/library/ms186775.aspx

    In addition, we could create a stored procedure that increments the FactorRef numbers and returns a new one to you.  So you will need to create a table to manage the FactorRefs.  So it will need to track the next FactorRef number to use for the Message table, for each vendor and log type.  It will pre-increment the number, then return that number.  You will need to set the transaction isolation level to serializable so that two simultaneous calls to the stored procedure will result in unique values.

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 11, 2016 3:39 AM
    Moderator
  • Thanks Cole Wu

    For you response. As I can't use it as Identity and not null, because it has random but not repeated data which was imported some Excel file.

    Now situation is while adding new rows from new UI that is web application. Now we need to make this as unique while every insertion and also don't want to use any SP as we are totally working though Entity Framework.

    Thanks

    Utshek Jain


    Thanks Utshek Jain

    Monday, July 11, 2016 4:57 AM
  • Now situation is while adding new rows from new UI that is web application. Now we need to make this as unique while every insertion and also don't want to use any SP as we are totally working though Entity Framework.

    The approach I have given you will work, as I have used the approach using EF for various regions that had a running region count for a record added to the application for each region, which was a Web based solution

    BTW, EF's engine  uses the internal MS SQL Server Stored Procedure  to submit T-SQL to be executed by the DB engine.

    • Marked as answer by Utshek Tuesday, July 12, 2016 6:32 AM
    Monday, July 11, 2016 12:46 PM
  • Thanks a lot,

    Sure will implement this via new table.


    Thanks Utshek Jain

    Tuesday, July 12, 2016 6:32 AM