Use the following script to create a table in SQL Server:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NullableTypeBug](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[ContractId] [int] NOT NULL,
[SubContractId] [int] NULL,
CONSTRAINT [PK_NullableTypeBug] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY],
CONSTRAINT [IX_NullableTypeBug_1] UNIQUE NONCLUSTERED
(
[ContractId] ASC,
[SubContractId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
Use NetTiers to generate an entity and a service layer.
You will then get a NullableTypeBugService class with a
GetByContractIdSubContractId(int contractId, int? subContractId) method. Now,
let's say you call this method with 1000 as ContractId and with Null as
subContractId, the SQL generated for the Where Clause will be:
WHERE
[ContractId] = @ContractId
AND [SubContractId] = @SubContractId
and further down the SQL, the parameters will be defines as such:
',N'@ContractId int,@SubContractId int',@ContractId=1000,@SubContractId=NULL
However, the where clause is erroneous in the case where @SubContractId is
null. "[SubContractId] = @SubContractId" would have to be replaced by
"[SubContractId] IS NULL".
One solution is to replace SubContractId with a zero, an empty space, or any
other appropriate value in the Where Clause:
WHERE
[ContractId] = @ContractId
AND ISNULL([SubContractId], 0) = ISNULL(@SubContractId, 0)
This may not be a solution in all cases however. There has to be a value by
which a Null can be replaced.
I found this bug in .NetTiers 2.3.0 and I am using CodeSmith Standard 4.1.4
Original issue reported on code.google.com by viave...@gmail.com on 9 Aug 2011 at 8:05
Original issue reported on code.google.com by
viave...@gmail.com
on 9 Aug 2011 at 8:05