IncludeDeleted() not working for Join expressions

Hi there,

I've noticed that the results returned from the following two linq expressions in which I've changed the order of the join/from expressions give different results. The difference is that the first query does not contain any deleted questions even though IncludeDeleted() is used:

var query1 = (
    from bp in BoardParticipants.IncludeDeleted()
    join section in Sections.IncludeDeleted() on bp.BulletinBoardId equals section.BoardId
    join question in Questions.IncludeDeleted() on section.Id equals question.SectionId
    where bp.Hashkey == "fbba7d4f"
    select question
    ).ToList();

var query2 = (
    from question in Questions.IncludeDeleted()
    join section in Sections.IncludeDeleted() on question.SectionId equals section.Id
    join bp in BoardParticipants.IncludeDeleted() on section.BoardId equals bp.BulletinBoardId
    where bp.Hashkey == "fbba7d4f"
    select question
    ).ToList();

Using Linqpad and the lightspeed driver, the generated SQL is: (query1)

SELECT
      t0.Id AS [t0.Id],
      t0.BulletinBoardId AS [t0.BulletinBoardId],
      t0.CreatedOn AS [t0.CreatedOn],
      t0.DeletedOn AS [t0.DeletedOn],
      t0.Hashkey AS [t0.Hashkey],
      t0.LastLogin AS [t0.LastLogin],
      t0.MessageCount AS [t0.MessageCount],
      t0.ParticipantId AS [t0.ParticipantId],
      t0.RoleId AS [t0.RoleId],
      t0.UpdatedOn AS [t0.UpdatedOn],
      t1.Id AS [t1.Id],
      t1.BoardId AS [t1.BoardId],
      t1.CreatedOn AS [t1.CreatedOn],
      t1.DeletedOn AS [t1.DeletedOn],
      t1.DependentOnSectionId AS [t1.DependentOnSectionId],
      t1.EndDate AS [t1.EndDate],
      t1.HiddenWhenEnded AS [t1.HiddenWhenEnded],
      t1.IsSequential AS [t1.IsSequential],
      t1.Position AS [t1.Position],
      t1.PublishDate AS [t1.PublishDate],
      t1.SectionHeader AS [t1.SectionHeader],
      t1.Title AS [t1.Title],
      t1.TypeId AS [t1.TypeId],
      t1.UpdatedOn AS [t1.UpdatedOn],
      t2.Id AS [t2.Id],
      t2.AccessTypeId AS [t2.AccessTypeId],
      t2.AllowAttachments AS [t2.AllowAttachments],
      t2.AllowMultiSubmission AS [t2.AllowMultiSubmission],
      t2.AnswerTypeId AS [t2.AnswerTypeId],
      t2.AttachAudio AS [t2.AttachAudio],
      t2.AttachDocuments AS [t2.AttachDocuments],
      t2.AttachImages AS [t2.AttachImages],
      t2.AttachVideos AS [t2.AttachVideos],
      t2.AuthorId AS [t2.AuthorId],
      t2.AutoAdvanceIfClosed AS [t2.AutoAdvanceIfClosed],
      t2.Body AS [t2.Body],
      t2.CreatedOn AS [t2.CreatedOn],
      t2.DeletedOn AS [t2.DeletedOn],
      t2.DependentOnQuestionId AS [t2.DependentOnQuestionId],
      t2.EndDate AS [t2.EndDate],
      t2.HasCriteria AS [t2.HasCriteria],
      t2.HiddenWhenEnded AS [t2.HiddenWhenEnded],
      t2.Position AS [t2.Position],
      t2.PublishDate AS [t2.PublishDate],
      t2.QuestionTypeId AS [t2.QuestionTypeId],
      t2.SecondaryQuestionText AS [t2.SecondaryQuestionText],
      t2.SectionId AS [t2.SectionId],
      t2.ShowSecondaryQuestion AS [t2.ShowSecondaryQuestion],
      t2.Title AS [t2.Title],
      t2.UpdatedOn AS [t2.UpdatedOn],
      t2.VideoFileId AS [t2.VideoFileId]
    FROM
      BoardParticipants t0
    INNER JOIN
      Sections t1
    ON
      t0.BulletinBoardId = t1.BoardId AND t1.DeletedOn IS NULL
    INNER JOIN
      Questions t2
    ON
      t1.Id = t2.SectionId AND t2.DeletedOn IS NULL
    WHERE
      t0.Hashkey = 'fbba7d4f'

    --> Time:   163 ms


    SELECT
      BulletinBoards.Id AS [BulletinBoards.Id],
      BulletinBoards.BannerImageId AS [BulletinBoards.BannerImageId],
      BulletinBoards.CreatedOn AS [BulletinBoards.CreatedOn],
      BulletinBoards.CustomerId AS [BulletinBoards.CustomerId],
      BulletinBoards.DeletedOn AS [BulletinBoards.DeletedOn],
      BulletinBoards.EndDate AS [BulletinBoards.EndDate],
      BulletinBoards.LanguageId AS [BulletinBoards.LanguageId],
      BulletinBoards.PrivateName AS [BulletinBoards.PrivateName],
      BulletinBoards.ProjectId AS [BulletinBoards.ProjectId],
      BulletinBoards.PublicName AS [BulletinBoards.PublicName],
      BulletinBoards.Server AS [BulletinBoards.Server],
      BulletinBoards.StartDate AS [BulletinBoards.StartDate],
      BulletinBoards.ThemeId AS [BulletinBoards.ThemeId],
      BulletinBoards.UpdatedOn AS [BulletinBoards.UpdatedOn],
      BulletinBoards.UseVersion2Controls AS [BulletinBoards.UseVersion2Controls]
    FROM
      BulletinBoards
    WHERE
      (BulletinBoards.Id = 1477 AND BulletinBoards.DeletedOn IS NULL)

    --> Time:   38 ms

