I have a table with a nullable column when I execute code as follows ...
IList<IChecklistQuestionEntity> questions = repository.Where(q => q.OriginalQuestionID.HasValue);
IList<IChecklistQuestionEntity> originalQuestions = repository.Where(q => !q.OriginalQuestionID.HasValue);
...what I see in profiler is ...
SELECT
[Extent1].[ID] AS [ID],
[Extent1].[OriginalQuestionID] AS [OriginalQuestionID],
[Extent1].[Question] AS [Question],
FROM [dbo].[cklChecklistQuestion] AS [Extent1]
ORDER BY [Extent1].[Question] ASC
and
SELECT
CAST(NULL AS int) AS [C1],
CAST(NULL AS int) AS [C2],
CAST(NULL AS varchar(1)) AS [C3],
FROM ( SELECT 1 AS X ) AS [SingleRowTable1]
WHERE 1 = 0
The EF class is implemented as follows...
[Table("cklChecklistQuestion")]
public class ChecklistQuestionEntity
{
/// <summary>
/// Gets or sets the ID
/// </summary>
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int ID
{
get;
set;
}
/// <summary>
/// Gets or sets the OriginalQuestionID
/// </summary>
public int? OriginalQuestionID
{
get;
set;
}
[Required]
public string Question
{
get;
set;
}
public ICollection<ChecklistQuestionEntity> Revisions { get; set; }
}
Naturally I was hoping to see a Where IS NOT NULL clause on the first query and the 2nd a select on the table with where IS NULL.
I'm using EF 6.1.3 from my searches it appears this issue has been around but should be fixed. Would EF 6.2 give me more joy on this issue?
EDIT: I've tried with and without the virtual key word on the OriginalQuestionID property
EDIT2: I've also tried without repositories and just the EF context
IList<ChecklistQuestionEntity> questions = context.Set<ChecklistQuestionEntity>().Where(q => q.OriginalQuestionID.HasValue).ToList();
IList<ChecklistQuestionEntity> originalQuestions = context.Set<ChecklistQuestionEntity>().Where(q => !q.OriginalQuestionID.HasValue).ToList();
And received the same result.
The create for the table looks like...
CREATE TABLE [dbo].[cklChecklistQuestion](
[ID] [int] IDENTITY(1,1) NOT NULL,
[OriginalQuestionID] [int] NULL,
[Question] [nvarchar](450) NOT NULL,
CONSTRAINT [PK_cklChecklistQuestion] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[cklChecklistQuestion] WITH CHECK ADD CONSTRAINT [FK_cklChecklistQuestion_cklChecklistQuestion] FOREIGN KEY([OriginalQuestionID])
REFERENCES [dbo].[cklChecklistQuestion] ([ID])
GO
ALTER TABLE [dbo].[cklChecklistQuestion] CHECK CONSTRAINT [FK_cklChecklistQuestion_cklChecklistQuestion]
GO
EDIT3: I think this post suggests it should be fixed
How can i query for null values in entity framework?
EDIT4: Perhaps it relates to this issue... Entity Framework Linq equals value or is null
Although it is different as whilst OriginalQuestionID is not part of a primary key it is a foreign key relationship to the primary key on the same table
EDIT5: Apologies all... When I attempted to replicate this issue in a smaller application I found I had left out a crucial part. I've amended the code above to include a Revisions property. Plus you need the following Context.
public class MyContext : DbContext
{
public MyContext(string nameOrConnectionString) : base(nameOrConnectionString)
{
}
protected MyContext()
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
// Commenting out the following resolves the issue
modelBuilder.Entity<ChecklistQuestionEntity>()
.HasRequired(cq => cq.OriginalQuestion)
.WithMany(cq => cq.Revisions)
.HasForeignKey(cq => cq.OriginalQuestionID)
.WillCascadeOnDelete(false);
}
}
