可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):
问题:
I am building an MVC 4 application, using EF 5.
I need to do an audit trail, ie log any changes that end users make.
I have asked this question a few times, but haven't really gotten a satisfying answer before. So I am adding a lot more details in hoping to get somewhere..
currently I have multiple repositories
ie
public class AuditZoneRepository : IAuditZoneRepository
{
private AISDbContext context = new AISDbContext();
public int Save(AuditZone model, ModelStateDictionary modelState)
{
if (model.Id == 0)
{
context.AuditZones.Add(model);
}
else
{
var recordToUpdate = context.AuditZones.FirstOrDefault(x => x.Id == model.Id);
if (recordToUpdate != null)
{
recordToUpdate.Description = model.Description;
recordToUpdate.Valid = model.Valid;
recordToUpdate.ModifiedDate = DateTime.Now;
}
}
try
{
context.SaveChanges();
return 1;
}
catch (Exception ex)
{
modelState.AddModelError("", "Database error has occured. Please try again later");
return -1;
}
}
}
public class PostcodesRepository : IPostcodesRepository
{
private AISDbContext context = new AISDbContext();
public int Save(Postcodes model, ModelStateDictionary modelState)
{
if (model.Id == 0)
{
context.Postcodes.Add(model);
}
else
{
var recordToUpdate = context.Postcodes.FirstOrDefault(x => x.Id == model.Id);
if (recordToUpdate != null)
{
recordToUpdate.Suburb = model.Suburb;
recordToUpdate.State = model.State;
recordToUpdate.Postcode = model.Postcode;
recordToUpdate.AuditZoneId = model.AuditZoneId;
recordToUpdate.ModifiedDate = DateTime.Now;
}
}
try
{
context.SaveChanges();
return 1;
}
catch (Exception ex)
{
modelState.AddModelError("", "Database error has occured. Please try again later");
return -1;
}
}
}
Now I know for me to add the code to check to see if there are any changes i need to add it in the try of the save. Before the context.SaveChanges().
But currently I have 10 repos. I don't really want to add code to 10 different places. As this code will do exactly the same thing. I want to somehow have a baseclass that the repos inherit from.
any help? any sample code? any pointers?
would be appreciated. I am sure other people would have done this before
I am mappying my keys, relationships and tables like so
public class AuditZoneMap : EntityTypeConfiguration<AuditZone>
{
public AuditZoneMap()
{
// Primary Key
HasKey(t => t.Id);
// Properties
Property(t => t.Description)
.HasMaxLength(100);
// Table & Column Mappings
ToTable("AuditZone");
Property(t => t.Id).HasColumnName("Id");
Property(t => t.Description).HasColumnName("Description");
Property(t => t.Valid).HasColumnName("Valid");
Property(t => t.CreatedDate).HasColumnName("CreatedDate");
Property(t => t.CreatedBy).HasColumnName("CreatedBy");
Property(t => t.ModifiedDate).HasColumnName("ModifiedDate");
Property(t => t.ModifiedBy).HasColumnName("ModifiedBy");
// Relationships
HasOptional(t => t.CreatedByUser)
.WithMany(t => t.CreatedByAuditZone)
.HasForeignKey(d => d.CreatedBy);
HasOptional(t => t.ModifiedByUser)
.WithMany(t => t.ModifiedByAuditZone)
.HasForeignKey(d => d.ModifiedBy);
}
}
回答1:
What I recommend you is to use the ChangeTracker property in EF.
Inside your DBContext.cs you will have this:
public class DBContext : DbContext
{
public DBContext () : base("DatabaseName")
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
}
public DbSet<YourPocoModelNameHere > YourPocoModelNameHere { get; set; }
// This is overridden to prevent someone from calling SaveChanges without specifying the user making the change
public override int SaveChanges()
{
throw new InvalidOperationException("User ID must be provided");
}
public int SaveChanges(int userId)
{
// Get all Added/Deleted/Modified entities (not Unmodified or Detached)
foreach (var ent in this.ChangeTracker.Entries().Where(p => p.State == System.Data.EntityState.Added || p.State == System.Data.EntityState.Deleted || p.State == System.Data.EntityState.Modified))
{
// For each changed record, get the audit record entries and add them
foreach (AuditLog x in GetAuditRecordsForChange(ent, userId))
{
this.AuditLogs.Add(x);
}
}
// Call the original SaveChanges(), which will save both the changes made and the audit records
return base.SaveChanges();
}
private List<AuditLog> GetAuditRecordsForChange(DbEntityEntry dbEntry, int userId)
{
List<AuditLog> result = new List<AuditLog>();
DateTime changeTime = DateTime.UtcNow;
// Get the Table() attribute, if one exists
//TableAttribute tableAttr = dbEntry.Entity.GetType().GetCustomAttributes(typeof(TableAttribute), false).SingleOrDefault() as TableAttribute;
TableAttribute tableAttr = dbEntry.Entity.GetType().GetCustomAttributes(typeof(TableAttribute), true).SingleOrDefault() as TableAttribute;
// Get table name (if it has a Table attribute, use that, otherwise get the pluralized name)
string tableName = tableAttr != null ? tableAttr.Name : dbEntry.Entity.GetType().Name;
// Get primary key value (If you have more than one key column, this will need to be adjusted)
var keyNames = dbEntry.Entity.GetType().GetProperties().Where(p => p.GetCustomAttributes(typeof(KeyAttribute), false).Count() > 0).ToList();
string keyName = keyNames[0].Name; //dbEntry.Entity.GetType().GetProperties().Single(p => p.GetCustomAttributes(typeof(KeyAttribute), false).Count() > 0).Name;
if (dbEntry.State == System.Data.EntityState.Added)
{
// For Inserts, just add the whole record
// If the entity implements IDescribableEntity, use the description from Describe(), otherwise use ToString()
foreach (string propertyName in dbEntry.CurrentValues.PropertyNames)
{
result.Add(new AuditLog()
{
AuditLogId = Guid.NewGuid(),
UserId = userId,
EventDateUTC = changeTime,
EventType = "A", // Added
TableName = tableName,
RecordId = dbEntry.CurrentValues.GetValue<object>(keyName).ToString(),
ColumnName = propertyName,
NewValue = dbEntry.CurrentValues.GetValue<object>(propertyName) == null ? null : dbEntry.CurrentValues.GetValue<object>(propertyName).ToString()
}
);
}
}
else if (dbEntry.State == System.Data.EntityState.Deleted)
{
// Same with deletes, do the whole record, and use either the description from Describe() or ToString()
result.Add(new AuditLog()
{
AuditLogId = Guid.NewGuid(),
UserId = userId,
EventDateUTC = changeTime,
EventType = "D", // Deleted
TableName = tableName,
RecordId = dbEntry.OriginalValues.GetValue<object>(keyName).ToString(),
ColumnName = "*ALL",
NewValue = (dbEntry.OriginalValues.ToObject() is IDescribableEntity) ? (dbEntry.OriginalValues.ToObject() as IDescribableEntity).Describe() : dbEntry.OriginalValues.ToObject().ToString()
}
);
}
else if (dbEntry.State == System.Data.EntityState.Modified)
{
foreach (string propertyName in dbEntry.OriginalValues.PropertyNames)
{
// For updates, we only want to capture the columns that actually changed
if (!object.Equals(dbEntry.OriginalValues.GetValue<object>(propertyName), dbEntry.CurrentValues.GetValue<object>(propertyName)))
{
result.Add(new AuditLog()
{
AuditLogId = Guid.NewGuid(),
UserId = userId,
EventDateUTC = changeTime,
EventType = "M", // Modified
TableName = tableName,
RecordId = dbEntry.OriginalValues.GetValue<object>(keyName).ToString(),
ColumnName = propertyName,
OriginalValue = dbEntry.OriginalValues.GetValue<object>(propertyName) == null ? null : dbEntry.OriginalValues.GetValue<object>(propertyName).ToString(),
NewValue = dbEntry.CurrentValues.GetValue<object>(propertyName) == null ? null : dbEntry.CurrentValues.GetValue<object>(propertyName).ToString()
}
);
}
}
}
// Otherwise, don't do anything, we don't care about Unchanged or Detached entities
return result;
}
}
This will use the following table in your DB:
USE [databasename]
GO
/****** Object: Table [dbo].[auditlog] Script Date: 06/01/2014 05:56:49 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[auditlog](
[auditlogid] [uniqueidentifier] NOT NULL,
[userid] [int] NOT NULL,
[eventdateutc] [datetime] NOT NULL,
[eventtype] [char](1) NOT NULL,
[tablename] [nvarchar](100) NOT NULL,
[recordid] [nvarchar](100) NOT NULL,
[columnname] [nvarchar](100) NOT NULL,
[originalvalue] [nvarchar](max) NULL,
[newvalue] [nvarchar](max) NULL,
CONSTRAINT [PK_AuditLog] PRIMARY KEY NONCLUSTERED
(
[auditlogid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[auditlog] WITH CHECK ADD CONSTRAINT [FK_auditlog_users] FOREIGN KEY([userid])
REFERENCES [dbo].[users] ([userid])
GO
ALTER TABLE [dbo].[auditlog] CHECK CONSTRAINT [FK_auditlog_users]
GO
With this all set then you will just need to call your dbContext.SaveChanges(here the userId);
Hope this will work for you... I use it in all my applications and works great!
Enjoy it.
Full code found here: https://jmdority.wordpress.com/2011/07/20/using-entity-framework-4-1-dbcontext-change-tracking-for-audit-logging/
回答2:
I found this NuGet package (TrackerEnabledDbContext) and followed these 4 steps:
Install package TrackerEnabledDbContext
Inherit my DbContext from TrackerContext in TrackerEnabledDbContext namespace
public class ApplicationDbContext : TrackerContext
{
public ApplicationDbContext()
: base("DefaultConnection")
{
}
Add a migration and update my database. Two new tables were created for recording changes (AuditLog and AuditLogDetails).
Decide which tables you want to track and apply [TrackChanges] attribute to the classes. In case you want to skip tracking for some specific columns, you can apply [SkipTracking] attribute to those columns (properties).
Whenever you make a change in database, you call DbContext.SaveChanges(). Now you have an overload available for that which takes an integer. This should be the logged-in person's user ID. If you don't pass the user ID, this change will not be recorded into the tracking table.
databaseContext.SaveChanges(userId);
And that's all. Later you can retrieve the logs with this:
var AuditLogs = db.GetLogs<Proyecto>(id).ToList();
回答3:
Disclaimer: I'm the owner of the project Entity Framework Plus
EF+ has auditing features which support EF5, EF6, and EF Core.
// using Z.EntityFramework.Plus; // Don't forget to include this.
var ctx = new EntityContext();
// ... ctx changes ...
var audit = new Audit();
audit.CreatedBy = "ZZZ Projects"; // Optional
ctx.SaveChanges(audit);
// Access to all auditing information
var entries = audit.Entries;
foreach(var entry in entries)
{
foreach(var property in entry.Properties)
{
}
}
A lot of options are available like an AutoSave in the database.
Documentation: EF+ Audit
回答4:
In Generic repository pattern, we can write a generic event handler for db context savechanges event.
I googled it and gathered few many information.
- I dont want to write a sql server trigger
- I dont want to handle savechanges method in each entity.
So i am planning to write generic single method
Db structure that I am using
audit table
CREATE TABLE [dbo].[Audit](
[Id] [BIGINT] IDENTITY(1,1) NOT NULL,
[TableName] [nvarchar](250) NULL,
[Updated By] [nvarchar](100) NULL,
[Actions] [nvarchar](25) NULL,
[OldData] [text] NULL,
[NewData] [text] NULL,
[Created For] varchar(200) NULL,
[Updated Date] [datetime] NULL,
CONSTRAINT [PK_DBAudit] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
2.Update your dbcontext with Audit table entity.
3.Hook generic event handler for Dbcontext savechanges
c# code
namespace ARMS.Domain
{
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.Objects;
using System.Linq;
using System.Text;
using System.ComponentModel.DataAnnotations;
public partial class ARMSContext
{
Collection<Audit> auditTrailList = new Collection<Audit>();
partial void OnContextCreated()
{
this.SavingChanges += new EventHandler(ArmsEntities_SavingChanges);
}
public enum AuditActions
{
Added,
Modified,
Deleted
}
void ArmsEntities_SavingChanges(object sender, EventArgs e)
{
auditTrailList.Clear();
IEnumerable<ObjectStateEntry> changes =
this.ObjectStateManager.GetObjectStateEntries(
EntityState.Added | EntityState.Deleted | EntityState.Modified);
foreach (ObjectStateEntry stateEntryEntity in changes)
{
if (!stateEntryEntity.IsRelationship && stateEntryEntity.Entity != null && !(stateEntryEntity.Entity is Audit))
{
Audit audit = this.GetAudit(stateEntryEntity);
auditTrailList.Add(audit);
}
}
if (auditTrailList.Count > 0)
{
foreach (var audit in auditTrailList)
{
this.Audits.AddObject(audit);
}
}
}
public Audit GetAudit(ObjectStateEntry entry)
{
Audit audit = new Audit();
audit.Updated_By ="Test";
audit.TableName = entry.EntitySet.ToString();
audit.Updated_Date = DateTime.Now;
audit.Created_For = Convert.ToString(entry.Entity);
audit.Actions = Enum.Parse(typeof(AuditActions),entry.State.ToString(), true).ToString();
StringBuilder newValues = new StringBuilder();
StringBuilder oldValues = new StringBuilder();
if (entry.State == EntityState.Added)
{
SetAddedProperties(entry, newValues);
audit.NewData = newValues.ToString();
}
else if (entry.State == EntityState.Deleted)
{ SetDeletedProperties(entry, oldValues);
audit.OldData = oldValues.ToString();
}
else if (entry.State == EntityState.Modified)
{
SetModifiedProperties(entry, oldValues, newValues);
audit.OldData = oldValues.ToString();
audit.NewData = newValues.ToString();
}
return audit;
}
private void SetAddedProperties(ObjectStateEntry entry, StringBuilder newData)
{
CurrentValueRecord currentValues = entry.CurrentValues;
for (int i = 0; i < currentValues.FieldCount; i++)
{
newData.AppendFormat("{0}={1} || ", currentValues.GetName(i), currentValues.GetValue(i));
}
}
private void SetDeletedProperties(ObjectStateEntry entry, StringBuilder oldData)
{
foreach (var propertyName in entry.GetModifiedProperties())
{
var oldVal = entry.OriginalValues[propertyName];
if (oldVal != null)
{
oldData.AppendFormat("{0}={1} || ", propertyName, oldVal);
}
}
}
private void SetModifiedProperties(ObjectStateEntry entry, StringBuilder oldData, StringBuilder newData)
{
foreach (var propertyName in entry.GetModifiedProperties())
{
var oldVal = entry.OriginalValues[propertyName];
var newVal = entry.CurrentValues[propertyName];
if (oldVal != null && newVal != null && !Equals(oldVal, newVal))
{
newData.AppendFormat("{0}={1} || ", propertyName, newVal);
oldData.AppendFormat("{0}={1} || ", propertyName, oldVal);
}
}
}
}
}
回答5:
Create a class for capture the changes or track the changes when entity added, modifies or deleted.
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Text;
using System.Web;
namespace MVC_AuditTrail.Models
{
public class AuditTrailFactory
{
private DbContext context;
public AuditTrailFactory(DbContext context)
{
this.context = context;
}
public Audit GetAudit(DbEntityEntry entry)
{
Audit audit = new Audit();
// var user = (User)HttpContext.Current.Session[":user"];
audit.UserId = "swapnil";// user.UserName;
audit.TableName = GetTableName(entry);
audit.UpdateDate = DateTime.Now;
audit.TableIdValue = GetKeyValue(entry);
//entry is Added
if (entry.State == EntityState.Added)
{
var newValues = new StringBuilder();
SetAddedProperties(entry, newValues);
audit.NewData = newValues.ToString();
audit.Actions = AuditActions.I.ToString();
}
//entry in deleted
else if (entry.State == EntityState.Deleted)
{
var oldValues = new StringBuilder();
SetDeletedProperties(entry, oldValues);
audit.OldData = oldValues.ToString();
audit.Actions = AuditActions.D.ToString();
}
//entry is modified
else if (entry.State == EntityState.Modified)
{
var oldValues = new StringBuilder();
var newValues = new StringBuilder();
SetModifiedProperties(entry, oldValues, newValues);
audit.OldData = oldValues.ToString();
audit.NewData = newValues.ToString();
audit.Actions = AuditActions.U.ToString();
}
return audit;
}
private void SetAddedProperties(DbEntityEntry entry, StringBuilder newData)
{
foreach (var propertyName in entry.CurrentValues.PropertyNames)
{
var newVal = entry.CurrentValues[propertyName];
if (newVal != null)
{
newData.AppendFormat("{0}={1} || ", propertyName, newVal);
}
}
if (newData.Length > 0)
newData = newData.Remove(newData.Length - 3, 3);
}
private void SetDeletedProperties(DbEntityEntry entry, StringBuilder oldData)
{
DbPropertyValues dbValues = entry.GetDatabaseValues();
foreach (var propertyName in dbValues.PropertyNames)
{
var oldVal = dbValues[propertyName];
if (oldVal != null)
{
oldData.AppendFormat("{0}={1} || ", propertyName, oldVal);
}
}
if (oldData.Length > 0)
oldData = oldData.Remove(oldData.Length - 3, 3);
}
private void SetModifiedProperties(DbEntityEntry entry, StringBuilder oldData, StringBuilder newData)
{
DbPropertyValues dbValues = entry.GetDatabaseValues();
foreach (var propertyName in entry.OriginalValues.PropertyNames)
{
var oldVal = dbValues[propertyName];
var newVal = entry.CurrentValues[propertyName];
if (oldVal != null && newVal != null && !Equals(oldVal, newVal))
{
newData.AppendFormat("{0}={1} || ", propertyName, newVal);
oldData.AppendFormat("{0}={1} || ", propertyName, oldVal);
}
}
if (oldData.Length > 0)
oldData = oldData.Remove(oldData.Length - 3, 3);
if (newData.Length > 0)
newData = newData.Remove(newData.Length - 3, 3);
}
public long? GetKeyValue(DbEntityEntry entry)
{
var objectStateEntry = ((IObjectContextAdapter)context).ObjectContext.ObjectStateManager.GetObjectStateEntry(entry.Entity);
long id = 0;
if (objectStateEntry.EntityKey.EntityKeyValues != null)
id = Convert.ToInt64(objectStateEntry.EntityKey.EntityKeyValues[0].Value);
return id;
}
private string GetTableName(DbEntityEntry dbEntry)
{
TableAttribute tableAttr = dbEntry.Entity.GetType().GetCustomAttributes(typeof(TableAttribute), false).SingleOrDefault() as TableAttribute;
string tableName = tableAttr != null ? tableAttr.Name : dbEntry.Entity.GetType().Name;
return tableName;
}
}
public enum AuditActions
{
I,
U,
D
}
}
Then create audit table entity and context class.
And Override savechanges method in this method get audit changes and save before base entity saved.
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Web;
namespace MVC_AuditTrail.Models
{
public class Student
{
public int StudentID { get; set; }
public string Name { get; set; }
public string mobile { get; set; }
}
public class Audit
{
public long Id { get; set; }
public string TableName { get; set; }
public string UserId { get; set; }
public string Actions { get; set; }
public string OldData { get; set; }
public string NewData { get; set; }
public Nullable<long> TableIdValue { get; set; }
public Nullable<System.DateTime> UpdateDate { get; set; }
}
public class StdContext : DbContext
{
private AuditTrailFactory auditFactory;
private List<Audit> auditList = new List<Audit>();
private List<DbEntityEntry> objectList = new List<DbEntityEntry>();
public StdContext() : base("stdConnection")
{
Database.SetInitializer<StdContext>(new CreateDatabaseIfNotExists<StdContext>());
}
public DbSet<Student> Student { get; set; }
public DbSet<Audit> Audit { get; set; }
public override int SaveChanges()
{
auditList.Clear();
objectList.Clear();
auditFactory = new AuditTrailFactory(this);
var entityList = ChangeTracker.Entries().Where(p => p.State == EntityState.Added || p.State == EntityState.Deleted || p.State == EntityState.Modified);
foreach (var entity in entityList)
{
Audit audit = auditFactory.GetAudit(entity);
bool isValid = true;
if (entity.State == EntityState.Modified && string.IsNullOrWhiteSpace(audit.NewData) && string.IsNullOrWhiteSpace(audit.OldData))
{
isValid = false;
}
if (isValid)
{
auditList.Add(audit);
objectList.Add(entity);
}
}
var retVal = base.SaveChanges();
if (auditList.Count > 0)
{
int i = 0;
foreach (var audit in auditList)
{
if (audit.Actions == AuditActions.I.ToString())
audit.TableIdValue = auditFactory.GetKeyValue(objectList[i]);
this.Audit.Add(audit);
i++;
}
base.SaveChanges();
}
return retVal;
}
}
}
![Prime Path[POJ3126] [SPFA/BFS]](https://oscimg.oschina.net/oscnet/e1200f32e838bf1d387d671dc8e6894c37d.jpg "Prime Path[POJ3126] [SPFA/BFS]")
{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://img.manongdao.com/sparkler-677774__340.jpg', '推荐 神经病院院长 的文章《how to create an audit trail with Entity framework》','https://www.manongdao.com/article-193077.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}