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

问题描述

我是在实现一个数据库审计线索，从而CRUD操作通过我的控制器在我的Web API项目进行序列化将新老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.

一旦序对象的创建和存储过程来揭露它，我创建了以下类：

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屋！