Entity Framework 5 doesn't update Many to Many relation

Question

I have the following tables in an SQL2008 database:

Accommodation

code    varchar(18)
name    varchar(80)

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

Attributes

code    int
name    varchar(50)

AccommodationAttributes

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.

EDIT

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 => x.name == attribute).SingleOrDefault();
        bool newEntry = at == null ? true : false;
        at = newEntry ? new Attribute { name = attribute } : at;
        ih.Attributes.Attach(at);
        ih.Entry(at).State = newEntry ? System.Data.EntityState.Added : System.Data.EntityState.Modified;
        ih.SaveChanges();
    }
    var Accommodations = from c in xe.Elements("accommodation") select c;
    int AccomodationCount = Accommodations.Count();
    int AccomodationIndex = 0;
    foreach (var accommodation in Accommodations)
    {
        AccomodationIndex++;
        var AccCode = accommodation.Element("code").Value;
            try
            {
                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 => x.name == attribute).SingleOrDefault();
                    a.Attributes.Add(at);
                }
                if (newAccommodation)
                {
                    ih.Accommodations.Add(a);
                }
                else
                {
                    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;
                }

                ih.SaveChanges();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
    ih.SaveChanges();
}

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.

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,

Giannis

Have you placed foreign key constraints in the DB? if not - that is the cause of the problem. If you did - please add them to your posted question. — omer schleifer, Jun 19 '13 at 06:42
I will certainly check for the constraints, thanks for the suggestion. I think i have them, but i have done some changes in the db during the design, so i might have dropped them for some reason. — Giannis Paraskevopoulos, Jun 19 '13 at 08:25
Hi,Please check http://img819.imageshack.us/img819/8233/vxr.png and http://img543.imageshack.us/img543/3974/s8t.png where i have the images of the relations. — Giannis Paraskevopoulos, Jun 19 '13 at 18:38

omer schleifer · Accepted Answer · 2013-06-19T19:30:25.713

0

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:

accomodation.Attrbutes.Add(attribute);

edited Jun 19 '13 at 19:30

answered Jun 19 '13 at 19:08

omer schleifer

3,897
5
31
42

It is not what your image shows. please correct your post and i will try to offer another suggestion – omer schleifer Jun 19 '13 at 19:23
Maybe i am missing it in the code. but where are you linking the accomodation object instance to an attribute instance or vice versa? – omer schleifer Jun 19 '13 at 19:26
Sorry, it seems that i had included the same image. Here is the second foreign ket to the Attribute table: http://img824.imageshack.us/img824/307/1az.png – Giannis Paraskevopoulos Jun 19 '13 at 19:29
You are right, i missed that in the code: a.Attributes.Add(at); I am selecting the Accommodation and store it in a, then for each attribute at i call a.Attributes.Add(at). – Giannis Paraskevopoulos Jun 19 '13 at 19:32
Sorry, i thought i missed to include it in my post, though it was there... I still have the problem ,though i appreciate any insight. – Giannis Paraskevopoulos Jun 19 '13 at 19:36
It is quite hard to help if you don't show correct code, correct schema etc.. please improve your question so that will make it easier to identify the problem. thanks – omer schleifer Jun 19 '13 at 19:40
Please see this screenshot which has the relationships in a view (i just added the tables in the View designer in SSMS, the column definitions of all the three tables including the primary keys and a screen of the model. If you need further info, i can provide: http://img195.imageshack.us/img195/7303/lueu.png – Giannis Paraskevopoulos Jun 19 '13 at 19:53
And the code i provided is as is from what i had, I have only removed the fetching of the xml from an ftp site and the load to xe variable, but i think these are out of scope. – Giannis Paraskevopoulos Jun 19 '13 at 19:55
I also don't see where you actually add new entities to the context. if you add the entites and before calling saveChanges query the context you will not get them. see: http://stackoverflow.com/questions/6426053/why-does-not-ef-queries-return-unsaved-objects-entities – omer schleifer Jun 19 '13 at 21:22
First of all, thanks for any suggestions right or wrong... I have updated the code snippet to be exactly as is in my program (i have only removed some irrelevant comments). Please be patient with me as it is my first time dealing with EF and i am alone not belonging to a team, so whatever written is for personal research. If you find anything wrong or out of place please advice accordingly. Again many thanks for any help... – Giannis Paraskevopoulos Jun 20 '13 at 09:34
You should not use attach to add new objects to the context. use AddObject instead. see: http://stackoverflow.com/questions/3920111/entity-framework-4-addobject-vs-attach – omer schleifer Jun 20 '13 at 09:38
I have to mention that Attach does work as i am marking it as Added in the state, and i have tested the part where i am adding attributes and it does insert them. I don't know if this is just wrong approach. I can modify it in order to check if exists and then add or attach accordingly. What do you think? But will it solve the problem? I will check. .... By the way i don't get an AddObject in the intellisence – Giannis Paraskevopoulos Jun 20 '13 at 09:53
All I can say is that I've used many times many to many realtions in EF without any problem. I have never used Attach. I don't see any reason to do so in order to add an object. good luck, I'm out of any more ideas so hope it will help – omer schleifer Jun 20 '13 at 09:56
Hi, 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. Thanks for the help. – Giannis Paraskevopoulos Jun 20 '13 at 17:49
It seems that finally i got the answer i needed. What you said was fundamentally right, so i accept your solution. I needed to remove ih.Configuration.AutoDetectChangesEnabled = false; or set to true. – Giannis Paraskevopoulos Jun 20 '13 at 18:35

Entity Framework 5 doesn't update Many to Many relation

1 Answers1