I am implementing the repository for a RESTful microservice using EF Core 3.1 and MS SQL Server, and I'm experiencing some strange behavior when doing a PUT that updates data backed by a navigation property in the data model. The purpose of the microservice is to manage "category and subcategory" definitions for product information management. There are two levels of resources in the REST service, /categories
and /categories/{id}/subcategories
. However, the underlying data structures are a little more complex, as both categories and subcategories have "divisions" and for a subcategory to have a given division, the category it is under must also have that division.
Here's roughly what the relevant portion of the subcategory object looks like:
{
"id": 7083,
"categoryId": 728,
"name": "Notebooks",
"divisions": [
{
"divisionId": 1,
"status": "active"
},
{
"divisionId": 7,
"status": "inactive"
}
]
}
And here's how I have structured the database to support the requirements:
The problem occurs when a Subcategory is PUT with a different set of divisions than it had previously. For example, using the above entity as an example, suppose a PUT request was received which looked like this:
{
"id": 7083,
"categoryId": 728,
"name": "Notebooks",
"divisions": [
{
"divisionId": 1,
"status": "active"
},
{
"divisionId": 7,
"status": "inactive"
},
{
"divisionId": 3,
"status": "active"
}
]
}
Basically, we should just be adding a CategoryDivisionSubcategory
record for Category 728, Subcategory 7083, Division 3. However, what actually happens is that the CategoryDivisionSubcategory
records for the existing two divisions (1 and 7) are deleted, and only the CategoryDivisionSubcategory
record for the new division (3) is inserted. This is apparently happening because of the way I'm trying to manage the state of the entities and handle REST-style PUTs in which the entire state of the object should be updated.
I have created a module for every entity in my database. Then, I have the following 3 or 4 functions in every module:
fromSql
: Construct the domain model from the EF Core entity
toSql
: Construct the EF Core entity from the domain model
getOrAdd
: Check if the given domain model exists in the DB or the tracked changes for the current context, or add it if not, returning the EF Core entity.
addOrUpdate
: Check if a record matching the primary key of the given domain model exists in the DB, if it already exists, update the non-key fields to match the domain model, otherwise insert the record, and finally return the EF Core entity.
Generally, a given module would either have getOrAdd
or addOrUpdate
, depending on how that entity should behave. Those functions are always implemented using a call to a standard getOrAdd
function behind the scenes:
let getOrAdd<'model, 'entity when 'entity: not struct> toSql (getDbSet: CategoryContext -> DbSet<'entity>) (isEqual: Expr<'entity -> 'entity -> bool>) (context: CategoryContext) (model: 'model) : AsyncResult<'entity, CategoryRepositoryError> =
asyncResult {
let entity = model |> toSql
let dbSet = context |> getDbSet
let filter = <@ (%isEqual) entity @>
let! existing =
query {
for row in dbSet do
where ((%filter) row)
select row
}
|> tryExactlyOneAsync // Wrapper around EF Core .SingleOrDefaultAsync(), returning an Async<'entity option>
match existing with
| Some row ->
return row
| None ->
let checkEquality = filter.Compile()
let tracked = context.ChangeTracker.Entries<'entity>().Where(fun tracked -> checkEquality tracked.Entity) |> Seq.tryHead
match tracked with
| Some trackedEntity ->
return trackedEntity.Entity
| None ->
dbSet.Add(entity) |> ignore
return entity
}
Then, the Subcategory addOrUpdate
function uses that function several times to implement the whole PUT operation:
let addOrUpdate context (subcategory: Subcategory) =
asyncResult {
let! entity =
getOrAdd toSql
(fun context -> context.Subcategories)
<@ fun current row -> current.CategoryId = row.CategoryId && current.Subcategory = row.Subcategory @>
context subcategory
entity.Name <- subcategory.Name |> SubcategoryName.value
entity.CategoryDivisionSubcategories.Clear()
let! divisions = subcategory.Divisions |> Seq.map (CategoryDivisionSubcategory.addOrUpdate context entity) |> AsyncResult.join |> AsyncResult.mapError MultipleRepositoryErrors
for division in divisions do
entity.CatgoryDivisionSubcategories.Add(division)
return entity
}
I would expect that by clearing the CategoryDivisionSubcategories
collection (effectively deleting reach related record via the navigation property), and then adding back each of the ones that are present in the PUT request, I would end up with the state in the DB matching the state of the object in the request. However, this is not the case, and as I mentioned in the example above, I end up with only the net-new records, with any previously existing records being deleted. After the PUT, instead of now having 3 CategoryDivisionSubcategories, I only have 1.
I can make the function work by changing the logic to actually compare each record in the related table via the navigation property with the entries in the array sent in the PUT request, but this seems inefficient, and in practice, I have many more places where I would need to do this:
let removedDivisions = entity.CategoryDivisionSubcategories.AsEnumerable() |> Seq.filter (fun scd -> divisions |> Seq.exists(fun d -> scd.SubcategoryId = d.SubcategoryId && scd.DivisionId = d.DivisionId) |> not) |> Seq.toList
for division in divisions do
entity.CategoryDivisionSubcategories.Add(division)
for removedDivision in removedDivisions do
entity.CategoryDivisionSubcategories.Remove(removedDivision) |> ignore
Is there some way I can avoid doing this "compare every record in the list" logic to ensure I end up with the state in the DB exactly matching the PUT request, or will I need to implement this type of logic everywhere I want to completely replace any child entities in a navigation following a PUT?
I ended up writing a custom Merge
function for DbSet that allows you to produce the new set out of any existing DbSet and an ICollection
with the updated state. Here's how it's implemented:
type DbSetComparison<'entity> =
{
Added: 'entity list
Removed: 'entity list
Modified: 'entity list
Unmodified: 'entity list
}
[<CompileWith(ModuleSuffix)>]
module DbSet =
type private PartialComparison<'entity> =
{
Added: 'entity list
Removed: 'entity list
Matching: ('entity * 'entity) list
}
let private getRemovedAndAdded<'entity, 'key, 'field when 'key: equality and 'field: equality> (newState: 'entity list) (getKey: 'entity -> 'key) (getField: 'entity -> 'field) (current: ICollection<'entity>) =
let existing = current.ToList()
let removed =
existing
|> Seq.filter (fun e1 -> newState |> Seq.exists(fun e2 -> (getKey e1) = (getKey e2)) |> not)
|> Seq.toList
let added = newState |> List.filter (fun e1 -> existing |> Seq.exists(fun e2 -> (getKey e1) = (getKey e2)) |> not)
let matching =
existing
|> Seq.choose (fun e1 -> newState |> Seq.tryFind (fun e2 -> (getKey e1) = (getKey e2)) |> Option.map (fun e2 -> e1,e2))
|> Seq.toList
{ Removed = removed; Added = added; Matching = matching }
let compareBy<'entity, 'key, 'field when 'key: equality and 'field: equality> (newState: 'entity list) (getKey: 'entity -> 'key) (getField: 'entity -> 'field) (current: ICollection<'entity>) =
let partialResults = getRemovedAndAdded newState getKey getField current
let modified = partialResults.Matching |> List.filter (fun (e1,e2) -> (getField e1) <> (getField e2))
let unmodified = partialResults.Matching |> List.filter (fun (e1,e2) -> (getField e1) = (getField e2)) |> List.map snd
{ Added = partialResults.Added; Removed = partialResults.Removed; Modified = modified |> List.map snd; Unmodified = unmodified }
let compare<'entity, 'key when 'entity: equality and 'key: equality> (newState: 'entity list) (getKey: 'entity -> 'key) (current: ICollection<'entity>) =
let partialResults = getRemovedAndAdded newState getKey id current
let modified = partialResults.Matching |> List.filter (fun (e1,e2) -> e1 <> e2)
let unmodified = partialResults.Matching |> List.except modified |> List.map snd
{ Added = partialResults.Added; Removed = partialResults.Removed; Modified = modified |> List.map snd; Unmodified = unmodified }
[<Extension>]
type DbSetExtensions =
[<Extension>]
static member Compare<'entity, 'key when 'entity: equality and 'key: equality>(dbSet: ICollection<'entity>, newState: 'entity list, getKey: 'entity -> 'key) =
dbSet |> DbSet.compare newState getKey
[<Extension>]
static member CompareBy<'entity, 'key, 'field when 'field: equality and 'key: equality>(dbSet: ICollection<'entity>, newState: 'entity list, getKey: 'entity -> 'key, getField: 'entity -> 'field) =
dbSet |> DbSet.compareBy newState getKey getField
[<Extension>]
static member Merge<'entity, 'key when 'entity: equality and 'key: equality>(dbSet: ICollection<'entity>, newState: 'entity list, getKey: 'entity -> 'key, update: 'entity -> 'entity -> unit) =
let changes = dbSet |> DbSet.compare newState getKey
for removed in changes.Removed do
dbSet.Remove(removed) |> ignore
for modified in changes.Modified do
let existing = dbSet.SingleOrDefault(fun e -> (getKey e) = (getKey modified))
if existing |> isNotNull then
update existing modified
for added in changes.Added do
dbSet.Add(added)