CODEWITHSUNDEEP
c#.netentity-framework-6
Mick
Mick

Reputation: 6864

Linq Entity Framework Ignoring IS NOT NULL

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);
    }
}

Upvotes: 3

Views: 1038

Answers (1)

Mick
Mick

Reputation: 6864

OK it's not really a solution but the issue is caused by addition of a Revisions navigation property. Removing this statement resolves the issue.

modelBuilder.Entity<ChecklistQuestionEntity>()
        .HasRequired(cq => cq.OriginalQuestion)
        .WithMany(cq => cq.Revisions)
        .HasForeignKey(cq => cq.OriginalQuestionID)
        .WillCascadeOnDelete(false);

I believe this is a bug and it's related to the issue discussed here...

Entity Framework Linq equals value or is null

Whilst OriginalQuestionID is not part of a key on the table relating it to the primary key of the table seems to cause EF to stop believing it can be null.

I tried upgrading to EF 6.2, the issue remained.

The only work around I have is to remove the Revisions navigation property.

Upvotes: 1

Related Questions