实体框架代码优先和 SQL Server 2012 序列 [英] Entity Framework Code First and SQL Server 2012 Sequences

问题描述

我正在实施数据库审计跟踪，通过我的 Web API 项目中的控制器执行的 CRUD 操作将序列化旧的和新的 poco 并存储它们的值以供以后检索(历史、回滚等...).

I was in the middle of implementing a database audit trail whereby CRUD operations performed through my controllers in my Web API project would serialize the old and new poco's and store their values for later retrieval (historical, rollback, etc...).

当我一切正常时，我不喜欢它在 POST 期间让我的控制器看起来如何，因为我最终不得不调用 SaveChanges() 两次，一次是为了获取插入的 ID实体，然后再次提交需要知道该 ID 的审计记录.

When I got it all working, I did not like how it made my controllers look during a POST because I ended up having to call SaveChanges() twice, once to get the ID for the inserted entity and then again to commit the audit record which needed to know that ID.

我开始将项目(仍处于起步阶段)转换为使用序列而不是标识列.这有一个额外的好处，可以进一步将我从 SQL Server 中抽象出来，虽然这不是一个真正的问题，但它也允许我减少提交的数量，并让我从控制器中提取该逻辑并将其填充到我的服务层中将我的控制器从存储库中抽象出来，并让我在这个填充"层中进行类似审计的工作.

I set out to convert the project (still in its infancy) to use sequences instead of identity columns. This has the added bonus of further abstracting me from SQL Server, though that is not really an issue, but it also allows me to reduce the number of commits and lets me pull that logic out of the controller and stuff it into my service layer which abstracts my controllers from the repositories and lets me do work like this auditing in this "shim" layer.

一旦创建了 Sequence 对象和一个存储过程来公开它，我就创建了以下类:

Once the Sequence object was created and a stored procedure to expose it, I created the following class:

public class SequentialIdProvider : ISequentialIdProvider
{
    private readonly IService<SequenceValue> _sequenceValueService;

    public SequentialIdProvider(IService<SequenceValue> sequenceValueService)
    {
        _sequenceValueService = sequenceValueService;
    }

    public int GetNextId()
    {
        var value = _sequenceValueService.SelectQuery("GetSequenceIds @numberOfIds", new SqlParameter("numberOfIds", SqlDbType.Int) { Value = 1 }).ToList();
        if (value.First() == null)
        {
            throw new Exception("Unable to retrieve the next id's from the sequence.");
        }

        return value.First().FirstValue;
    }

    public IList<int> GetNextIds(int numberOfIds)
    {
        var values = _sequenceValueService.SelectQuery("GetSequenceIds @numberOfIds", new SqlParameter("numberOfIds", SqlDbType.Int) { Value = numberOfIds }).ToList();
        if (values.First() == null)
        {
            throw new Exception("Unable to retrieve the next id's from the sequence.");
        }

        var list = new List<int>();
        for (var i = values.First().FirstValue; i <= values.First().LastValue; i++)
        {
            list.Add(i);
        }

        return list;
    }
}

它只是提供了两种获取 ID 的方法，单一的和范围的.

Which simply provides two ways to get IDs, a single and a range.

在第一组单元测试中，这一切都很好，但是当我开始在真实场景中测试它时，我很快意识到对 GetNextId() 的一次调用将返回相同的结果该上下文的生命周期的价值，直到 SaveChanges() 被调用，从而否定任何真正的好处.

This all worked great during the first set of unit tests but as soon as I started testing it in a real world scenario, I quickly realized that a single call to GetNextId() would return the same value for the life of that context, until SaveChanges() is called, thus negating any real benefit.

我不确定是否有办法解决这个问题，即创建第二个上下文(不是一个选项)或使用老式 ADO.NET 并进行直接 SQL 调用并使用 AutoMapper 来获得相同的最终结果.这些都不吸引我，所以我希望其他人有想法.

I am not sure if there is a way around this short of creating a second context (not an option) or going old school ADO.NET and making direct SQL calls and use AutoMapper to get to the same net result. Neither of these are appeal to me so I am hoping someone else has an idea.

推荐答案

不知道这是否对您有帮助，但这是我首先使用代码进行审计日志跟踪的方式.下面编码成一个继承自 DbContext 的类.

Don't know if this might help you, but this is how I did my audit log trail using code first. The following is coded into a class inheriting from DbContext.

在我的构造函数中，我有以下内容

in my constructor I have the following

IObjectContextAdapter objectContextAdapter = (this as IObjectContextAdapter);
objectContextAdapter.ObjectContext.SavingChanges += SavingChanges;

这是我之前设置的保存更改方法

This is my saving changes method wired up previously

void SavingChanges(object sender, EventArgs e) {
        Debug.Assert(sender != null, "Sender can't be null");
        Debug.Assert(sender is ObjectContext, "Sender not instance of ObjectContext");

        ObjectContext context = (sender as ObjectContext);
        IEnumerable<ObjectStateEntry> modifiedEntities = context.ObjectStateManager.GetObjectStateEntries(EntityState.Modified);
        IEnumerable<ObjectStateEntry> addedEntities = context.ObjectStateManager.GetObjectStateEntries(EntityState.Added);

        addedEntities.ToList().ForEach(a => {
            //Assign ids to objects that don't have
            if (a.Entity is IIdentity && (a.Entity as IIdentity).Id == Guid.Empty)
                (a.Entity as IIdentity).Id = Guid.NewGuid();

            this.Set<AuditLogEntry>().Add(AuditLogEntryFactory(a, _AddedEntry));
        });

        modifiedEntities.ToList().ForEach(m => {
            this.Set<AuditLogEntry>().Add(AuditLogEntryFactory(m, _ModifiedEntry));
        });
    }

这些是以前用来建立审计日志细节的方法

And these are the methods used previosly to build up the audit log details

private AuditLogEntry AuditLogEntryFactory(ObjectStateEntry entry, string entryType) {
        AuditLogEntry auditLogEntry = new AuditLogEntry() {
            EntryDate = DateTime.Now,
            EntryType = entryType,
            Id = Guid.NewGuid(),
            NewValues = AuditLogEntryNewValues(entry),
            Table = entry.EntitySet.Name,
            UserId = _UserId
        };

        if (entryType == _ModifiedEntry) auditLogEntry.OriginalValues = AuditLogEntryOriginalValues(entry);

        return auditLogEntry;
    }

    /// <summary>
    /// Creates a string of all modified properties for an entity.
    /// </summary>
    private string AuditLogEntryOriginalValues(ObjectStateEntry entry) {
        StringBuilder stringBuilder = new StringBuilder();

        entry.GetModifiedProperties().ToList().ForEach(m => {
            stringBuilder.Append(String.Format("{0} = {1},", m, entry.OriginalValues[m]));
        });

        return stringBuilder.ToString();
    }

    /// <summary>
    /// Creates a string of all modified properties' new values for an entity.
    /// </summary>
    private string AuditLogEntryNewValues(ObjectStateEntry entry) {
        StringBuilder stringBuilder = new StringBuilder();

        for (int i = 0; i < entry.CurrentValues.FieldCount; i++) {
            stringBuilder.Append(String.Format("{0} = {1},",
                entry.CurrentValues.GetName(i), entry.CurrentValues.GetValue(i)));
        }

        return stringBuilder.ToString();
    }

希望这可以为您指明一个可以帮助您解决问题的方向.

Hopefully this might point you into a direction that might help you solve your problem.

这篇关于实体框架代码优先和 SQL Server 2012 序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！