Loading soft-deleted child entities

Hi guys, I have a bunch of child entities that have been soft-deleted that I would like to load for a read-only view.

I understand that the IncludeDeleted flag only works for the aggregate root and doesn't extend to all the children that this parent tries to load.

What would be the best way to solve this problem? Do you recommend using another column altogether (e.g. DeletedOnDate) and managing it manually? Or, is there a better way?

Thank you!

IncludeDeleted extends to any entities loaded in that query, so if you are eager loading the child entities as part of loading the parent then both will include deleted entities in any results returned. It does not "persist" to any lazy loaded children so these load normally and will filter any deleted entities.

We would definitely recommend using the eager loading approach to handle this type of situation.

Jeremy

Thank you Jeremy. That's what I wanted to know.

I am using LightSpeed 3.11 for now so I assume that this behavior works for it just as it works for version 4?

Thanks again for your prompt reply!

Yes, thats correct :) We have not made any changes to this, so it will work just the same in LightSpeed 4.

Jeremy

Hello,

I am trying to eagerly load all with IncludeDeleted on an object by id that references n other entities via a many to many relationship. It doesn't appear to work. I can see the root entity is loaded WITHOUT the DeletedOn IS NULL clause, the intermediary (M2M) table is loaded WITHOUT the DeletedOn IS NULL clause but then the final table is HAS the DeletedOn IS NULL clause. Are you sure this works with Many to Many relationships? It seems to only work with many to ones. I'm probably doing something wrong. I am using an aggregate ("GetCommunicationAggregate"). All the relevant relationships defined are set to eager load using the same aggregate name.

var query = new Query(EntityType) {AggregateName = aggregateName, QueryExpression = queryExpression};
query.IncludeDeleted = true;

T entity = RepositoryUnitOfWork.Find<T>(query).FirstOrDefault();

The query expression was just: {ClientId = 39d915c1-b925-df11-bbbb-6cf04907333d}, T is Communication. Find has not been overridden - it's the lightspeed unitofwork implementation.

SELECT
  [Communication].[Id] AS [Communication.Id],
  [Communication].[ClientId] AS [Communication.ClientId],
  [Communication].[CommunicationSupportRequirements] AS [Communication.CommunicationSupportRequirements],
  [Communication].[ComprehensionAssistanceRequired] AS [Communication.ComprehensionAssistanceRequired],
  [Communication].[ComprehensionSupportRequirements] AS [Communication.ComprehensionSupportRequirements],
  [Communication].[CreatedOn] AS [Communication.CreatedOn],
  [Communication].[HasAttachment] AS [Communication.HasAttachment],
  [Communication].[LevelOfIndependenceId] AS [Communication.LevelOfIndependenceId],
  [Communication].[LevelOfIndependenceOtherDetail] AS [Communication.LevelOfIndependenceOtherDetail],
  [Communication].[LockVersion] AS [Communication.LockVersion],
  [Communication].[MemoryAssistanceRequired] AS [Communication.MemoryAssistanceRequired],
  [Communication].[MemorySupportRequirements] AS [Communication.MemorySupportRequirements],
  [Communication].[PrimaryCommunicationMethodId] AS [Communication.PrimaryCommunicationMethodId],
  [Communication].[PrimaryCommunicationMethodOtherDetail] AS [Communication.PrimaryCommunicationMethodOtherDetail],
  [Communication].[UpdatedOn] AS [Communication.UpdatedOn]
FROM
  [Cirts].[Communication]
WHERE
  [Communication].[ClientId] = @p0;

SELECT
  [CommunicationAids].[Id],
  [CommunicationAids].[CommunicationAidId],
  [CommunicationAids].[CommunicationId]
FROM
  [Cirts].[CommunicationAids]
WHERE
  EXISTS (
    SELECT
      [Communication].*
    FROM
      [Cirts].[Communication]
    WHERE
      [Communication].[Id] = [CommunicationAids].[CommunicationId] AND
      [Communication].[ClientId] = @p0
  );

SELECT
  [CommunicationAid].[Id],
  [CommunicationAid].[CreatedOn],
  [CommunicationAid].[DeletedOn],
  [CommunicationAid].[Description],
  [CommunicationAid].[IsProtected],
  [CommunicationAid].[LockVersion],
  [CommunicationAid].[Sequence],
  [CommunicationAid].[UpdatedOn]
FROM
  [Cirts].[CommunicationAid]
