Entity Framework Core - efficient way to update Entity that has children based on JSON representation of entity being passed in via Web API

Question

I am writing an .NET Core Web API which is backed by Entity Framework Core and having a PostgreSQL database underneath (AWS Aurora). I have been able to learn and successfully work with EF Core for inserts and querying data, which is great, but starting to look into UPDATES of existing entities and it's become unclear to me the most efficient way to achieve what I'm after. I have my Database Entities as a result of my Database First scaffolding exercise. I have an entity that is similar to the below (simplified).

Customer -< Addresses -< ContactInformation

So a Customer, which may have multiple Addresses and / or multiple ContactInformation records. I am expecting my Web API to pass in a JSON payload that can be converted to a Customer with all associated information. I have a method which converts my CustomerPayload to a Customer that can be added to the database:

public class CustomerPayload : Payload, ITransform<CustomerPayload, Customer>
{
    [JsonProperty("customer")]
    public RequestCustomer RequestCustomer { get; set; }

    public Customer Convert(CustomerPayload source)
    {
        Console.WriteLine("Creating new customer");
        Customer customer = new Customer
        {
            McaId = source.RequestCustomer.Identification.MembershipNumber,
            BusinessPartnerId = source.RequestCustomer.Identification.BusinessPartnerId,
            LoyaltyDbId = source.RequestCustomer.Identification.LoyaltyDbId,
            Title = source.RequestCustomer.Name.Title,
            FirstName = source.RequestCustomer.Name.FirstName,
            LastName = source.RequestCustomer.Name.Surname,
            Gender = source.RequestCustomer.Gender,
            DateOfBirth = source.RequestCustomer.DateOfBirth,
            CustomerType = source.RequestCustomer.CustomerType,
            HomeStoreId = source.RequestCustomer.HomeStoreId,
            HomeStoreUpdated = source.RequestCustomer.HomeStoreUpdated,
            StoreJoined = source.RequestCustomer.StoreJoinedId,
            CreatedDate = DateTime.UtcNow,
            UpdatedDate = DateTime.UtcNow,
            UpdatedBy = Functions.DbUser
        };

        Console.WriteLine("Creating address");
        if (source.RequestCustomer.Address != null)
        {
            customer.Address.Add(new Address
            {
                AddressType = "Home",
                AddressLine1 = source.RequestCustomer.Address.AddressLine1,
                AddressLine2 = source.RequestCustomer.Address.AddressLine2,
                Suburb = source.RequestCustomer.Address.Suburb,
                Postcode = source.RequestCustomer.Address.Postcode,
                Region = source.RequestCustomer.Address.State,
                Country = source.RequestCustomer.Address.Country,
                CreatedDate = DateTime.UtcNow,
                UpdatedDate = DateTime.UtcNow,
                UpdatedBy = Functions.DbUser,
                UpdatingStore = null, // Not passed by API at present
                AddressValidated = false, // Not passed by API
                AddressUndeliverable = false, // Not passed by API
            });
        }

        Console.WriteLine("Creating marketing preferences");
        if (source.RequestCustomer.MarketingPreferences != null)
        {
            customer.MarketingPreferences = source.RequestCustomer.MarketingPreferences
                .Select(x => new MarketingPreferences()
                {
                    ChannelId = x.Channel,
                    OptIn = x.OptIn,
                    ValidFromDate = x.ValidFromDate,
                    UpdatedBy = Functions.DbUser,
                    CreatedDate = DateTime.UtcNow,
                    UpdatedDate = DateTime.UtcNow,
                    ContentTypePreferences = (from c in x.ContentTypePreferences
                        where x.ContentTypePreferences != null
                        select new ContentTypePreferences
                        {
                            TypeId = c.Type,
                            OptIn = c.OptIn,
                            ValidFromDate = c.ValidFromDate,
                            ChannelId = x.Channel // Should inherit parent marketing preference channel
                        }).ToList(),
                    UpdatingStore = null // Not passed by API
                })
                .ToList();
        }

        Console.WriteLine("Creating contact information");
        if (source.RequestCustomer.ContactInformation != null)
        {
            // Validate email if present
            var emails = (from e in source.RequestCustomer.ContactInformation
                where e.ContactType.ToUpper() == ContactInformation.ContactTypes.Email && e.ContactValue != null
                select e.ContactValue);

            if (!emails.Any()) throw new Exception("At least 1 email address must be provided for a customer registration.");

            foreach (var email in emails)
            {
                Console.WriteLine($"Validating email {email}");
                if (!IsValidEmail(email))
                {
                    throw new Exception($"Email address {email} is not valid.");
                }
            }

            customer.ContactInformation = source.RequestCustomer.ContactInformation
                .Select(x => new ContactInformation()
                {
                    ContactType = x.ContactType,
                    ContactValue = x.ContactValue,
                    CreatedDate = DateTime.UtcNow,
                    UpdatedBy = Functions.DbUser,
                    UpdatedDate = DateTime.UtcNow,
                    Validated = x.Validated,
                    UpdatingStore = x.UpdatingStore

                })
                .ToList();
        }
        else
        {
            throw new Exception("Minimum required elements not present in POST request");
        }
        Console.WriteLine("Creating external cards");
        if (source.RequestCustomer.ExternalCards != null)
        {
            customer.ExternalCards = source.RequestCustomer.ExternalCards
                .Select(x => new ExternalCards()
                {
                    CardNumber = x.CardNumber,
                    CardStatus = x.Status,
                    CardDesign = x.CardDesign,
                    CardType = x.CardType,
                    UpdatingStore = x.UpdatingStore,
                    UpdatedBy = Functions.DbUser
                })
                .ToList();
        }

        Console.WriteLine($"Converted customer object --> {JsonConvert.SerializeObject(customer)}");
        return customer; 
    }

I would like to be able to look up an existing customer by McaId (which is fine, I can do that via)

