问题描述
在附件链接中看到一个问题后，我了解了如何在Entity Framework中设置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.
BTW即使没关系，我正在建设这个应用程序使用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.
对于我的解决方案（ASP.NET WebForms和C＃中的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).
更新：这是我对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类中： / p>
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.
这篇关于DateCreated或修改列 - 实体框架或在SQL Server上使用触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！