WHERE
  (EXISTS (
    SELECT
      [CommunicationAids].*
    FROM
      [Cirts].[CommunicationAids]
    WHERE
      [CommunicationAids].[CommunicationAidId] = [CommunicationAid].[Id] AND
      EXISTS (
        SELECT
          [Communication].*
        FROM
          [Cirts].[Communication]
        WHERE
          [Communication].[Id] = [CommunicationAids].[CommunicationId] AND
          [Communication].[ClientId] = @p0
      )
  ) AND [CommunicationAid].[DeletedOn] IS NULL);

Here is the relevant diagram:

I have actually just tried this with a simpler scenario (M:1) and it doesn't work either.

I have an entity called Education that will reference ModeOfTransport. ModeOfTransport is set to load on aggregrate "GetEducationAggregate". Given:

var query = new Query { AggregateName = "GetEducationAggregate", Identifier = id};
query.IncludeDeleted = true;
Education education = this.RepositoryUnitOfWork.Find<Education>(query).SingleOrDefault();

I would expect any related ModeOfTransports to be loaded regardless of whether they were deleted or not. However that does not occur. The query for Education includes this in the SQL:

SELECT
  [ModeOfTransport].[Id],
  [ModeOfTransport].[CreatedOn],
  [ModeOfTransport].[DeletedOn],
  [ModeOfTransport].[Description],
  [ModeOfTransport].[IsProtected],
  [ModeOfTransport].[LockVersion],
  [ModeOfTransport].[Sequence],
  [ModeOfTransport].[UpdatedOn]
FROM
  [Cirts].[ModeOfTransport]
WHERE
  (EXISTS (
    SELECT
      [Education].*
    FROM
      [Cirts].[Education]
    WHERE
      [Education].[ModeOfTransportId] = [ModeOfTransport].[Id] AND
      [Education].[Id] = @p0
  ) AND [ModeOfTransport].[DeletedOn] IS NULL)

