Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add FK constraint checking to the in-memory provider #2166

Open
htuomola opened this Issue May 12, 2015 · 8 comments

Comments

Projects
None yet
9 participants
@htuomola
Copy link

htuomola commented May 12, 2015

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

This comment has been minimized.

Copy link
Member

rowanmiller commented May 13, 2015

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

This comment has been minimized.

Copy link
Contributor

ErikEJ commented May 14, 2015

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

@pauldalyii

This comment has been minimized.

Copy link

pauldalyii commented Jun 2, 2015

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

This comment has been minimized.

Copy link
Member

ajcvickers commented Aug 23, 2017

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.

@ajcvickers ajcvickers reopened this Aug 23, 2017

@ajcvickers ajcvickers changed the title In-memory provider doesn't enforce required foreign keys Add FK constraint checking to the in-memory provider Aug 23, 2017

@ajcvickers ajcvickers added this to the Backlog milestone Aug 23, 2017

@apis3445

This comment has been minimized.

Copy link

apis3445 commented Oct 31, 2017

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 comment has been minimized.

Copy link
Contributor

SimonCropp commented Feb 1, 2018

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

This comment has been minimized.

Copy link
Contributor

SimonCropp commented Jun 20, 2018

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

@cgountanis

This comment has been minimized.

Copy link

cgountanis commented Jul 9, 2018

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.