Add FK constraint checking to the in-memory provider

[htuomola]

Hi, my apologies if this is already covered by any other open/closed issue but I couldn't find any.
It seems that the In-memory provider is not enforcing required foreign key properties and thus acts differently from SQL Server provider. I'm using build 7.0.0-beta5-13171 (latest nightly).

I suppose this is a known issue? Is there a plan/target for fixing it? I believe unit testing is a big use case for using the in-memory provider but with this kind of limitations, its value is diminished (for now).

Here's sample code to demonstrate this:

[TestMethod]
[ExpectedException(typeof(DbUpdateException))]
public void AddOnlyChild_SQL()
{
    /* This method throws an exception (as it should): 
     * Microsoft.Data.Entity.Update.DbUpdateException: An error occurred while updating the entries. 
     * See the inner exception for details. 
     * ---> System.Data.SqlClient.SqlException: 
     * The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Child_Parent_ParentId". 
     * The conflict occurred in database "EF7Test", table "dbo.Parent", column 'Id'.
     */
    using (var ctx = new SimpleContext())
    {
        AddOnlyChild(ctx);
    }
}

[TestMethod]
[ExpectedException(typeof(DbUpdateException))]
public void AddOnlyChild_InMemory()
{
    // this doesn't throw an exception, thus failing the test 
    var optionsBuilder = new DbContextOptionsBuilder();
    optionsBuilder.UseInMemoryStore();
    using (var ctx = new SimpleContext(optionsBuilder.Options))
    {
        AddOnlyChild(ctx);
    }
}

private static void AddOnlyChild(SimpleContext ctx)
{
    var child = new Child {Name = "Dave", Id = Guid.NewGuid()};
    ctx.Children.Add(child);
    ctx.SaveChanges();
}

Context & model:

public class SimpleContext : DbContext
{
    public SimpleContext() { }

    public SimpleContext(DbContextOptions options) : base(options)  { }

    public DbSet<Parent> Parents { get; set; }
    public DbSet<Child> Children { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
            optionsBuilder.UseSqlServer(@"Server=(localdb)\v11.0;Database=EF7Test;Trusted_Connection=True;MultipleActiveResultSets=true");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Child>().Table("Child");

        modelBuilder.Entity<Child>().Key(r => r.Id);

        modelBuilder.Entity<Child>()
            .Property(r => r.Id)
            .GenerateValueOnAdd(true);

        modelBuilder.Entity<Child>()
            .Reference<Parent>(rs => rs.Parent)
            .InverseCollection(r => r.Children)
            .ForeignKey(rs => rs.ParentId)
            .Required();

        // parent model

        modelBuilder.Entity<Parent>().Table("Parent");

        modelBuilder.Entity<Parent>().Key(r => r.Id);

        modelBuilder.Entity<Parent>()
            .Property(r => r.Id)
            .GenerateValueOnAdd(true);
    }
}

public class Parent
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public List<Child> Children { get; set; }
}

public class Child
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public Guid ParentId { get; set; }
    public Parent Parent { get; set; }
}

[rowanmiller]

Triage Discussed (at length 😄) and ultimately decided that enforcing relationships in the data store is a relational thing. Many other data stores are not going to enforce this (i.e. Azure Table Storage). We could build the InMemory store to emulate relational database behavior but we ultimately decided this is not what we want to do. Ultimately you need to test your application against a real database to some extent since even different relational databases behave differently. If you want something closer to a relational database then using SQLite in in-memory mode is an option.

[ErikEJ]

@rowanmiller and remember that the sqlite engine does not enforce foreign keys unless you run a PRAGMA command

[pauldalyii]

What about testing for unique indexes? Now that unique indexes have recently been fixed, I tried writing a unit test to validate this using the InMemoryProvider. The InMemoryProvider doesn't appear to throw an exception when I try to violate the contraint. Is the recommendation for enforcing unique constraints are in place also to use SqlLite?

[ajcvickers]

Based on the semantics of the FK as discussed in #9470 and that these semantics now matter for querying data from the in-memory provider, we think it would now be useful to have the in-memory provider check cosntraints--that is, if an FK has a non-null value, then there must be a principal key with that value.

[apis3445]

I think that would be if the in memory add the validations for unique index and foreign keys, maybe in a memory with validations and other in memory without validations or maybe with a property for in the memory

[SimonCropp]

this is my workaround for validating indexes

public static class IndexValidator
{
    public static void ValidateIndexes(this DbContext context)
    {
        foreach (var entry in context.ChangeTracker.Entries().GroupBy(x=>x.Metadata))
        {
            foreach (var index in entry.Key.UniqueIndices())
            {
                index.ValidateEntities(entry.Select(x => x.Entity));
            }
        }
    }

    static void ValidateEntities(this IIndex index, IEnumerable<object> entities)
    {
        var dictionary = new Dictionary<int, List<object>>();
        foreach (var entity in entities)
        {
            var valueLookup = index.GetProperties(entity).ToList();
            var values = valueLookup.Select(x => x.value).ToList();
            var hash = values.GetHash();

            if (!dictionary.ContainsKey(hash))
            {
                dictionary[hash] = values;
                continue;
            }

            var builder = new StringBuilder($"Conflicting values for unique index. Entity: {entity.GetType().FullName},\r\nIndex Properties:\r\n");
            foreach (var (name, value) in valueLookup)
            {
                builder.AppendLine($"    {name}='{value}'");
            }
            throw new Exception(builder.ToString());
        }
    }

    static IEnumerable<IIndex> UniqueIndices(this IEntityType entityType)
    {
        return entityType.GetIndexes()
            .Where(x => x.IsUnique);
    }

    static int GetHash(this IEnumerable<object> values)
    {
        return values.Where(x => x != null)
            .Sum(x => x.GetHashCode());
    }

    static IEnumerable<(string name, object value)> GetProperties(this IIndex index, object entity)
    {
        return index.Properties
            .Select(property => property.PropertyInfo)
            .Select(info => (info.Name, info.GetValue(entity)));
    }
}

[SimonCropp]

so i needed to work around this enough times that i wrapped the workaround in a nuget https://github.com/SimonCropp/EfCore.InMemoryHelpers

[cgountanis]

Is this solved?

I am using ModelBuilder ApplyConfiguration and one of the options for example:

builder.HasIndex(o => o.Email).IsUnique();

InMemoryDatabase just ignores this and it is frustrating. I can insert multiple records with the same 'Email' in this case.

[ajcvickers]

We recommend against using the in-memory provider for testing--see Testing EF Core Applications. While we have no plans to remove the in-memory provider, we will not be adding any new features to this provider because we believe valuable development time is better spent in other areas. When feasible, we plan to still fix regressions in existing behavior.