DateCreated 或 Modified 列 - 实体框架或在 SQL Server 上使用触发器
阅读附加链接中的一个问题后，我了解了如何在实体框架中设置 DateCreated 和 DateModified 列并在我的应用程序中使用它.虽然在旧的 SQL 方式中，触发器方式更受欢迎，因为从 DBA 的角度来看更安全.
After I read one question in attached link, I got a sense of how to set DateCreated and DateModified columns in Entity Framework and use it in my application. In the old SQL way though, the trigger way is more popular because is more secure from DBA point of view.
那么关于哪种方式是最佳实践的任何建议?为了应用程序完整性，它应该在实体框架中设置吗?还是应该使用触发器，因为从数据安全的角度来看它更有意义?或者有没有办法在实体框架中组合触发器?谢谢.
So any advice on which way is the best practice? should it be set in entity framework for the purpose of application integrity? or should use trigger as it make more sense from data security point of view? Or is there a way to compose trigger in entity framework? Thanks.
EF CodeFirst:Rails-样式创建和修改的列
顺便说一句，尽管这并不重要，但我正在使用 ASP.NET MVC C# 构建这个应用程序.
BTW, even though it doesn't matter much, I am building this app using ASP.NET MVC C#.
推荐答案
意见:触发器就像隐藏的行为，除非你去寻找它们，否则你通常不会意识到它们的存在.我也喜欢在使用 EF 时尽可能保持数据库哑"，因为我使用的是 EF，所以我的团队不需要维护 SQL 代码.
Opinion: Triggers are like hidden behaviour, unless you go looking for them you usually won't realise they are there. I also like to keep the DB as 'dumb' as possible when using EF, since I'm using EF so my team wont need to maintain SQL code.
对于我的解决方案(C# 中的 ASP.NET WebForms 和 MVC 与另一个包含 DataContext 的项目中的业务逻辑的混合):
我最近遇到了类似的问题，尽管对于我的情况来说它更复杂(DatabaseFirst，因此需要自定义 TT 文件)，但解决方案大体相同.
I recently had a similar issue, and although for my situation it was more complex (DatabaseFirst, so required a custom TT file), the solution is mostly the same.
我创建了一个界面:
public interface ITrackableEntity
{
DateTime CreatedDateTime { get; set; }
int CreatedUserID { get; set; }
DateTime ModifiedDateTime { get; set; }
int ModifiedUserID { get; set; }
}
然后我只是在我需要的任何实体上实现了该接口(因为我的解决方案是 DatabaseFirst，我更新了 TT 文件以检查表是否有这四列，如果有，则将接口添加到输出中).
Then I just implemented that interface on any entities I needed to (because my solution was DatabaseFirst, I updated the TT file to check if the table had those four columns, and if so added the interface to the output).
UPDATE:这是我对 TT 文件的更改，我在其中更新了 EntityClassOpening() 方法:
UPDATE: here's my changes to the TT file, where I updated the EntityClassOpening() method:
public string EntityClassOpening(EntityType entity)
{
var trackableEntityPropNames = new string[] { "CreatedUserID", "CreatedDateTime", "ModifiedUserID", "ModifiedDateTime" };
var propNames = entity.Properties.Select(p => p.Name);
var isTrackable = trackableEntityPropNames.All(s => propNames.Contains(s));
var inherits = new List<string>();
if (!String.IsNullOrEmpty(_typeMapper.GetTypeName(entity.BaseType)))
{
inherits.Add(_typeMapper.GetTypeName(entity.BaseType));
}
if (isTrackable)
{
inherits.Add("ITrackableEntity");
}
return string.Format(
CultureInfo.InvariantCulture,
"{0} {1}partial class {2}{3}",
Accessibility.ForType(entity),
_code.SpaceAfter(_code.AbstractOption(entity)),
_code.Escape(entity),
_code.StringBefore(" : ", String.Join(", ", inherits)));
}
唯一剩下的就是将以下内容添加到我的部分 DataContext 类中:
The only thing left was to add the following to my partial DataContext class:
public override int SaveChanges()
{
// fix trackable entities
var trackables = ChangeTracker.Entries<ITrackableEntity>();
if (trackables != null)
{
// added
foreach (var item in trackables.Where(t => t.State == EntityState.Added))
{
item.Entity.CreatedDateTime = System.DateTime.Now;
item.Entity.CreatedUserID = _userID;
item.Entity.ModifiedDateTime = System.DateTime.Now;
item.Entity.ModifiedUserID = _userID;
}
// modified
foreach (var item in trackables.Where(t => t.State == EntityState.Modified))
{
item.Entity.ModifiedDateTime = System.DateTime.Now;
item.Entity.ModifiedUserID = _userID;
}
}
return base.SaveChanges();
}
请注意，每次创建时，我都会将当前用户 ID 保存在 DataContext 类的私有字段中.
Note that I saved the current user ID in a private field on the DataContext class each time I created it.
相关文章