Update an entity's property's values in the database whithin a repository query but before the query returns

Question

I know my title sounds a little bit confusing but here's the better explanation:

I have an API made with ASP.NET CORE and .NET 5 where I use the repository pattern to perform all my DB queries.

For one of these queries I need to have some kind of calculation made and set the result of that calculation as the value of one of the entity's property in the database before it returns the entire object, but the calculation needs to happen before the query returns because I need the query to also return the new value inside the object.

In other words, here's what I had so far:

public async Task<IReadOnlyList<Place>> GetAllPlacesByCategoryWithRelatedDataAndFilters(Guid id, string city)
    {
        // I need the ratio to be calculated before I return "places"
        var places = await context.Places
            .Include(d => d.Category)
            .Where(d => d.CategoryId == id)
            .ToListAsync();

        foreach(var place in places)
        {
            // Ratio is a double
            place.Ratio = CalculateRatioByMultiplying(place.Amount1, place.Amount2);
            // Here's where I don't know how to really update the value inside of the
            // database, if I leave it like this then it will show the new value when
            // the query is performed but the value is still the default value in the
            // database

            // I've also tried this but this time nothing happens,
            // it doesn't even throw an Exception or anything and the value
            // is still the default value in the database
            var ratio = CalculateRatioByMultiplying(place.Amount1, place.Amount2);
            place.Ratio = ratio;
            await placeRepository.UpdateAsync(place);

            // Also the CalculateRatioByMultiplying is a method I've added to the
            // IPlaceRepository and so in this PlaceRepository too that just
            // multiplies arg1 with arg2 and returns a double
        }

        // Re-do the query with the new Ratio values for each of the places
        var places = await context.Places
            .Include(d => d.Category)
            .Where(d => d.CategoryId == id)
            .Where(d => d.Ratio != 0
            && d.City == city)
            .OrderByDescending(d => d.Ratio)
            .Take(10)
            .ToListAsync();

        return places;
    }

However, I'm also using the mediator pattern to query from the controllers ( I'm using clean architecture ) and some times I get confused as to should the calculation be made in the "GetAllPlacesByCategoryWithRelatedDataAndFiltersQueryHandler" ( where the query is called ) or in the repository directly.

Unfortunately I can't share the project or any sample code as it is a private project but I can answer any question you have of course.

Thanks for your help

I think you should have one query (queryA) that just returns unmodified results. And then you also need a command/operation (commandA) that actually uses that queryA and applies/persists changes. and then you make a 3rd query/command commandB that uses both queryA and commandA as well as return some values. see https://stackoverflow.com/questions/8862888/get-result-of-executed-method-in-command-pattern — Bangonkali, Jun 08 '21 at 23:52
@Bangonkali I actually have tried something like this and it didn't work, I've tried to put it in the Commands, call mutliple Commands ( the update one and then the get one ) but nothing worked, thank you for answering ! — Jimmy, Jun 09 '21 at 01:24

score 0 · Answer 1 · answered Jun 09 '21 at 00:28

0

You're trying to update something (executing a command) while querying the data. Not sure if you're using CQRS or not, but it's still a separation of concerns problem.

May I suggest you entirely remove the update of place.Ratio from this query. And in all the commands that might have effects on place.Ratio, you could publish/dispatch a PlaceRatioRequiredChangedEvent event (it's an INotification for MediatR) then handle this event/notification.

I know it's a handful of work, but it'll set a better business flow for later development.

answered Jun 09 '21 at 00:28

Củ Su Hào

372
6
15

Awesome, thank you so much, I figured it needed to be an event, yes I'm using CQRS, just one question: When I add the event supposed to change the value of the Ratio, do I call the UpdateAsync method of the repository or the UpdateCommand of CQRS ? – Jimmy Jun 09 '21 at 01:16
@SachaPerson I'll say it depends on your personal taste :). Since I don't use Repository in CQRS, EF core, and MediatR, I'll do the most simple way: implement INotificationHandler with XXXDbContext injected into it, then update place.Ratio. The implementation might vary depends on your structure. But please keep in mind MediatR is written (unambitiously) to simplify and reduce noise in our code, so no need to complicate things :) – Củ Su Hào Jun 09 '21 at 04:14
1

Thank you ! I'll try that and make your answer the accepted answer if it works ! – Jimmy Jun 09 '21 at 20:17

Update an entity's property's values in the database whithin a repository query but before the query returns

1 Answers1