            var customer = await loyalty.Customer
                .Include(c => c.ContactInformation)
                .Include(c => c.Address)
                .Include(c => c.MarketingPreferences)
                .Include(c => c.ContentTypePreferences)
                .Include(c => c.ExternalCards)
                .Where(c => c.McaId == updateCustomer.McaId).FirstAsync(); 

But then be able to neatly update that Customer and associated tables with any different values for any properties contained in the Customer - OR - its related entities. So, in pseudo code:

CustomerPayload (cust: 1234) Comes in. 
Convert CustomerPayload to Customer(1234)
Get Customer(1234) current entity and related data from Database. 
Check changed values for any properties of Customer(1234) compared to Customer(1234) that's come in. 
Generate the update statement: 
UPDATE Customer(1234)
Set thing = value, thing = value, thing = value. 
UPDATE Address where Customer = Customer(1234) 
Set thing = value

Save to Database.

Can anyone help as to the best way to achieve this?

EDIT: Updated with attempt. Code below:

public static async void UpdateCustomerRecord(CustomerPayload customerPayload)
{
    try
    {
        var updateCustomer = customerPayload.Convert(customerPayload);

        using (var loyalty = new loyaltyContext())
        {
            var customer = await loyalty.Customer
                .Include(c => c.ContactInformation)
                .Include(c => c.Address)
                .Include(c => c.MarketingPreferences)
                .Include(c => c.ContentTypePreferences)
                .Include(c => c.ExternalCards)
                .Where(c => c.McaId == updateCustomer.McaId).FirstAsync();

            loyalty.Entry(customer).CurrentValues.SetValues(updateCustomer);
            await loyalty.SaveChangesAsync();
            //TODO expand code to cover scenarios such as an additional address on an udpate
        }
    }
    catch (ArgumentNullException e)
    {
        Console.WriteLine(e);
        throw new CustomerNotFoundException();
    }
}

All I've changed is the last name of the customer. No errors occur, however the record is not updated in the database. I have the following settings on so was expecting to see the generated SQL statements in my log, but no statements were generated:

Entity Framework Core 3.1.4 initialized 'loyaltyContext' using provider 'Npgsql.EntityFrameworkCore.PostgreSQL' with options: SensitiveDataLoggingEnabled

That's the only entry I have listed in my log.

Answer 1

You are working with graphs of disconnected entities. Here is the documentation section that might be of interest to you.

Example:

var existingCustomer = await loyalty.Customer
    .Include(c => c.ContactInformation)
    .Include(c => c.Address)
    .Include(c => c.MarketingPreferences)
    .Include(c => c.ContentTypePreferences)
    .Include(c => c.ExternalCards)
    .FirstOrDefault(c => c.McaId == customer.McaId);

if (existingCustomer == null)
{
    // Customer does not exist, insert new one
    loyalty.Add(customer);
}
else
{
    // Customer exists, replace its property values
    loyalty.Entry(existingCustomer).CurrentValues.SetValues(customer);

    // Insert or update customer addresses
    foreach (var address in customer.Address)
    {
        var existingAddress = existingCustomer.Address.FirstOrDefault(a => a.AddressId == address.AddressId);

        if (existingAddress == null)
        {
            // Address does not exist, insert new one
            existingCustomer.Address.Add(address);
        }
        else
        {
            // Address exists, replace its property values
            loyalty.Entry(existingAddress).CurrentValues.SetValues(address);
        }
    }

    // Remove addresses not present in the updated customer
    foreach (var address in existingCustomer.Address)
    {
        if (!customer.Address.Any(a => a.AddressId == address.AddressId))
        {
            loyalty.Remove(address);
        }
    }
}

loyalty.SaveChanges();