It's explicitly excluding deleted ModeOfTransport entities. Further the "Find" call craps out with a KeyNotFoundException. Which is the same behaviour as I get with the other many to many association.

   at System.ThrowHelper.ThrowKeyNotFoundException()
   at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
   at ..€(AssociationModel )
   at ..(LightSpeedContext , AliasedTypeModel , QueryExpression , String , Boolean )
   at ..(LightSpeedContext , List`1 , QueryExpression , Order , Group , String , Boolean , Boolean )
   at ..(AliasedTypeModel , IList , String , Boolean )
   at ..(TypeModel , Query , IList )
   at ..(Query , TypeModel , IList )
   at ..(Query , IList )
   at Mindscape.LightSpeed.UnitOfWork.Find(Query query, IList results)
   at Mindscape.LightSpeed.UnitOfWorkBase.Find[TEntity](Query query)
   at ***.*****.Server.Services.Personal.Repositories.EducationRepository.GetEducation(Guid id) in D:\Cirts\ReferenceData\*****\src\****\Server\Lwb.*****.Server.Services\Personal\Repositories\EducationRepository.cs:line 22

Lightspeed version is 3.1.1891.14711

I have created a test project to isolate the issue:

It contains two entities both with a PK called ID. One entity references another M:1 style.

The two entities are Root and Related. Related has soft delete enabled.

[Fact]
public void CanLoadAnEntityByAggregateWhereReferenceIsDeletedFailsWithoutIncludeDeleted()
{
    var context = new LightSpeedContext();
    context.ConnectionString = "Data Source=.;Integrated Security=SSPI;Initial Catalog=LSTest";
    var uow = context.CreateUnitOfWork();
    var query = new Query
    {
        AggregateName = "GetRelated",
        Identifier = Guid.Parse("a468679e-1727-493b-bfa3-9358007b6248")
    };
    var result = uow.Find<Root>(query);
    //unsure of what the expected behaviour should be here... as it never works/gets passed the above line.
}

The above always fails as the "Related" that the queried "Root" relates to has been soft deleted. The Find call fails ungracefully with an ambiguous KeyNotFoundException.

I've attached the sample VS2010 solution. Expects the db to be local and the catalot to be called LSTest.

DB Script - (or use the attached SQL 2008 R2 backup file)

/****** Object:  Table [dbo].[Related]    Script Date: 07/16/2012 17:16:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Related](
    [Id] [uniqueidentifier] NOT NULL,
    [DeletedOn] [datetime] NULL,
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]
GO
/****** Object:  Table [dbo].[Root]    Script Date: 07/16/2012 17:16:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Root](
    [Id] [uniqueidentifier] NOT NULL,
    [RelatedId] [uniqueidentifier] NULL,
 CONSTRAINT [PK__Root__3214EC0707F6335A] 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]
GO
/****** Object:  Default [DF_Related_Id]    Script Date: 07/16/2012 17:16:09 ******/
ALTER TABLE [dbo].[Related] ADD  CONSTRAINT [DF_Related_Id]  DEFAULT (newid()) FOR [Id]
GO
/****** Object:  Default [DF_Root_Id]    Script Date: 07/16/2012 17:16:09 ******/
ALTER TABLE [dbo].[Root] ADD  CONSTRAINT [DF_Root_Id]  DEFAULT (newid()) FOR [Id]
GO
/****** Object:  ForeignKey [FK__Root__RelatedId__09DE7BCC]    Script Date: 07/16/2012 17:16:09 ******/
ALTER TABLE [dbo].[Root]  WITH CHECK ADD  CONSTRAINT [FK__Root__RelatedId__09DE7BCC] FOREIGN KEY([RelatedId])
REFERENCES [dbo].[Related] ([Id])
GO
ALTER TABLE [dbo].[Root] CHECK CONSTRAINT [FK__Root__RelatedId__09DE7BCC]
GO

I tried this with lightspeed 4.0.646.18138 as well and got the same result. What am I doing wrong?

If I set IncludeDeleted to true in my test I get exactly the same result and sql generated is:

exec sp_executesql N'SELECT
  Root.Id AS [Root.Id],
  Root.RelatedId AS [Root.RelatedId]
FROM
  Root
WHERE
  Root.Id = @p0;

SELECT
  Related.Id,
  Related.DeletedOn
FROM
  Related
WHERE
  (EXISTS (
    SELECT
      Root.*
    FROM
      Root
    WHERE
      Root.RelatedId = Related.Id AND
      Root.Id = @p0
  ) AND Related.DeletedOn IS NULL)',N'@p0 uniqueidentifier',@p0='A468679E-1727-493B-BFA3-9358007B6248'

It works when there is no criteria (where clause) specified. Fails when there is.

Hi Sam,

Thanks for the detailed information on this. I have had a look at the repro you provided but I am not seeing a problem in testing here, you are however using a much older version of LightSpeed so its likely we have fixed something in the interim which is leading to the crash you saw. That said I have set up some test cases here which has surfaced an issue where the soft delete flag would be included on an association load despite the IncludeDeleted being set on the query so its likely that this is what you are running into. I have added in a fix for this which will be available in the next nightly build for 4.0.

Let us know once you have had a chance to grab the nightly and review this against your actual model to see if this covers the issue you are seeing. If that doesnt cover the problem would you please be able to send through a small repro project which highlights the issue and I can debug this further locally.

Thanks!
Jeremy

Hello Jeremy,

I was having this KeyNotFoundException using 3.1.1820.14537 AND the version 4 trial I downloaded yesterday. Yes there is an issue where the DeletedOn clause is being included in the association load, but there is also a separate issue where entities referencing deleted entities fail to load. They are sort of related, both around soft deletes.

The root entity query loads up the "root" entity and it's related "related" entities. However it doesn't find the referenced "related" entity in the result and throws a keynotfoundexception. The problem is this occurs regardless of whether deletedon is included or not effectively meaning that any entity referencing a soft deleted entity is broken and cannot be loaded in any scenario.

In my previous post I attached a 7zip file containing a test solution that reproduces the issue. If you run the provided SQL script and then run the test in the project, the test query issued in the test fails with a KeyNotFoundException.

Did you run my test solution against the same version of lightspeed? Are all of the unit tests passing?

We've verified this across 2 machines and with both v3.1.1 and the latest v4.

We are using lightspeed in a very large application that has been stable in production for over 2 years. It would be a major risk to upgrade to Lightspeed v4 and could introduce a series of regressions that we cannot anticipate. Therefore we are very keen to stay with v3.1.1.

Can you please try this test with v3.1.1820.14537? We would like to try and find a workaround that is possible with this version.

Thanks Sam

Hi Sam,

Both tests are passing for me here. The version of 4.0 you mentioned earlier dates to Aug 11 2011 so I suspect there has been a fix for the issue you are running into made in between then and now.

I have sent you an email to Sam K who should forward this on to you which has your test project and a copy of the Mindscape.LightSpeed + Linq assemblies I am using with the fix included which you can check against. Please ping me back via email as needed.

Jeremy