2

I have a pattern in my database tables for soft deleting. I want to generate Linq queries to fetch data that is not expired.

For example with the two tables below, a User can have multiple addresses. An address can be soft deleted by setting the SoftDeleteDate to Datetime.Now. When fetching the User we can create a query to exclude UserAddress where UserAddress.SoftDeleteDate is not null.

TABLE [dbo].[User](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](25) NOT NULL,
[SoftDeleteDate] [datetimeoffset](7) NULL
)

TABLE [dbo].[UserAddress](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[UserId] [bigint] NOT NULL,
[StreetName] [bigint] NOT NULL,
[City] [nvarchar](50) NULL,
[State] [nvarchar](50) NULL,
[SoftDeleteDate] [datetimeoffset](7) NULL
)

This is a simple use case. In practice an entity such as a user can be linked to child entities to several degrees of nesting.

Generic methods for fetching can be easily created using DbContext. These methods fetch nested entities using "Include". But linq has no concept of "Exclude"

T GetDataById<T>(long id)
{
    return Context.Set<T>().Find(id);
}

I want to create a generic method that produces Linq queries that excludes any expired child entities to no more than three degrees of nesting. This way, when I fetch a User I can exclude expired UserAddresses.

I have seen some posts about PredicateBuilder http://www.albahari.com/nutshell/predicatebuilder.aspx but not sure if it can be used to generate a query with the needed filter.

Wondering if anyone out there has worked on this problem.

Femi A
  • 95
  • 8
  • You may find it easier to do this after fetching the data perhaps using AutoMapper to map each collection to filter out the soft-deleted entries. Is there a performance reason why you couldn't fetch them all and then filter? – Ian Mercer May 04 '19 at 17:37
  • See also https://stackoverflow.com/a/7079759/224370 – Ian Mercer May 04 '19 at 17:46
  • Are you using EF6 or EF Core or something else? Please add an appropriate tag. – Olivier Jacot-Descombes May 04 '19 at 17:49
  • Se msdn : https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetimeoffset-transact-sql?view=sql-server-2017 – jdweng May 04 '19 at 18:15
  • This is using EntityFramework 6.2.0. I have not found a decent point after querying where I can intercept the data returned and the loop through and filter. So I am focusing on the query itself. – Femi A May 05 '19 at 14:48
  • [stackoverflow.com/a/7079759/224370](https://stackoverflow.com/a/7079759/224370) is very helpful and covers what I need here. Much thanks Ian! – Femi A May 05 '19 at 14:53

0 Answers0