Entity Framework Core - 根据通过 Web API 传入的实体的 JSON 表示更新具有子项的实体的有效方法 [英] Entity Framework Core - efficient way to update Entity that has children based on JSON representation of entity being passed in via Web API

问题描述

我正在编写一个 .NET Core Web API，它由 Entity Framework Core 提供支持，并在下面有一个 PostgreSQL 数据库 (AWS Aurora).我已经能够学习并成功地使用 EF Core 进行插入和查询数据，这很棒，但开始研究现有实体的更新，我不清楚实现我所追求的最有效方法.由于我的 Database First 脚手架练习，我拥有了我的数据库实体.我有一个类似于下面的实体(简化).

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

一个客户，可能有多个地址和/或多个 ContactInformation 记录.我希望我的 Web API 传入一个 JSON 负载，该负载可以转换为包含所有相关信息的客户.我有一个方法可以将我的 CustomerPayload 转换为可以添加到数据库中的 Customer:

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; 
    }

我希望能够通过 McaId 查找现有客户(这很好，我可以通过)

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(); 

但随后能够使用 Customer 中包含的任何属性的任何不同值巧妙地更新该 Customer 和关联表 - 或 - 其相关实体.所以，在伪代码中:

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?

已尝试更新.代码如下:

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();
    }
}

我更改的只是客户的姓氏.没有发生错误，但是数据库中的记录没有更新.我有以下设置，所以希望在我的日志中看到生成的 SQL 语句，但没有生成任何语句:

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 使用提供程序Npgsql.EntityFrameworkCore.PostgreSQL"初始化loyaltyContext"，选项为:SensitiveDataLoggingEnabled

这是我在日志中列出的唯一条目.

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

推荐答案

您正在处理断开连接的实体图.这是文档部分 您可能会感兴趣.

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

示例:

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();

这篇关于Entity Framework Core - 根据通过 Web API 传入的实体的 JSON 表示更新具有子项的实体的有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！