lundi 29 juin 2015

Seeding SQL Server by Entity Framework code-first approach with many-to-many relationship

I'm using EF6 code first with ASP.NET Web API.

Suppose there are two model classes

public class RawMaterial {
    public int ID { get; set; }
    public string Name { get; set; }
}
public class Furniture {
    public int ID { get; set; }
    public string Name { get; set; }
    public virtual ICollection<RawMaterial> RawMaterials { get; set; }
}

DbContext

public class FurnitureContext : DbContext {
    public DbSet<RawMaterial> RawMaterials { get; set; }
    public DbSet<Furniture> Furnitures { get; set; }
}

And in the initializer class,

protected override void Seed (FurnitureContext context) {
    var glass = new RawMaterial { Name = "glass" };
    var wood = new RawMaterial { Name = "wood" };
    var paint = new RawMaterial { Name = "paint" };
    context.RawMaterials.AddRange(new RawMaterial[] { glass, wood, paint });

    var chair = new Furniture {
        Name = "chair",
        RawMaterials = new RawMaterial[] { wood, paint }
    };
    var coffeeTable = new Furniture {
        Name = "coffee table",
        RawMaterials = new RawMaterial[] { wood, glass }
    };
    context.Furnitures.AddRange(new Furnitures[] { chair, coffeeTable });

    context.SaveChanges();
}

I encountered a run time error complaining "an item cannot be removed from fixed size array". So clearly the program's trying to remove wood from chair before adding it to coffeeTable. So I changed the initializations to use Lists, as

var chair = new Furniture {
    Name = "chair",
    RawMaterials = new List<RawMaterial> { wood, paint }
};

After that, I could clearly see that wood was indeed removed from one of the furnitures' RawMaterials.

I also tried selecting wood from the context by

var chair = new Furniture {
    Name = "chair",
    RawMaterials = new RawMaterial[] {
        context.RawMaterials.Where(r => r.Name == wood.Name).FirstOrDefault()
    }
};

The result is still the same.

So my question is: how can I add the test data such that wood is present in both chair and coffeeTable? I am aware that this is not typically how many-to-many relations are defined, since RawMaterial does not know of Furniture. Or should I define the models the other way?

Thank you.


Edit: I check the database tables in SQL Server Object Explorer, and the SQL for RawMaterial is

CREATE TABLE [dbo].[RawMaterials] (
    [ID]           INT            IDENTITY (1, 1) NOT NULL,
    [Name]         NVARCHAR (MAX) NULL,
    [Furniture_ID] INT            NULL,
    CONSTRAINT [PK_dbo.RawMaterials] PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [FK_dbo.RawMaterials_dbo.Furnitures_Furniture_ID] FOREIGN KEY ([Furniture_ID]) REFERENCES [dbo].[Furnitures] ([ID])
);


GO
CREATE NONCLUSTERED INDEX [IX_Furniture_ID]
    ON [dbo].[RawMaterials]([Furniture_ID] ASC);

And the SQL for Furniture is

CREATE TABLE [dbo].[Furnitures] (
    [ID]   INT            IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (MAX) NULL,
    CONSTRAINT [PK_dbo.Furnitures] PRIMARY KEY CLUSTERED ([ID] ASC)
);

So basically entity framework is not creating the database the way I need. That's why I cannot add wook to both chair and coffeeTable. How should I modify the Entity Models?

Aucun commentaire:

Enregistrer un commentaire