我有以下的code更新帐户表的LINQ to SQL。账户号码是主键列。这需要更新的唯一价值是AccountType;然而,持续时间也将被更新,以零(对于int默认值)。我们怎样才能避免这种不必要的覆盖?
注:我使用的连接方式
注:我理解这种现象的原因。 在DataContext不能一个场之间具有指定值零和一个仅是未分配的区别。。我要寻找一个解决方案来克服这一点。
[全球:: System.Data.Linq.Mapping.ColumnAttribute(存储=_时间的DbType =诠释NOT NULL
公众诠释时间
表中的数据。
表结构:
CREATE TABLE [DBO]。[帐户](
[账户号码] [INT] NOT NULL,
[AccountType] [NCHAR](10)NOT NULL,
[时间] [INT] NOT NULL,
[DepositedAmount] [INT] NULL,
约束[PK_Account] PRIMARY KEY CLUSTERED
(
[账户号码] ASC
)WITH(PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)ON [PRIMARY]
)ON [PRIMARY]
code
公共无效UpdateAccount()
{
RepositoryLayer.Account ACC1 =新RepositoryLayer.Account();
acc1.AccountNumber = 4;
acc1.AccountType =验证;
accountRepository.UpdateChangesByAttachAndRefresh(ACC1);
accountRepository.SubmitChanges();
}
公共虚拟无效UpdateChangesByAttachAndRefresh(T实体)
{
//可以GetOriginalEntityState造成任何错误?它是不必要的?
如果(GetTable()。GetOriginalEntityState(实体)== NULL)
{
//如果它尚未附
Context.GetTable< T>()附加(实体)。
Context.Refresh(System.Data.Linq.RefreshMode.KeepCurrentValues,实体);
}
}
生成的SQL
更新[DBO]。[帐户]
SET [AccountType] = @ P3,[时间] = @ P4
WHERE([账户号码] = @ P0)
与([AccountType] = @ p1)为
AND([时间] = @ P2)
与(DepositedAmount] IS NULL)
- @ P0:输入INT(尺寸= -1; preC = 0;规模= 0)[4]
- @ P1:输入NCHAR(大小= 10; preC = 0;规模= 0)[测试]
- @ P2:输入INT(尺寸= -1; preC = 0;规模= 0)[10]
- @ P3:输入NCHAR(大小= 10; preC = 0;规模= 0)[确认]
- @ P4:输入int(尺寸= -1; $ P $件= 0;量表= 0)[0]
- 背景:sqlProvider的(SQL2008)型号:AttributedMetaModel体形:4.0.30319.1
阅读:
Can LINQ到SQL省略上插入未指定的列,以便数据库使用默认值?
How我可以绑定一个枚举到位的的DbType或INT?
Linq到SQL:为什么我会得到IDENTITY_INSERT错误
?LINQ到SQL:更新无刷新的时候UpdateCheck的=从不
解决方案我通过修改更新的方式解决了这个问题,通过执行LINQ到SQL:更新无刷新的时候UpdateCheck的=从不
UpdateCheck的设置为从不为持续时间列
公共无效UpdateAccount()
{
//从previous使用的值选择
日期时间previousDateTime =新的日期时间(2012年,6,26,11,14,15,327);
INT prevDuration = 0;
RepositoryLayer.Account accEntity =新RepositoryLayer.Account();
accEntity.AccountNumber = 1; //首要的关键
accEntity.ModifiedTime = previousDateTime; //并发列
//accEntity.Duration = prevDuration;
accountRepository.UpdateChangesByAttach(accEntity);
//值后附加修改
accEntity.AccountType =双赢
accEntity.ModifiedTime = DateTime.Now;
尝试
{
accountRepository.SubmitChanges();
}
赶上(System.Data.Linq.ChangeConflictException E)
{
抛出新的异常(e.Message);
}
}
公共虚拟无效UpdateChangesByAttach(T实体)
{
如果(Context.GetTable< T>()GetOriginalEntityState(实体)== NULL)
{
//如果它尚未附
Context.GetTable< T>()附加(实体)。
}
}
生成的SQL
更新[DBO]。[帐户]
SET [AccountType] = @ P2,[ModifiedTime] = @ P3
WHERE([账户号码] = @ P0)
与([ModifiedTime] = @ p1)为
- @ P0:输入INT(尺寸= -1; preC = 0;规模= 0)[1]
- @ P1:输入的日期时间(尺寸= -1; preC = 0;规模= 0)[2012年6月26日上午11时十四分15秒]
- @ P2:输入NCHAR(大小= 10; preC = 0;规模= 0)[WIN-WIN]
- @ P3:输入的日期时间(尺寸= -1; preC = 0;规模= 0)[2012年6月26日上午11点16分29秒]
I have the following code for updating Account table with LINQ to SQL. AccountNumber is the primary key column. The only value which need to be updated is AccountType; however the Duration also gets updated with zero (default value for int). How can we avoid this unnecessary overwrite?
Note: I am using Attach method
Note: I understand the reason for this behavior. "The DataContext cannot distinguish between a field with an assigned value of zero and one that is merely unassigned.". I am looking for a solution to overcome this.
[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Duration", DbType="Int NOT NULL"
public int Duration
Table DATA
Table Structure:
CREATE TABLE [dbo].[Account](
[AccountNumber] [int] NOT NULL,
[AccountType] [nchar](10) NOT NULL,
[Duration] [int] NOT NULL,
[DepositedAmount] [int] NULL,
CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED
(
[AccountNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CODE
public void UpdateAccount()
{
RepositoryLayer.Account acc1 = new RepositoryLayer.Account();
acc1.AccountNumber = 4;
acc1.AccountType = "Verify";
accountRepository.UpdateChangesByAttachAndRefresh(acc1);
accountRepository.SubmitChanges();
}
public virtual void UpdateChangesByAttachAndRefresh(T entity)
{
//Can GetOriginalEntityState cause any bug? Is it unnecessary?
if (GetTable().GetOriginalEntityState(entity) == null)
{
//If it is not already attached
Context.GetTable<T>().Attach(entity);
Context.Refresh(System.Data.Linq.RefreshMode.KeepCurrentValues, entity);
}
}
Generated SQL
UPDATE [dbo].[Account]
SET [AccountType] = @p3, [Duration] = @p4
WHERE ([AccountNumber] = @p0)
AND ([AccountType] = @p1)
AND ([Duration] = @p2)
AND ([DepositedAmount] IS NULL)
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [4]
-- @p1: Input NChar (Size = 10; Prec = 0; Scale = 0) [TEST ]
-- @p2: Input Int (Size = -1; Prec = 0; Scale = 0) [10]
-- @p3: Input NChar (Size = 10; Prec = 0; Scale = 0) [Verify]
-- @p4: Input Int (Size = -1; Prec = 0; Scale = 0) [0]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
READING:
Can LINQ-to-SQL omit unspecified columns on insert so a database default value is used?
How can I bind an Enum to a DbType of bit or int?
Linq to SQL: Why am I getting IDENTITY_INSERT errors?
LINQ to SQL: Updating without Refresh when "UpdateCheck = Never"
解决方案
I solved this issue by changing the update approach by following the answer in LINQ to SQL: Updating without Refresh when "UpdateCheck = Never"
UpdateCheck is set as Never for the Duration column
public void UpdateAccount()
{
//Used value from previous select
DateTime previousDateTime = new DateTime(2012, 6, 26, 11, 14, 15, 327);
int prevDuration = 0;
RepositoryLayer.Account accEntity = new RepositoryLayer.Account();
accEntity.AccountNumber = 1; //Primary Key
accEntity.ModifiedTime = previousDateTime; //Concurrency column
//accEntity.Duration = prevDuration;
accountRepository.UpdateChangesByAttach(accEntity);
//Values to be modified after Attach
accEntity.AccountType = "WIN-WIN";
accEntity.ModifiedTime = DateTime.Now;
try
{
accountRepository.SubmitChanges();
}
catch(System.Data.Linq.ChangeConflictException e)
{
throw new Exception(e.Message);
}
}
public virtual void UpdateChangesByAttach(T entity)
{
if (Context.GetTable<T>().GetOriginalEntityState(entity) == null)
{
//If it is not already attached
Context.GetTable<T>().Attach(entity);
}
}
Generated SQL
UPDATE [dbo].[Account]
SET [AccountType] = @p2, [ModifiedTime] = @p3
WHERE ([AccountNumber] = @p0)
AND ([ModifiedTime] = @p1)
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [1]
-- @p1: Input DateTime (Size = -1; Prec = 0; Scale = 0) [6/26/2012 11:14:15 AM]
-- @p2: Input NChar (Size = 10; Prec = 0; Scale = 0) [WIN-WIN]
-- @p3: Input DateTime (Size = -1; Prec = 0; Scale = 0) [6/26/2012 11:16:29 AM]
相关推荐
最新文章