LINQ to SQL: filter nested objects with soft deletes
- by Alex
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!
