优化版本库的的SubmitChanges方法 [英] Optimizing Repository’s SubmitChanges Method
问题描述
我有以下资料库。我有LINQ 2 SQL生成的类和使用工厂域对象之间的映射。
下面code将工作;但我看到两个潜在的问题
1），这是使用SELECT查询更新语句之前。
2）需要更新所有的列（不仅是改变了列）。这是因为我们不知道所有列在域对象得到了改变。
如何克服这些缺点？
注：可以有场景（如触发器），它被基于特定列的更新执行。因此，我不能不必要地更新一列。
参考
-
<一个href="http://stackoverflow.com/questions/11189688/linq-to-sql-updating-without-refresh-when-updatecheck-never">LINQ到SQL：更新无刷新的时候UpdateCheck的=从不
code
命名空间RepositoryLayer
{
公共接口ILijosBankRepository
{
无效SubmitChangesForEntity（）;
}
公共类LijosSimpleBankRepository：ILijosBankRepository
{
私人IBankAccountFactory bankFactory =新MySimpleBankAccountFactory（）;
公共System.Data.Linq.DataContext上下文
{
得到;
组;
}
公共虚拟无效SubmitChangesForEntity（DomainEntitiesForBank.IBankAccount iBankAcc）
{
//不会从自动变更帮助跟踪（由于映射）
//选择所需的实体
DBML_Project.BankAccount tableEntity = Context.GetTable＆LT; DBML_Project.BankAccount＆GT;（）的SingleOrDefault（P =＆GT; p.BankAccountID == iBankAcc.BankAccountID）;
如果（tableEntity！= NULL）
{
//所有值设置为最新消息（除主键）
tableEntity.Status = iBankAcc.AccountStatus;
//类型检查
如果（iBankAcc是DomainEntitiesForBank.FixedBankAccount）
{
tableEntity.AccountType =固定;
}
如果（iBankAcc是DomainEntitiesForBank.SavingsBankAccount）
{
tableEntity.AccountType =储蓄;
}
Context.SubmitChanges（）;
}
}
}
}
命名空间DomainEntitiesForBank
{
公共接口IBankAccount
{
INT BankAccountID {获得;组; }
双平衡{获得;组; }
字符串AccountStatus {获得;组; }
无效FreezeAccount（）;
}
公共类FixedBankAccount：IBankAccount
{
公众诠释BankAccountID {获得;组; }
公共字符串AccountStatus {获得;组; }
公共双平衡{获得;组; }
公共无效FreezeAccount（）
{
AccountStatus =冻结;
}
}
}
如果我理解你的问题，你被通过了，你需要保存到数据库不知道原来的值是什么一个实体，或列实际上已经改变了。
如果是这样的话，那么你有四个选项
-
您需要返回到数据库，查看原始值，即执行的选择，因为你code在做什么。这使您可以设置所有的实体价值和LINQ2SQL会照顾其列实际上改变。所以，如果你没有列的实际变化，则没有更新语句被触发。
-
您需要避免的选择，只是更新列。你已经知道该怎么做（但别人看<一href="http://stackoverflow.com/questions/11189688/linq-to-sql-updating-without-refresh-when-updatecheck-never#comment14805870_11189688">this问答）。因为你不知道哪些列发生了变化，你没有选择，只能将它们全部。这将产生，即使没有列实际上改变的更新语句，这可能引发的任何数据库的触发器。除了禁用触发器，你可以在这里做的唯一的事情就是确保触发器被写入检查旧的和新的列值，以避免任何进一步的不必要的更新。
-
您需要，这样你需要旧的和新的实体值改变您的要求/程序，这样你就可以判断哪些列发生了变化，而不必返回到数据库。
-
不要使用LINQ为您更新。 LINQ代表语言集成的 QUERY ，它是（恕我直言）辉煌的查询，但我总是枕着更新/删除功能作为一个额外的奖金，但不是它被设计为。另外，如果定时/性能是至关重要的，那么有没有办法，LINQ将匹配正确的手工制作的SQL。
I have following repository. I have a mapping between LINQ 2 SQL generated classes and domain objects using a factory.
The following code will work; but I am seeing two potential issues
1) It is using a SELECT query before update statement.
2) It need to update all the columns (not only the changed column). This is because we don’t know what all columns got changed in the domain object.
How to overcome these shortcomings?
Note: There can be scenarios (like triggers) which gets executed based on specific column update. So I cannot update a column unnecessarily.
REFERENCE:
CODE
namespace RepositoryLayer
{
public interface ILijosBankRepository
{
void SubmitChangesForEntity();
}
public class LijosSimpleBankRepository : ILijosBankRepository
{
private IBankAccountFactory bankFactory = new MySimpleBankAccountFactory();
public System.Data.Linq.DataContext Context
{
get;
set;
}
public virtual void SubmitChangesForEntity(DomainEntitiesForBank.IBankAccount iBankAcc)
{
//Does not get help from automated change tracking (due to mapping)
//Selecting the required entity
DBML_Project.BankAccount tableEntity = Context.GetTable<DBML_Project.BankAccount>().SingleOrDefault(p => p.BankAccountID == iBankAcc.BankAccountID);
if (tableEntity != null)
{
//Setting all the values to updates (except primary key)
tableEntity.Status = iBankAcc.AccountStatus;
//Type Checking
if (iBankAcc is DomainEntitiesForBank.FixedBankAccount)
{
tableEntity.AccountType = "Fixed";
}
if (iBankAcc is DomainEntitiesForBank.SavingsBankAccount)
{
tableEntity.AccountType = "Savings";
}
Context.SubmitChanges();
}
}
}
}
namespace DomainEntitiesForBank
{
public interface IBankAccount
{
int BankAccountID { get; set; }
double Balance { get; set; }
string AccountStatus { get; set; }
void FreezeAccount();
}
public class FixedBankAccount : IBankAccount
{
public int BankAccountID { get; set; }
public string AccountStatus { get; set; }
public double Balance { get; set; }
public void FreezeAccount()
{
AccountStatus = "Frozen";
}
}
}
If I understand your question, you are being passed an entity that you need to save to the database without knowing what the original values were, or which of the columns have actually changed.
If that is the case, then you have four options
You need to go back to the database to see the original values ie perform the select, as you code is doing. This allows you to set all your entity values and Linq2Sql will take care of which columns are actually changed. So if none of your columns are actually changed, then no update statement is triggered.
You need to avoid the select and just update the columns. You already know how to do (but for others see this question and answer). Since you don't know which columns have changed you have no option but set them all. This will produce an update statement even if no columns are actually changed and this can trigger any database triggers. Apart from disabling the triggers, about the only thing you can do here is make sure that the triggers are written to check the old and new columns values to avoid any further unnecessary updates.
You need to change your requirements/program so that you require both old and new entities values, so you can determine which columns have changed without going back to the database.
Don't use LINQ for your updates. LINQ stands for Language Integrated QUERY and it is (IMHO) brilliant at query, but I always looked on the updating/deleting features as an extra bonus, but not something which it was designed for. Also, if timing/performance is critical, then there is no way that LINQ will match properly hand-crafted SQL.
这篇关于优化版本库的的SubmitChanges方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
