问题描述
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]