How to Seed a table with Index Unique Data Annotation based on 2 columns with one nullable

Question

I want to create a table where data uniqueness is based on multiple columns (2 or 3) but one of them can be null. For example:

FRUIT   WEIGHT  UNIT
Apple
Apple   1       Kg
Apple   2       Kg
Orange
Orange  1       Kg
Orange  2       Kg

will all be considered as unique entries. Can it be done with EF 6.1 Data Annotations? I beleive I achieved that like this:

[Required]
[Index("UniqueIndx", 1)]
public string Name { get; set; }
[Index("UniqueIndx", 2)]
public float? Weight { get; set; }
[Index("UniqueIndx", 3, IsUnique = true)]
public FormulUnit? Unit { get; set; }

which produces:

public override void Up()
{
    AlterColumn("MyDb.Fruits", "Weight", c => c.Single());
    AlterColumn("MyDb.Fruits", "Unit", c => c.Int());
    CreateIndex("MyDb.Fruits", new[] { "Name", "Weight", "Unit" }, 
                 unique: true, name: "UniqueIndx");
}

From my understanding of Up method created by migration is that uniqueness is based on all 3 columns, not just last one I wrote that annotation to. This is ok for me, this is actually what I want.

I still have the problem of seeding that table. I'm getting errors on AddOrUpdate method like:

System.InvalidOperationException: The binary operator Equal is not defined for the types 'System.Nullable`1[System.Single]' and 'System.Single'.

for:

context.Fruits.AddOrUpdate(
    p => new {p.Name, p.Weight, p.Unit}, fr1, fr2, fr3
);

Am I missing something? Thanks

http://stackoverflow.com/questions/12570634/equal-is-not-defined-between-type-nullableint32-and-int32 ? — Eugene Podskal, Aug 09 '14 at 09:35
Primary key for Fruit is in a base class that Fruit and other entities inherit from. — Bogac, Aug 09 '14 at 10:16

score 1 · Answer 1 · answered Aug 09 '14 at 10:15

I just debugged the EF source code, and the code that throws the error is inside AddOrUpdate.

Here is part of the code excerpt that causes error.

var matchExpression
    = identifyingProperties.Select(
        pi => Expression.Equal(
            Expression.Property(parameter, pi.Single()),
            Expression.Constant(pi.Last().GetValue(entity, null))))

Or you can replicate the error by doing this.

var fr1 = new Fruit { Weight = 1 };
var epe = Expression.Property(Expression.Constant(fr1), "Weight");
var ec = Expression.Constant(fr1.Weight);
var ee = Expression.Equal(epe, ec);

I'm not sure why Float? is not acceptable by Expression.Equal, perhaps somebody else can explain.

But, if you can just use Add or manually checking whether to add or update, instead of AddOrUpdate, that will work.

I was thinking I was wrong trying AddOrUpdate, but I ran Into another problem later by not using AddOrUpdate. I'm very troubled now. I'll just cancel all Index annotations from my entity. — Bogac, Aug 11 '14 at 18:27

Bogac · Answer 2 · 2014-08-12T10:31:31.103

UPDATE 2:

Below is what I thought my mistake and gave a possible sollution. But then again I was wrong. Although below code adds new records to databse with index based on 3 criteria, because it creates new objects of anonymous type during process, you loose foreign key relations. If you have a parent class, let's call it Grocery which holds many Fruits, seed method will not update those relations with given code below.

Back to work agin...

My logic is wrong.

If we have a unique index based on multiple criteria, how one can tell which record to update? A record with one different criteria out of 3 might be very well a new record, or maybe it's an old one to be updated but that is not something compiler can tell.

Each record need to be added manualy.

Thank you Yuliam.

UPDATE: For those of you who fall into same dilema, here is how I solved this situation (with help of other Stackoverflow posts)

First, you must know that Fruit entity has a base entity with ID, so that's why you don't see it here. Then to understand better here are steps needed to take:

Get your DbSet from context into an anonymous type list, stripped from properties that are not concern for comparison.
Put that anonymous type list into a strongly typed list (of your entity type, in this example: Fruit)
Make a new list where you only select from your seed objects that don't exist in database.
Add all of those new objects to context (and then context.save() )

var SeedFruits = new List { fr1, fr2, fr3 };

var result = from a in context.Fruits select new { a.Name, a.Weight, a.Unit };

List DBFruitsList = result.AsEnumerable() .Select(o => new Fruit { Name = o.Name, Weight = o.Weight, Unit = o.Unit }).ToList();

var UniqueFruitsList = from ai in SeedFruits where !DBFruitsList.Any(x => x.Name == ai.Name && x.Weight == ai.Weight && x.Unit == ai.Unit) select ai;

foreach (Fruit fruittoupdate in UniqueFruitsList) { context.Fruits.Add(fruittoupdate); }

How to Seed a table with Index Unique Data Annotation based on 2 columns with one nullable

2 Answers2