Giannis Paraskevopoulos Giannis Paraskevopoulos - 1 year ago 71
C# Question

Entity Framework 5 doesn't update Many to Many relation

I have the following tables in an



code varchar(18)
name varchar(80)

This table has more columns but I have removed them here for simplicity.


code int
name varchar(50)


AccommodationCode varchar(18)
AttributeCode int

As you may get, AccommodationAttributes describes the many to many relationship between Accommodations and Attributes.

I have created my model (EF5) using database first, and it has created two classes linked with a navigation property.

All this seems correct.

What I am trying to do is add values in the db, but though I am able to add Accommodations and Attributes, I don't seem to be able to make it add the corresponding values in the AccommodationAttributes table.

I am reading from an XML file.


Below is the code I am using exactly as it is:

public static void UpdateAccommodation(string file)
InterHomeEntities ih = new InterHomeEntities();
Stopwatch sw = new Stopwatch();
ih.Configuration.AutoDetectChangesEnabled = false;
ih.Configuration.ValidateOnSaveEnabled = false;
ih.Configuration.LazyLoadingEnabled = false;

XElement xe = XElement.Load(file);
DateTime DayToProcess = DateTime.Now.AddDays(Properties.Settings.Default.InterHome_DaysToProcess);

var Attributes = xe.XPathSelectElements("//attribute").Select(x => x.Value).Distinct();
foreach (var attribute in Attributes)
Attribute at = ih.Attributes.Where(x => == attribute).SingleOrDefault();
bool newEntry = at == null ? true : false;
at = newEntry ? new Attribute { name = attribute } : at;
ih.Entry(at).State = newEntry ? System.Data.EntityState.Added : System.Data.EntityState.Modified;
var Accommodations = from c in xe.Elements("accommodation") select c;
int AccomodationCount = Accommodations.Count();
int AccomodationIndex = 0;
foreach (var accommodation in Accommodations)
var AccCode = accommodation.Element("code").Value;
Accommodation a = ih.Accommodations.Where(x=>x.code == AccCode).SingleOrDefault();
bool newAccommodation = a == null ? true : false;
a = !newAccommodation ? a :
new Accommodation
code = accommodation.Element("code") == null ? null : accommodation.Element("code").Value,
name = accommodation.Element("name") == null ? null : accommodation.Element("name").Value,
country = accommodation.Element("country") == null ? null : accommodation.Element("country").Value,
region = accommodation.Element("region") == null ? null : accommodation.Element("region").Value,
place = accommodation.Element("place") == null ? null : accommodation.Element("place").Value,
zip = accommodation.Element("zip") == null ? null : accommodation.Element("zip").Value,
type = accommodation.Element("type") == null ? null : accommodation.Element("type").Value,
quality = accommodation.Element("quality") == null ? (byte?)null : Convert.ToByte(accommodation.Element("quality").Value),
details = accommodation.Element("details") == null ? null : accommodation.Element("details").Value,
brand = accommodation.Element("brand") == null ? null : accommodation.Element("brand").Value,
pax = accommodation.Element("pax") == null ? (double?)null : Convert.ToDouble(accommodation.Element("pax").Value),
sqm = accommodation.Element("sqm") == null ? (double?)null : Convert.ToDouble(accommodation.Element("sqm").Value),
floor = accommodation.Element("floor") == null ? (double?)null : Convert.ToDouble(accommodation.Element("floor").Value),
rooms = accommodation.Element("rooms") == null ? (double?)null : Convert.ToDouble(accommodation.Element("rooms").Value),
bedrooms = accommodation.Element("bedrooms") == null ? (double?)null : Convert.ToDouble(accommodation.Element("bedrooms").Value),
toilets = accommodation.Element("toilets") == null ? (double?)null : Convert.ToDouble(accommodation.Element("toilets").Value),
bathrooms = accommodation.Element("bathrooms") == null ? (double?)null : Convert.ToDouble(accommodation.Element("bathrooms").Value),
lat = accommodation.Element("geodata") == null || accommodation.Element("geodata").Element("lat") == null ? null : accommodation.Element("geodata").Element("lat").Value,
lng = accommodation.Element("geodata") == null || accommodation.Element("geodata").Element("lng") == null ? null : accommodation.Element("geodata").Element("lng").Value,
LastUpdated = DateTime.Now
foreach (var attribute in accommodation.Elements("attributes").Elements("attribute").Select(x=>x.Value))
Attribute at = ih.Attributes.Where(x => == attribute).SingleOrDefault();
if (newAccommodation)
ih.Entry(ih.Accommodations.Where(x => x.code == a.code).SingleOrDefault()).CurrentValues.SetValues(a);
ih.Entry(ih.Accommodations.Where(x => x.code == a.code).SingleOrDefault()).State = System.Data.EntityState.Modified;

catch (Exception ex)

After running this code I run the following in SQL:

select COUNT(*) from Accommodations
select COUNT(*)from Attributes
select COUNT(*)from AccommodationAttributes

But though I see entries in the two tables, the link table comes with 0 rows.

I have tried other variations, like attaching the objects to the context, or implicitly specifying that it is a modified object.

By the time that this code will run I am sure that the Attributes are already inserted in the db, but the Accommodation is either an Insert or Update.


After further investigation, it seems that it works when I add a new Accommodation, but it fails when the Accommodation is already in the db and I just add new attributes. In my case in the process of developing I had first added the Accommodation and in a later step of development I created the process to import attributes. So I need to find a way to update the relationship when both accommodation and attribute are already in the db.
I am eager to hear your thoughts,


Answer Source

make sure you set the following:

In table Accomodation PK is code.

In table Attrrrribute PK is code.

In table AccomodationAtrribute PK is AccomodationCode+AttributeCode.

In table AccomodationAttribute set a foriegn key of AccomondationCode to colum code in table Accomodation.

In table AccomodationAttribute set a foriegn key of AttributeCode to colum code in table Attribute.

also for the linking table to be filled you need to link an attribute instance to an accomodation or vice versa in the code. somtheing like:

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download