(query2)

SELECT
      t0.Id AS [t0.Id],
      t0.AccessTypeId AS [t0.AccessTypeId],
      t0.AllowAttachments AS [t0.AllowAttachments],
      t0.AllowMultiSubmission AS [t0.AllowMultiSubmission],
      t0.AnswerTypeId AS [t0.AnswerTypeId],
      t0.AttachAudio AS [t0.AttachAudio],
      t0.AttachDocuments AS [t0.AttachDocuments],
      t0.AttachImages AS [t0.AttachImages],
      t0.AttachVideos AS [t0.AttachVideos],
      t0.AuthorId AS [t0.AuthorId],
      t0.AutoAdvanceIfClosed AS [t0.AutoAdvanceIfClosed],
      t0.Body AS [t0.Body],
      t0.CreatedOn AS [t0.CreatedOn],
      t0.DeletedOn AS [t0.DeletedOn],
      t0.DependentOnQuestionId AS [t0.DependentOnQuestionId],
      t0.EndDate AS [t0.EndDate],
      t0.HasCriteria AS [t0.HasCriteria],
      t0.HiddenWhenEnded AS [t0.HiddenWhenEnded],
      t0.Position AS [t0.Position],
      t0.PublishDate AS [t0.PublishDate],
      t0.QuestionTypeId AS [t0.QuestionTypeId],
      t0.SecondaryQuestionText AS [t0.SecondaryQuestionText],
      t0.SectionId AS [t0.SectionId],
      t0.ShowSecondaryQuestion AS [t0.ShowSecondaryQuestion],
      t0.Title AS [t0.Title],
      t0.UpdatedOn AS [t0.UpdatedOn],
      t0.VideoFileId AS [t0.VideoFileId],
      t1.Id AS [t1.Id],
      t1.BoardId AS [t1.BoardId],
      t1.CreatedOn AS [t1.CreatedOn],
      t1.DeletedOn AS [t1.DeletedOn],
      t1.DependentOnSectionId AS [t1.DependentOnSectionId],
      t1.EndDate AS [t1.EndDate],
      t1.HiddenWhenEnded AS [t1.HiddenWhenEnded],
      t1.IsSequential AS [t1.IsSequential],
      t1.Position AS [t1.Position],
      t1.PublishDate AS [t1.PublishDate],
      t1.SectionHeader AS [t1.SectionHeader],
      t1.Title AS [t1.Title],
      t1.TypeId AS [t1.TypeId],
      t1.UpdatedOn AS [t1.UpdatedOn],
      t2.Id AS [t2.Id],
      t2.BulletinBoardId AS [t2.BulletinBoardId],
      t2.CreatedOn AS [t2.CreatedOn],
      t2.DeletedOn AS [t2.DeletedOn],
      t2.Hashkey AS [t2.Hashkey],
      t2.LastLogin AS [t2.LastLogin],
      t2.MessageCount AS [t2.MessageCount],
      t2.ParticipantId AS [t2.ParticipantId],
      t2.RoleId AS [t2.RoleId],
      t2.UpdatedOn AS [t2.UpdatedOn]
    FROM
      Questions t0
    INNER JOIN
      Sections t1
    ON
      t0.SectionId = t1.Id AND t1.DeletedOn IS NULL
    INNER JOIN
      BoardParticipants t2
    ON
      t1.BoardId = t2.BulletinBoardId AND t2.DeletedOn IS NULL
    WHERE
      t2.Hashkey = 'fbba7d4f'

    --> Time:   161 ms


    SELECT
      BulletinBoards.Id AS [BulletinBoards.Id],
      BulletinBoards.BannerImageId AS [BulletinBoards.BannerImageId],
      BulletinBoards.CreatedOn AS [BulletinBoards.CreatedOn],
      BulletinBoards.CustomerId AS [BulletinBoards.CustomerId],
      BulletinBoards.DeletedOn AS [BulletinBoards.DeletedOn],
      BulletinBoards.EndDate AS [BulletinBoards.EndDate],
      BulletinBoards.LanguageId AS [BulletinBoards.LanguageId],
      BulletinBoards.PrivateName AS [BulletinBoards.PrivateName],
      BulletinBoards.ProjectId AS [BulletinBoards.ProjectId],
      BulletinBoards.PublicName AS [BulletinBoards.PublicName],
      BulletinBoards.Server AS [BulletinBoards.Server],
      BulletinBoards.StartDate AS [BulletinBoards.StartDate],
      BulletinBoards.ThemeId AS [BulletinBoards.ThemeId],
      BulletinBoards.UpdatedOn AS [BulletinBoards.UpdatedOn],
      BulletinBoards.UseVersion2Controls AS [BulletinBoards.UseVersion2Controls]
    FROM
      BulletinBoards
    WHERE
      (BulletinBoards.Id = 1477 AND BulletinBoards.DeletedOn IS NULL)

    --> Time:   42 ms

