LINQ to SQL: filter nested objects with soft deletes

Question

Hello everyone,

I'm using soft deleting in my database (IsDeleted field). I'm actively using LoadWith and AssociateWith methods to retrieve and filter nested records.

The thing is AssociateWith only works with properties that represents a one-to-many relationship.

DataLoadOptions loadOptions = new DataLoadOptions();
loadOption.LoadWith<User>(u = > u.Roles);
loadOption.AssociateWith<User>(u = > u.Roles.Where(r = > !r.IsDeleted));

In the example above I just say: I want to retrieve users with related (undeleted) roles.

But when I have one-to-one relationship, e.g. Document -> File (the only one file is related to the document) I'm unable to filter soft deleted object:

DataLoadOptions loadOptions = new DataLoadOptions();
loadOption.LoadWith<Document>(d = > d.File);
// the next certainly won't work
loadOption.AssociateWith<File>(f = > !f.IsDeleted);

So, is there any idea how to filter records within the one-to-one relationship?

Thanks!

Answer 1

maybe try:

loadOptions.AssociateWith<File>(f => f.IsDeleted ? null : f);

this will give you null instead of files where IsDeleted is true.

Answer 2

So far I found only one suitable solution for this (apart from not to use soft deletes at all): to delete the soft-deletion relationship on entity update.

E.g. when I decided to remove a file from the document, I perform something like:

// this may be a part of update method
var file = document.File;
if (file.IsDeleted)
{
    // attach soft deleted file
    context.Files.Attach(file, true); 

    // remove a file reference
    document.File = null;
}

// attach document entity and submit changes
context.Documents.Attach(document, true);
context.SubmitChanges();

So, this may be a replacement for one-to-one relationship filtering on complex data retrieving.