EF Code First - references vs validation

In EF Code First, context.SaveChanges() automatically does validations.
But lazy loading can collide with validations.

In our model, a Product must have a Category.

public class Product
{
    [Key]
    public int ProductId { get; set; }
 
    [Required]
    [StringLength(40)]
    public string ProductName { get; set; }
 
    [Required]
    public virtual Category Category { get; set; }
}

Let's try this...

using (var context = new NorthwindContext())
{
    //find a specific product
    var product = context.Products.Find(147);
    //set a new name
    product.ProductName = Guid.NewGuid().ToString();
 
    //ERROR!
    context.SaveChanges();
}

Oh no, System.Data.Entity.Validation.DbEntityValidationException. "Validation failed for one or more entities." How can that be? We just loaded it from the database, it must be correct?
The exception says that Category is null. But it exists in the database.

If you inspect it in the debugger, you can see the category ... and if you continue the SaveChanges succeeds. The debugger triggered a lazy load, so it works.

context.SaveChanges() internally turns off lazy loading before validating. Which is good, because you don't want unnecessary database access. But when the reference is required, the validation doesn't recognise this is a proxy which has a categoryId but hasn't loaded it.

Solution 1- no auto validation

One solution is to turn off validation. When you are setting individual properties with values you've already validated, you do not need it here.

context.Configuration.ValidateOnSaveEnabled = false;
context.SaveChanges();

The SQL, by the way, is update [dbo].[Products] set [ProductName] = @0 where ([ProductID] = @1).
(Check it by hooking up a SQL logger, as described here)

An alternative is to cheat the model- ensure references are not Required.

But if you have a product coming back from a UI for insert or update, you need to manually validate that it always has a category.

Solution 2- ensure required references are loaded

This triggers extra database access, but you can keep the automatic validation.

//find a specific product
var product = context.Products.Find(147);
//make sure it's loaded
context.Entry(product).Reference(p => p.Category).Load();

If you load from a query, you can use an .Include(p => p.Category) which will load the product with a join to the category table, so there is only one sql statement. This will bypass the internal cache so if it was loaded the same product earlier, you can't save any data access.

var product = context.Products
    .Include(p => p.Category)
    .First(p => p.ProductId == 147);

Solution 3- Add a foreign key Id property

You can specify foreign key Id properties in addition to the instance property.

//it's not nullable so it's implicitly required
public int CategoryId { get; set; }
 
public virtual Category Category { get; set; }

Note the CategoryId is now required. The Category instance isn't. Validation can check the CategoryId, and we can set it directly without having to load the instance.

You have to map this arrangement (unless you like to see an EntityCommandCompilationException with the inner exception message being "More than one item in the metadata collection match the identity 'CategoryId'." - I didn't).
In the EntityTypeConfiguration<Product> the mapping must point to the foreign key id property.

HasRequired(x => x.Category)
    .WithMany()
    .HasForeignKey(p => p.CategoryId);

We've "denormalized" our entity model here, but it makes dealing with detached objects and viewmodels a little easier.

But won't the CategoryId and Category properties get out of step? If you set one, which gets persisted?

Let's set the foreign key id property.

//find a specific product
var product = context.Products.Find(146);
 
//it's category 4 in both
Console.WriteLine(product.Category.CategoryId);
Console.WriteLine(product.CategoryId);
 
//set the categoryId property
product.CategoryId = 1;
 
//we've just set it, so it's 1
Console.WriteLine(product.CategoryId);
//oh no, still says 4!
Console.WriteLine(product.Category.CategoryId);

context.SaveChanges() does the right thing- it saves the new value, 1, assigned to the id property.

Let's set the instance.

//find a specific product
var product = context.Products.Find(146);
 
//it's category 1 in both
Console.WriteLine(product.Category.CategoryId);
Console.WriteLine(product.CategoryId);
 
//set the category instance
product.Category = context.Categories.Find(2);
 
//we've just set it, so it's 2
Console.WriteLine(product.Category.CategoryId);
//oh no, still says 1!
Console.WriteLine(product.CategoryId);

But again, context.SaveChanges() does the right thing- it saves the new value, 2, assigned to the instance.

So it looks like persistence always works as you'd expect, but the properties get out of step. You must be careful in your workflow. This won't work...

product.CategoryId = 1;
var returnMessage = "Category updated to " + product.Category.CategoryName;

Conclusion

In most cases I'd prefer to remove validation on save with context.Configuration.ValidateOnSaveEnabled = false - as long as the values were validated downstream (perhaps in the UI validation framework).

But exposing the foreign key property is undoubtably convenient when the UI just sends you categoryId= 1 and you don't want to load that category from the database just so you can persist product.

static void