It seems that the IncludeDeleted() method isn't working when used in a Join expression (notice the ".DeletedOn IS NULL" statements are still present).

Is this a bug, or do I need to perform my queries in a different way?

This is using the latest nightly build (Version: 4.0.1466.20917)

Thanks, Paul

Hi Paul,

IncludeDeleted() is a query level setting which applies to the entity being queried on and not entities which are joined so only the IncludeDeleted() attached to the primary entity in the query is being applied.

In query 1 this is BoardParticipants:

from bp in BoardParticipants.IncludeDeleted()

In query 2 this is Questions

from question in Questions.IncludeDeleted()

Jeremy

Hi Jeremy,

Thanks for your reply. Are you able to suggest a workaround to achieve the intent of my queries?

Thanks, Paul

Hi Paul,

Are you able to look at using a stored proc for this? Because we are going to always include the soft delete attribute for joined entities this means you are going to have limited control over this through using the Querying API or LINQ.

We are having a think about changing the behavior of this to respect that flag for joined entities but we wont be making this change in 4.0.

Jeremy

Hi Jeremy,

Currently I'm working around this by simplifying all of our queries and performing all of the join logic in memory. At this stage performance isn't an issue so hopefully that's all I'll need to do.

Is there a case for throwing an exception or something in the situation where IncludeDeleted() has been used but is silently being ignored. In my particular case, my misuse of IncludeDeleted() lead to a bug that went unnoticed in our system for quite a long time.

Anyhow, my current workaround is acceptable, if performance becomes a problem I'll consider stored procedures.

Thanks for you responses,

Paul

Hi Jeremy, Has there been any update on this? Using IncludeDeleted in Joins? Thanks - Imtiaz

No there has been no change with this.

Jeremy

Is there a known work around yet?

I just wasted a day on this too. I would agree that methods that silently fail are a bad idea.

I'd rather not do the join in memory as that was exactly what I was in the process of removing when I hit this issue.

No there isn't any change to this but this is a good reminder we should look at throwing an exception if IncludeDeleted is specified on a joined entity in the query so I will have a look into that.

Jeremy