.NET MAUI+EF Core 7多对多关联表更新问题求助

问题场景 #

使用.NET MAUI + EF Core 7.0.14 + SQLite，存在三个核心表：

• Food：预存食物数据，Name字段设为唯一约束
• SavedMeal：餐食表，Name字段设为唯一约束
• FoodSelection：多对多关联表，关联Food和SavedMeal，包含Min/Max等自定义参数

核心问题：

• 创建/更新FoodSelection时，偶尔触发Food表唯一约束冲突（需求是绝不新增Food记录）
• 存在属性赋值后不保存、对象引用为空等异常
• 需要实现：新增/覆盖SavedMeal、修改关联FoodSelection参数的正确逻辑

相关实体类代码 #

Food类 #

[Index(nameof(Name), IsUnique = true)]
public class Food
{
    [Key]
    [Column(Order = 1)]
    public int Id { get; set; }

    [Required]
    [Column(Order = 2, TypeName = "TEXT COLLATE NOCASE")]
    public string Name { get; set; }

    [Column(Order = 3)]
    public string? Brand { get; set; }

    [Column(Order = 4)]
    public double Fats { get; set; }

    [Column(Order = 5)]
    public double Carbohydrates { get; set; }

    [Column(Order = 6)]
    public double Proteins { get; set; }

    public virtual List<FoodSelection> FoodSelections { get; set; }
}

SavedMeal类 #

[Index(nameof(Name), IsUnique = true)]
public class SavedMeal
{
    [Key]
    [Column(Order = 1)]
    public int Id { get; set; }

    [Required]
    [Column(Order = 2, TypeName = "TEXT COLLATE NOCASE")]
    public string Name { get; set; }

    [Column(Order = 3)]
    public int MacroNutrientRatio_MaxProteins { get; set; }

    [Column(Order = 4)]
    public int MacroNutrientRatio_MaxCarbohydrates { get; set; }

    [Column(Order = 5)]
    public int MacroNutrientRatio_MaxFats { get; set; }

    [Column(Order = 6)]
    public DateTime LastSavedDate { get; set; } = DateTime.Now;

    public virtual List<FoodSelection> FoodSelections { get; set; } = new List<FoodSelection>();
}

FoodSelection类 #

public class FoodSelection : INotifyPropertyChanged
{
    [Key]
    [Column(Order = 1)]
    public int Id { get; set; }

    [Column(Order = 2)]
    public int SavedMealId { get; set; }
    
    [ForeignKey(nameof(SavedMealId))]
    public virtual SavedMeal SavedMealItem { get; set; }

    [Column(Order = 3)]
    public int FoodId { get; set; }        

    [ForeignKey(nameof(FoodId))]
    public virtual Food FoodItem { get; set; }

    private int _min = 0;
    [Column(Order = 5)]
    public int Min
    {
        get => _min;
        set
        {
            _min = value;
            NotifyPropertyChanged(nameof(IsValid));
        }
    }

    private int _max = 0;
    [Column(Order = 6)]
    public int Max
    {
        get => _max;
        set
        {
            _max = value;
            NotifyPropertyChanged(nameof(IsValid));
        }
    }

    [NotMapped]
    public bool IsValid => Max >= Min;

    public event PropertyChangedEventHandler PropertyChanged;
    private void NotifyPropertyChanged([CallerMemberName] String propertyName = "")
    {
        PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
    }
}

DatabaseContext类 #

public class DatabaseContext : DbContext
{
    public DbSet<Food> Foods { get; set; }
    public DbSet<SavedMeal> SavedMeals { get; set; }
    public DbSet<FoodSelection> FoodSelections { get; set; }

    // 省略SQLite配置代码
}

原更新逻辑代码 #

