Interview Questions and Answers

public class Customer
{
    public int CustomerID { get; set; }
    public string Name { get; set; }
    [ConcurrencyCheck]
    public byte[] RowVersion { get; set; }
}

var customer = context.Customers
.SqlQuery("SELECT * FROM Customers WHERE CustomerID = @p0 FOR UPDATE", customerID)
.FirstOrDefault();

catch (DbUpdateConcurrencyException ex)
{
    var entry = ex.Entries.Single();
    var databaseValues = entry.GetDatabaseValues();
    var clientValues = entry.CurrentValues;
    var originalValues = entry.OriginalValues;

    // Merge the values and update the row
    entry.OriginalValues.SetValues(databaseValues);
    entry.CurrentValues.SetValues(clientValues);
    context.SaveChanges();
}

var customers = context.Customers.ToList();
foreach (var customer in customers)
{
    Console.WriteLine(customer.Orders.Count()); // Lazy loading
}

var customers = context.Customers.Include(c => c.Orders).ToList(); // Eager loading
foreach (var customer in customers)
{
    Console.WriteLine(customer.Orders.Count());
}

var customer = context.Customers.Find(1);
context.Entry(customer).Collection(c => c.Orders).Load(); // Explicit loading
Console.WriteLine(customer.Orders.Count());

using (var transaction = new TransactionScope())
{
    // Perform database operations here
}

Set the transaction isolation level to Serializable to enable pessimistic locking:

using (var transaction = new TransactionScope(
    TransactionScopeOption.Required,
    new TransactionOptions { IsolationLevel = IsolationLevel.Serializable }))
    {
    // Perform database operations here
    }

    Retrieve the database records to be locked and apply the lock using the ForUpdate
    method:

using (var context = new MyDbContext())
{
    var record = context.MyEntities.Where(e => e.Id == entityId).FirstOrDefault();
    context.Entry(record).State = EntityState.Modified;
    context.Database.ExecuteSqlCommand("SELECT 1 FROM MyEntities WITH (UPDLOCK) WHERE Id =
    @id", new SqlParameter("@id", entityId));
    context.SaveChanges();
}

using (var transaction = context.Database.BeginTransaction())
{
    try
    {
        // Modify entities in the context
        // ...

        // Save changes to the database without committing the transaction
        context.SaveChanges(false);

        // Perform additional operations
        // ...

        // Commit the transaction
        transaction.Commit();
    }
    catch (Exception)
    {
        // Roll back the transaction if an error occurs
        transaction.Rollback();
        context.AcceptAllChanges();
        throw;
    }
}

try
{
    context.SaveChanges();
}
catch (DbUpdateConcurrencyException ex)
{
    ex.Entries.Single().Reload();
    ex.Entries.Single().State = EntityState.Modified;

    // Set client values to override database values
    var clientValues = (MyEntity)ex.Entries.Single().Entity;
    var databaseValues = (MyEntity)ex.Entries.Single().GetDatabaseValues().ToObject();

    // Update client values with database values
    clientValues.Property1 = databaseValues.Property1;
    clientValues.Property2 = databaseValues.Property2;

    // Save changes with client wins strategy
    context.SaveChanges();
}

using (var context = new MyDbContext())
{
    var entity = context.MyEntities.FirstOrDefault();
    entity.Property1 = "New value";
    context.SaveChanges();
}

using (var context = new MyDbContext())
{
    var entity = context.MyEntities.FirstOrDefault();
    entity.Property1 = "New value";
    context.SaveChanges();
    context.AcceptAllChanges();
}