CODEWITHSUNDEEP
linqentity-frameworkcode-firsteager-loading
skella
skella

Reputation: 15

Many-to-many relationship troubles

I am hoping to get some help. I have a code first solution, with two classes Styles and QuantityBreaks. They have a many-to-many relationship which I believe I have correct in their class definitions listed below. I seed the database with some data to both tables, the many-to-many table is created but there's no data in it as I'm not sure how to add data to it yet. I can do it manually but that gets erased when I build.

So I guess my first question is how do I seed the many-to-many table?

The second question is how do I query the QuantityBreaks table where the StyleID is say 1. I can't do a join as there isn't a foreign key in either table or do I have to do a join on the three tables? I did try using the .include statement but found that very frustrating and couldn't get that to work. I thought this should be rather simple.

QuantityBreak Class with virtual ICollection to Style:

public class QuantityBreak
{
    [ScaffoldColumn(false)]
    public int QuantityBreakID { get; set; }

    [Required, Display(Name = "Press Type")]
    public PressType PressType { get; set; }

    [Display(Name = "Quantity")]
    public double? Quantity { get; set; }

    //Navigation Properties
    public virtual ICollection<Product> Products { get; set; }
    public virtual ICollection<Style> Styles { get; set; }
}

Style Class with virtual ICollection to QuantityBreaks:

public class Style
{
    [ScaffoldColumn(false)]
    public int StyleID { get; set; }

    [Required, StringLength(60), Display(Name = "Name")]
    public string StyleName { get; set; }

    [Required, StringLength(6), Display(Name = "Short Code (6)")]
    public string StyleCode { get; set; }

    [Display(Name = "Style Description")]
    public string Description { get; set; }

    public string ImagePath { get; set; }

    [Display(Name = "Image Width")]
    public int ImageWidth { get; set; }

    [Display(Name = "Image Height")]
    public int ImageHeight { get; set; }

    public int CategoryID { get; set; }

    public int SizeID { get; set; }

    //Navigation Properties
    public virtual Category Category { get; set; }
    public virtual Size Size { get; set; }
    public virtual ICollection<Stock> Stocks { get; set; }
    public virtual ICollection<QuantityBreak> QuantityBreaks { get; set; }
    public virtual ICollection<FinishConstraint> FinishConstraints { get; set; }
}

Upvotes: 1

Views: 155

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109079

how do I seed the many-to-many table?

In a many-to-many association you have to add items to a collection member, as in

var quantityBreak = db.QuantityBreaks.Create();
// .. set properties.
db.QuantityBreaks.AddOrUpdate(quantityBreak);

var style = db.Styles.Create();
// .. set properties.

// Add if StyleName is new, otherwise update
db.Styles.AddOrUpdate(s => s.StyleName, style);

// Make sure quantityBreak is attached 
// (AddOrUpdate does not attach exising entities)
if (db.Entry(quantityBreak).State != EntityState.Added)
    quantityBreak = db.QuantityBreaks.Find(quantityBreak.QuantityBreakID);

quantityBreak.Styles.Add(style)
d.SaveChanges();

As you see, a little trickery is necessary to make sure that quantityBreak entities that already exist in the database are attached to the context. Somehow, setting their state to Unchanged (which is the standard way to attach entities) throws a duplicate key exception, even though the entities aren't attached at that point.

how do I query the QuantityBreaks table where the StyleID is say 1

I guess this means querying QuantityBreaks having at least one of their styles with StyleID == 1:

db.QuantityBreaks.Where(q => q.Styles.Any(s => s.StyleID == 1))

db is a context instance in these code snippets.

Upvotes: 1

Related Questions