EF Core replace all related child entities

entity-framework-core f# sql-server

Question

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:

Database Layout

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?

1
4
12/17/2019 7:02:39 PM

Popular Answer

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)
0
1/16/2020 2:21:20 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow