Aggregate Bug with Soft Deletes

Hello. Noticed an issue when looking up aggregates of soft deleted records.

Let's take the model:

And the query:

var items = (from x in uow.Events where x.EventOccurrences.Min(eo => eo.Start) >= start select x);

(start is a DateTime)

If there are EventOccurrences that are previous to start but are deleted they are still included in the Min aggregation.

Did some more digging and here is the query LS built:

SELECT
  Events.Id AS [Events.Id],
  Events.Category AS [Events.Category],
  Events.Content AS [Events.Content],
  Events.ContentText AS [Events.ContentText],
  Events.CreatedOn AS [Events.CreatedOn],
  Events.DeletedOn AS [Events.DeletedOn],
  Events.Meta AS [Events.Meta],
  Events.ShortId AS [Events.ShortId],
  Events.Slug AS [Events.Slug],
  Events.Title AS [Events.Title],
  Events.UpdatedOn AS [Events.UpdatedOn]
FROM
  (
    SELECT
      sxt0.Id AS [Id],
      sxt0.Category AS [Category],
      sxt0.Content AS [Content],
      sxt0.ContentText AS [ContentText],
      sxt0.CreatedOn AS [CreatedOn],
      sxt0.DeletedOn AS [DeletedOn],
      sxt0.Meta AS [Meta],
      sxt0.ShortId AS [ShortId],
      sxt0.Slug AS [Slug],
      sxt0.Title AS [Title],
      sxt0.UpdatedOn AS [UpdatedOn],
      (SELECT
        MIN(sx_MinStart.Start) AS [sx_MinStart.Start]
      FROM
        (
          SELECT
            EventOccurrences.Start AS [Start],
            EventOccurrences.EventId AS [EventId]
          FROM
            EventOccurrences
        )
        sx_MinStart
      WHERE
        sx_MinStart.EventId = sxt0.Id
      ) AS MinStart
    FROM
      Events sxt0
  )
  Events
WHERE
  (Events.MinStart >= '10/1/2014 12:00:00 AM' AND Events.DeletedOn IS NULL)

Seems it should be this instead:

SELECT
  Events.Id AS [Events.Id],
  Events.Category AS [Events.Category],
  Events.Content AS [Events.Content],
  Events.ContentText AS [Events.ContentText],
  Events.CreatedOn AS [Events.CreatedOn],
  Events.DeletedOn AS [Events.DeletedOn],
  Events.Meta AS [Events.Meta],
  Events.ShortId AS [Events.ShortId],
  Events.Slug AS [Events.Slug],
  Events.Title AS [Events.Title],
  Events.UpdatedOn AS [Events.UpdatedOn]
FROM
  (
    SELECT
      sxt0.Id AS [Id],
      sxt0.Category AS [Category],
      sxt0.Content AS [Content],
      sxt0.ContentText AS [ContentText],
      sxt0.CreatedOn AS [CreatedOn],
      sxt0.DeletedOn AS [DeletedOn],
      sxt0.Meta AS [Meta],
      sxt0.ShortId AS [ShortId],
      sxt0.Slug AS [Slug],
      sxt0.Title AS [Title],
      sxt0.UpdatedOn AS [UpdatedOn],
      (SELECT
        MIN(sx_MinStart.Start) AS [sx_MinStart.Start]
      FROM
        (
          SELECT
            EventOccurrences.Start AS [Start],
            EventOccurrences.EventId AS [EventId]
          FROM
            EventOccurrences
          WHERE
            EventOccurrences.DeletedOn IS NULL
        )
        sx_MinStart
      WHERE
        sx_MinStart.EventId = sxt0.Id
      ) AS MinStart
    FROM
      Events sxt0
  )
  Events
WHERE
  (Events.MinStart >= '10/1/2014 12:00:00 AM' AND Events.DeletedOn IS NULL)

This piece is the difference:

  WHERE
    EventOccurrences.DeletedOn IS NULL

Do you need anything more from me to address this?

No - thanks - we are still investigating this. I will let you know once we have made more progress.

Jeremy

Any progress on this front?

No progress still sorry - this one is still on our backlog to try and address in the future as its not a simple fix. I will certainly post any update once we have something.

Jeremy

It's been over a year now. Any progress on this? That yearly subscription is starting to not make much sense.

14 months bump.