0

I have these two tables

public partial class Items
{
    public Items()
    {
        this.Variables = new HashSet<Variable>();
    }

    public int Id { get; set; }
    public string name {get; set};
    //other columns 

    public virtual ICollection<Variable> Variables { get; set; }
  }
}

And

public partial class Variable
{
    public Variable()
    {
        this.Items= new HashSet<Items>();
    }

    public int Id { get; set; }
    public string name {get; set};
    //other stuff

    public virtual ICollection<Items> Items{ get; set; }
 }
}

tr These were both created automatically from a database first Entity Framework (update model from database in the edmx file). I need to be able to update the junction table that is created from 

public virtual ICollection<Template> Templates { get; set; }
public virtual ICollection<Variable> Variables { get; set; }

The junction table is just two columns TemplateId and VariableID which are both the FK to the respective tables.These need to match on the name of Item and Variable. which currently i am able to accomplish somewhat with (I dont get ID from the Item Table):

foreach (var tempItem in db.Items)
           {

               var item = db.Variables.FirstOrDefault(x => x.Name tempItem.Name);
                db.Variables.Add(item);

            }
            db.SaveChanges();

edit: The sql for what i am trying to accomplish is:

select Top 1000 V.ID, I.ID
FROM [PageflexWeb].[dbo].[Variables] as V, [PageflexWeb].[dbo].[Items] as I
Where V.Name = I.Name

Which works fine, but i need a way to save this to the junction table dbo.ItemVariables

Any help would be greatly appreciated.

I have read through all the documentation on msdn and stack but I am unable to find the answer that I am looking for.

tscLegend
  • 76
  • 11
  • `context.SaveChanges();` – Robert McKee Apr 28 '15 at 17:44
  • db.Variables.Add(item); is giving an error saying that Iqueryable is not assignable to parameter type Model.Item. then i change to Variable item = context.Variables.Where(x => x.Name == templateItem.Name); gives an error to the tune of cannot convert source type. This would be before context.savechanges – tscLegend Apr 28 '15 at 17:47
  • Is db a DbContext or is it an `Items`? If it is an `Items`, then you just need to add it to the Variables collection, and call SaveChanges on your DbContext (assuming you have changetracking on), and db is currently attached (Loaded from database). – Robert McKee Apr 28 '15 at 17:49
  • Sorry, your code has a bug. Change `var item = context.Variables.Where(x => x.Name == templateItem.Name);` to `var item = context.Variables.First(x => x.Name == templateItem.Name)` – Robert McKee Apr 28 '15 at 17:56
  • db is set to the modelEntities, where model is the name of my model that entity framework created when used the ADO.NET Entity Data Model? context is also set the same – tscLegend Apr 28 '15 at 18:06
  • Then you only need one. Get rid of the other. Load an Item from the database, load a variable from the database. Add the variable to the item, and call savechanges. – Robert McKee Apr 28 '15 at 18:21
  • That all works fine - but it is only saving the to one of the tables, and not creating the relationship to the junction table – tscLegend Apr 29 '15 at 13:37
  • If both your Item and Variable objects are created (or attached) to the same context, and you add one to the others navigation collection, it should save the junction entry as well. Unless you've disabled it somehow (disabling ChangeTracking, or the like). – Robert McKee Apr 29 '15 at 14:58

1 Answers1

0
Db.Templates.Add(newItem);
foreach (var record in variable.Where(record => record.Name == newItem.Name))
    {
         record.Templates.Add(newItem);
    }

}
Db.SaveChanges();

so i needed to add the newItem to the item table - then load the records and move through them building the relationship. Unless there is another way with automatic updating (changeTracking?) I am going to mark this as the answer. Thanks @Robert McKee

tscLegend
  • 76
  • 11