private async Task SaveDataBase()
{
    bool savedChangesSuccessfully = false;

    SavedMeal savedMeal = _databaseService.DatabaseContext.SavedMeals.Where(x => x.Name == SelectedMealName).FirstOrDefault();

    if (savedMeal != null)
    {
        _errorHandlingService.AddErrorCallback(SQLitePCL.raw.SQLITE_CONSTRAINT_UNIQUE, async (updateException) =>
        {
            // 显示提示
        });

        bool overwrite = await Application.Current.MainPage.DisplayAlert(AppResources.AppTitle, Translate(AppResources.CalculateMacros_MealAlreadyExistingOverwriteMessage, savedMeal.Name), AppResources.DialogButton_Yes, AppResources.DialogButton_No);
        if (overwrite)
        {
            UpdateSavedMealWithFoodSelections(savedMeal);

            var entity = _databaseService.DatabaseContext.Find(typeof(SavedMeal), savedMeal.Id);
            _databaseService.DatabaseContext.Entry(entity).CurrentValues.SetValues(savedMeal);

            savedChangesSuccessfully = _databaseService.SaveChanges();
            if (savedChangesSuccessfully)
            {
                // 显示提示
            }
        }
        else
        {
            return;
        }
    }
    else
    {
        var meal = new SavedMeal();
        meal.Name = SelectedMealName;
        UpdateSavedMealWithFoodSelections(meal);

        _databaseService.DatabaseContext.SavedMeals.Add(meal);
        savedChangesSuccessfully = _databaseService.SaveChanges();
        if (savedChangesSuccessfully)
        {
            // 显示成功提示
        }
    }
}

public void UpdateSavedMealWithFoodSelections(SavedMeal meal)
{
    meal.MacroNutrientRatio_MaxProteins = ProteinsMax;
    meal.MacroNutrientRatio_MaxFats = FatsMax;
    meal.MacroNutrientRatio_MaxCarbohydrates = CarbohydratesMax;

    var removedFoodSelections = meal.FoodSelections.Where(fs => !SelectedFoodItems.Any(ufs => ufs.Id == fs.Id)).ToList();
    foreach (var removedFoodSelection in removedFoodSelections)
    {
        meal.FoodSelections.Remove(removedFoodSelection);
    }

    foreach (var foodSelection in SelectedFoodItems)
    {
        var existingFoodSelection = meal.FoodSelections.FirstOrDefault(fs => fs.Id == foodSelection.Id);

        if (existingFoodSelection != null)
        {
            var existingFoodItem = _databaseService.DatabaseContext.Find(typeof(Food), existingFoodSelection.FoodItem.Id);
            if (existingFoodItem != null)
            {
                var entry = _databaseService.DatabaseContext.Entry(existingFoodItem);
                if (entry != null)
                {
                    entry.State = EntityState.Unchanged;
                }
            }

            existingFoodSelection.Min = foodSelection.Min;
            existingFoodSelection.Max = foodSelection.Max;
            existingFoodSelection.OptimalAmount = foodSelection.OptimalAmount;
        }
        else
        {
            var existingFoodItem = _databaseService.DatabaseContext.Find(typeof(Food), foodSelection.FoodItem.Id);
            if (existingFoodItem != null)
            {
                var entry = _databaseService.DatabaseContext.Entry(existingFoodItem);
                if (entry != null)
                {
                    entry.State = EntityState.Unchanged;
                }

                var newFoodSelection = new FoodSelection();
                newFoodSelection.FoodId = ((Food)existingFoodItem).Id;
                newFoodSelection.FoodItem = (Food)existingFoodItem;
                newFoodSelection.Min = foodSelection.Min;
                newFoodSelection.Max = foodSelection.Max;
                newFoodSelection.OptimalAmount = foodSelection.OptimalAmount;

                meal.FoodSelections.Add(newFoodSelection);
            }
        }
    }

    meal.LastSavedDate = DateTime.Now;
}

已尝试的修改 #

• 将所有导航属性改为virtual
• 移除FoodSelection中自动设置外键ID的逻辑
• 修改关联表删除逻辑，直接调用上下文Remove方法

修复方案 #

问题根源 #

1. Food实体被误标记为新增：UI传递的FoodItem脱离上下文，EF将其识别为Added状态，触发唯一约束冲突
2. 集合修改未同步上下文：直接移除集合元素未标记为Deleted，导致更新不生效
3. 状态管理冗余：重复查找实体并手动设置状态，引发状态冲突

