How to deal with changing entities in Cosmos DB for soft-deletion?-CodePudding

I've been working on an application using ASP.NET Core using the Entity Framework Core connector for Cosmos DB. For the most part, it's been smooth sailing. I've reached a point where I'd like to add support for soft-deleting records from the database, using my application to query for a DeletedAt property to determine whether or not to include an entity in the results.

I'm using a base entity type to which I'm adding the aforementioned timestamp:

    public abstract class Entity
    {
        [Key]
        public long Id { get; internal set; }

        public DateTimeOffset CreatedAt { get; set; }

        public DateTimeOffset? UpdatedAt { get; set; }

        public DateTimeOffset? DeletedAt { get; set; }
    }

This updated code runs just fine. However, I do have several thousands of entities in my database that don't have the DeletedAt property defined.

I'm currently performing my queries from a generic repository type that does something along the lines of :

return await Queryable.Where(x => x.DeletedAt == null).Where(predicate).ToListAsync();

This works fine for new entities that do have a DeletedAt property defined, but excludes all of my older entities that don't have the property set. I'd expect EF to assume default values for properties that aren't defined, but it appears to ignore the old entities altogether.

Normally, using SQL Server, I'd just apply a migration and retrofit all of the older entities with a null DeletedAt timestamp, and all would be dandy. However, in Cosmos DB, I'm not sure how to handle this case. Do I have to go through all the older entities and retrofit them with the deletion timestamp, or is there another way to deal with changing entities and values missing?

Thanks!

CodePudding user response：

If you are using the .NET SDK for Cosmos DB, and thus using its deserialisation (which uses json.NET) then if your model type has a nullable property you don't need a value for that property set in the CosmosDB document.

It will remain a .NET instance with null values.

Thus optional properties in the CosmosDB document can be easily handled with nulls in your application code.

I set my serialisation options for Cosmos DB to not save null values to just avoid storing such properties all together.

CodePudding user response：

In Cosmos there's a difference between null and no value (undefined). In SQL query text you would use the following to query on a property being defined (and additionally you can add a null check to the expression):

SELECT * 
FROM c 
WHERE NOT IS_DEFINED(c.example)

For the C# Cosmos SDK with linq you could use Microsoft.Azure.Cosmos.Linq and do:

var qry = container.GetItemLinqQueryable<Item>()
    .Where(x => !x.Example.IsDefined())
    .ToFeedIterator();

For EF this unfortunately doesn't seem to be possible as of this moment. Links:

Git issue

Current available functions

If you want to set it to null you would indeed need to iterate over all items and set it to make such queries possible using EF.