0

So I have two tables, A and B. Each table is set up like so:

A: ItemAId, ItemAProperty1, ItemAProperty2, etc.
B: ItemBId, ItemBProperty1, ItemBProperty2, etc.

I have a record in table A and a record in table B. These records are in no way related to each other.

I have a third table, C set up like so:

C: ItemAId, ItemBId

I want to make a new record in table C from existing records in tables A and B. C is not available using intellisense. Everywhere I've looked, the suggestion is to build an object from A that already exists, and object(s) from B that already exist, add B to A (or vice versa), then do an add... and because the objects already exist in the database, EF will just do an update and link the objects together in the intermediate table. (From Insert/Update Many to Many Entity Framework . How do I do it?) I.E.:

/**** Rough Psuedocode ****/
 var a = context.First(a => a.Id == passedInAId);
 var b = context.First(b => b.Id == passedInBId);
 a.BProperty.Add(b);
 context.Add(a);
 context.SaveChanges()

However, in this case, the objects have no properties that relates them so this is not working.

I know I can write a stored proc to do this, but is it possible to do with EF?

Community
  • 1
  • 1
Bardicer
  • 1,405
  • 4
  • 26
  • 43
  • 2
    Is this helpful? http://www.codeproject.com/Articles/234606/Creating-a-Many-To-Many-Mapping-Using-Code-First – Jeremy Cook Jun 19 '14 at 16:33
  • If I understand correctly, you should have foreign keys set up from c to a and c to b. – tintyethan Jun 19 '14 at 16:36
  • is this database first or code first? – tintyethan Jun 19 '14 at 16:36
  • @tinty - Correct, the only commonality between A and B is that they each comprise half of a record in C. Currently it is database first. – Bardicer Jun 19 '14 at 18:31
  • when the edmx was generated, it should have recognized the relationships based on the foreign keys - did it? – tintyethan Jun 19 '14 at 19:18
  • Yes, I just didn't understand what all of the ICollections were at the bottom of the model of the object. Now I know that they're used for navigating through the database. – Bardicer Jun 19 '14 at 19:20
  • @JeremyCook - Yes, it shed a little more light on EF for me. (I've exclusively used manually created sprocs for DB operations up to this point). – Bardicer Jun 19 '14 at 19:21
  • 1
    Totally understand. Welcome to the joy and occasional pain that is EF. You may appreciate this one hour tutorial (free to anyone at the time of posting) http://pluralsight.com/training/Player?author=scott-allen&name=aspdotnet-mvc5-fundamentals-m6-ef6&mode=live&clip=0&course=aspdotnet-mvc5-fundamentals – Jeremy Cook Jun 19 '14 at 19:57

1 Answers1

0

So, as I discovered from digging around some more...

Even though ItemA and ItemB have no direct relations with each other, it appears that the fact that there is a linking table which contains the primary IDs results in an ICollection of ItemA inside the ItemB object, and vice versa. So to get it to work, really all I needed to do was, in fact, get the two objects I wanted to link, add one to the other's collection of said objects, and do an add. Actual (imitation) code of how it works is:

using ( var ctx = new Model()) 
{
   var item1 = ctx.ItemA.FirstOrDefault(i => i.Id == itemAId);
   var item2 = ctx.ItemB.FirstOrDefault(j => j.Id == itemBId);
   item1.ItemBs.Add(item2);
   ctx.SaveChanges();
}

Of course, First() would work if the existence of the objects was already verified, and the "item1.ItemBs.Add()" has "ItemBs" because of EF's annoying tendency to pluralize EVERYTHING.

Bardicer
  • 1,405
  • 4
  • 26
  • 43
  • In short, I'm a newb, and asked the question just before my poking around achieved the result I was looking for. My apologies for wasting everybody's time. – Bardicer Jun 19 '14 at 19:19