优化后的核心逻辑 #

重构SaveDatabase方法

private async Task SaveDatabase()
{
    var savedMeal = _databaseService.DatabaseContext.SavedMeals
        .Include(m => m.FoodSelections) // 预加载关联数据
        .FirstOrDefault(x => x.Name == SelectedMealName);

    if (savedMeal != null)
    {
        bool overwrite = await Application.Current.MainPage.DisplayAlert(
            AppResources.AppTitle, 
            Translate(AppResources.CalculateMacros_MealAlreadyExistingOverwriteMessage, savedMeal.Name), 
            AppResources.DialogButton_Yes, 
            AppResources.DialogButton_No);
        
        if (!overwrite) return;

        UpdateSavedMealWithFoodSelections(savedMeal);
    }
    else
    {
        savedMeal = new SavedMeal
        {
            Name = SelectedMealName,
            FoodSelections = new List<FoodSelection>()
        };
        UpdateSavedMealWithFoodSelections(savedMeal);
        _databaseService.DatabaseContext.SavedMeals.Add(savedMeal);
    }

    try
    {
        var savedChanges = await _databaseService.DatabaseContext.SaveChangesAsync();
        if (savedChanges > 0)
        {
            // 显示成功提示
        }
    }
    catch (DbUpdateException ex) when (ex.InnerException is SQLiteException sqliteEx 
                                      && sqliteEx.SqliteErrorCode == SQLitePCL.raw.SQLITE_CONSTRAINT_UNIQUE)
    {
        // 显示唯一约束冲突提示
    }
}

重构UpdateSavedMealWithFoodSelections方法

public void UpdateSavedMealWithFoodSelections(SavedMeal meal)
{
    // 更新餐食基础参数
    meal.MacroNutrientRatio_MaxProteins = ProteinsMax;
    meal.MacroNutrientRatio_MaxFats = FatsMax;
    meal.MacroNutrientRatio_MaxCarbohydrates = CarbohydratesMax;
    meal.LastSavedDate = DateTime.Now;

    // 1. 移除不再选中的FoodSelection
    var selectedIds = SelectedFoodItems.Select(fs => fs.Id).ToList();
    var selectionsToRemove = meal.FoodSelections
        .Where(fs => !selectedIds.Contains(fs.Id))
        .ToList();
    
    foreach (var selection in selectionsToRemove)
    {
        _databaseService.DatabaseContext.FoodSelections.Remove(selection);
    }

    // 2. 更新或新增选中的FoodSelection
    foreach (var uiSelection in SelectedFoodItems)
    {
        var existingSelection = meal.FoodSelections
            .FirstOrDefault(fs => fs.Id == uiSelection.Id);

        if (existingSelection != null)
        {
            // 更新现有选中项参数
            existingSelection.Min = uiSelection.Min;
            existingSelection.Max = uiSelection.Max;
            existingSelection.OptimalAmount = uiSelection.OptimalAmount;
        }
        else
        {
            // 确保Food实体被上下文跟踪（避免误标记为新增）
            var foodInContext = _databaseService.DatabaseContext.Foods
                .Find(uiSelection.FoodItem.Id);
            
            if (foodInContext == null)
            {
                // 若Food不在上下文，Attach而非Add
                _databaseService.DatabaseContext.Foods.Attach(uiSelection.FoodItem);
                foodInContext = uiSelection.FoodItem;
            }

            // 创建新关联项
            var newSelection = new FoodSelection
            {
                FoodId = foodInContext.Id,
                FoodItem = foodInContext,
                Min = uiSelection.Min,
                Max = uiSelection.Max,
                OptimalAmount = uiSelection.OptimalAmount
            };

            meal.FoodSelections.Add(newSelection);
        }
    }
}

额外注意事项 #

• 确保SelectedFoodItems中的FoodItem仅携带Id字段，或为上下文加载的实体
• 优先使用SaveChangesAsync适配MAUI异步环境
• 预加载关联集合时使用Include，避免延迟加载引发的上下文问题

内容的提问来源于stack exchange，提问作者OXO