Problem with insert on many-to-many

[Daniel Plomp]

Hi all,

I have this weird problem of not getting a record inside a Join table in my database.
I have 'Participant' and 'Role' connected through 'ParticipantRole'.

My model shows only 2 tables, which is correct. (ZeroMany - ZeroMany)

When I execute the following code, nothing happens?

/// <summary>
/// Add a Participant to a specific Role
/// </summary>
/// <param name="rolename"></param>
/// <param name="email"></param>
public void AddParticipantToRole(string rolename, string email)
{
   try
   {
      Role r = context.Roles.Where(x => x.Description == rolename).FirstOrDefault();
      Participant pa = context.Participants.Where(x => x.Email == email).FirstOrDefault();
      pa.Roles.Add(r);
      context.SaveChanges();
   }
   catch (Exception ex)
   {
      throw ex;
   }
}

The table is not beeing filled.
I downloaded the latest version of OpenAccess, updated my model, checked the relations between my tables and associations between my entities, but nothing works.

What should I do?

Thanks!
Daniel

[Alexander]

Hello Daniel,

Such behavior is observed when the association is not declared as "managed". If it is managed, adding a Role object to the Participants collection or adding Participant objects to the Roles collection should have the same effect. On the other hand, when the association is not managed, adding related objects is possible in only one of the directions. So the solution in your case would be to modify the code the following way:

Role r = context.Roles.Where(x => x.Description == rolename).FirstOrDefault();
Participant pa = context.Participants.Where(x => x.Email == email).FirstOrDefault();
//pa.Roles.Add(r);
r.Participants.Add(pa);
context.SaveChanges();

A question that you might ask is that why all associations are not managed by default. Actually they were, prior to Q2, but we found out that this could lead to decreased performance when working with large databases, containing hundreds of thousands of objects participating in such relationships. That is why we decided to create the associations as not managed by default. This could be changed from the Details Editor when the association is selected. However, I just verified that this option is not set correctly for m:n associations and we will have to fix this. So the workaround at the moment is to use the code above. Hope it helps.

Regards,
Alexander
the Telerik team

[francesco]

Hi Alexander,
   i had the same problem described by Daniel and i used your workaround. Will Telerik fix this problem on the next release?

Thanks
  Francesco

[Alexander]

Hi francesco,

Yes, this will be fixed in the 2011 Q1 release which is scheduled for the middle of March.

All the best,
Alexander
the Telerik team

[Tajes]

Hello, 
I have a similar issue. I'm using Open Access Visual Designer. I have enabled manage for a many to many relation, however the relation can be set only by one way.
Taking previous example:

Role r = context.Roles.Where(x => x.Description == rolename).FirstOrDefault();
    Participant pa = context.Participants.Where(x => x.Email == email).FirstOrDefault();
    //pa.Roles.Add(r); --> don't do anything
    r.Participants.Add(pa); //--> this works
    context.SaveChanges();


    Why this behavior, if I have enabled manage in the relation?


[Clement]

I have a M:N problem. I'm using Telerik 2010 Q3 purchased by my client. I need to have a many to many relationship between Scheme and Town.
I've created a join table in database called Scheme_towns and did a reverse mapping using OA which in turn generated a scheme_town collection and other related configurations.

In Scheme and town classes I added

[Telerik.OpenAccess.FieldAlias("schemeSystemTown")]
       public IList<SchemeSystem> SchemeSystemstown
       {
           get { return schemeSystemTown; }
       }

in town class and

[Telerik.OpenAccess.FieldAlias("schemeTowns")]
        public IList<Town> SchemeTown
        {
            get { return schemeTowns; }


        }

for Scheme Class.

In the UI, when registering Schemes, I select a  pre-registered towns that belong to the scheme ( one town can be in more than one scheme) from a checked combobox. When I save, data is entered in the join table. problem is instead of saving town_id for the town that i selected, it generates a new town_id that is saved in the join table together with the id for the current scheme. It also proceeds to add this town_id into the towns table, with a null for the town name since I'm not registering the town at this point.

How do I make it to save the town_id for the selected town in join table and also not to create a new town int towns table?

[Alexander]

Hello,

I am glad to notify you that the problem has been resolved in the newly uploaded Q1 release. In order to enable the Managed option for a many to many association, just open the designer, select the association in question and from the Properties pane enable the IsManaged option for the target member.

@Clement: Let's please continue the conversation in your other thread on this topic, it seems the problem is a bit different and will need more investigation.

All the best,
Alexander
the Telerik team

[Chuck]

Hi - I am having the same problem, only updating in one direction.  This is with Q2 2011.  I have set the target member to Managed.

Also can you please describe the IsDependent property?  The documentation is not very descriptive: "Specifies that this navigation property is dependent. "

[IT-Als]

Hi Chuck (and others listening),

IsDependent is a way to tell that an object instance or a list of objects) that it is dependent on the object it is contained in.

Example (classic approach) but it is the same in the Context / Visual Designer approach:

public class Customer
{
        ......
        [Telerik.OpenAccess.Depend()]
        private PayoutOptions payoutOption;
        ......
}

So the payoutOption field is dependent on the Customer instance it is contained in. So when the Customer is disposed the instance referenced by the payoutOption field is also disposed by OpenAccess...  You can think of it as a "SQL cascading delete" kind of thing.

As mentioned earlier this is also possible to do with lists (IList<Whatever> for example) of objects...

Regards

Henrik

[Chuck]

Henrik:  Got it.  Thanks for the explanation.

[Jörg]

Hi.

I have also a problem with inserting in on man-to-many tables..

I have the following tables (see picture).

The IsManged property is set to true for both navigation properties (Sector and Door)
For tests I use this code

public void TestManToMany()
{
    using (DbContext ctx = new DbContext())
    {
        int nr = 30;

        Sector sector = new Sector()
        {
            AId = 1,
            Desc = "bla",
            Name = "Sector " + nr,
            Path = "",
            SectorId = nr,

        };

        Door door = new Door()
        {
            DoorId = nr + 1000,
            Name = "Door " + nr,
            RoomNr = nr.ToString(),
        };

        sector.Doors.Add(door);
        //door.Sectors.Add(sector);

        ctx.Add(door);
        ctx.Add(sector);
        ctx.SaveChanges();
    }
}

I get the following exception:
Sorry, but I have only a German system. There is conflict between with the foreign key FK_tblDoor_tblSector in table Sector.
This foreign key constraint defines only the association between the Sector and the DoorSector table. I have check it and the definition of the constraint is ok.

Telerik.OpenAccess.Exceptions.DataStoreException was unhandled
  Message=Insert link table row failed: Telerik.OpenAccess.RT.sql.SQLException: Die INSERT-Anweisung steht in Konflikt mit der FOREIGN KEY-Einschränkung 'FK_tblDoorSector_tblSector'. Der Konflikt trat in der lsmdb-Datenbank, Tabelle 'dbo.tblSector' auf.
Die Anweisung wurde beendet.
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeUpdate()
   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeUpdate()
   at OpenAccessRuntime.Relational.metadata.RelationalLinkCollectionField.insertUnorderedLinkTableRows(OID oid, Object[] inserted, PreparedStatement psins, Boolean batch, String sql)
Field: System.Collections.Generic.IList`1[[OpenAccessModel1.Sector, OpenAccessModel1, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]] OpenAccessModel1.Door._sectors
Instance: NewObjectOID@3 classIndex = 2 id = 1 realOID = GenericOID@408 Door colDoorId=1030
Value: NewObjectOID@2 classIndex = 0 id = 2 realOID = GenericOID@1f Sector colAId=1/colSectorId=30
INSERT INTO [tblDoorSector]([colDoorId], [colSectorId], [colAId]) VALUES (?, ?, ?)
(set event logging to all to see parameter values) Telerik.OpenAccess.RT.sql.SQLException: Die INSERT-Anweisung steht in Konflikt mit der FOREIGN KEY-Einschränkung 'FK_tblDoorSector_tblSector'. Der Konflikt trat in der lsmdb-Datenbank, Tabelle 'dbo.tblSector' auf.
Die Anweisung wurde beendet.
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeUpdate()
   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeUpdate()
   at OpenAccessRuntime.Relational.metadata.RelationalLinkCollectionField.insertUnorderedLinkTableRows(OID oid, Object[] inserted, PreparedStatement psins, Boolean batch, String sql)
  Source=Telerik.OpenAccess
  CanRetry=false
  StackTrace:
       at Telerik.OpenAccess.SPI.Backends.ThrowException(Exception e)
       at OpenAccessRuntime.ExceptionWrapper.Throw()
       at OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.handleException(Exception x)
       at OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.internalCommit(Boolean phase)
       at OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.commit()
       at OpenAccessRuntime.EnlistableObjectScope.CommitChanges()
       at Telerik.OpenAccess.OpenAccessContextBase.SaveChanges(ConcurrencyConflictsProcessingMode failureMode)
       at Telerik.OpenAccess.OpenAccessContextBase.SaveChanges()
       at OpenAccessModel1.Dal.TestManToMany() in D:\Users\multhaupj\Documents\Visual Studio 2010\Projects\OpenAccessORMTest5\OpenAccessModel1\Dal.cs:line 84
       at ConsoleApplicationTest.Program.Main(String[] args) in D:\Users\multhaupj\Documents\Visual Studio 2010\Projects\OpenAccessORMTest5\ConsoleApplicationTest\Program.cs:line 14
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: Telerik.OpenAccess.RT.sql.SQLException
       Message=Die INSERT-Anweisung steht in Konflikt mit der FOREIGN KEY-Einschränkung 'FK_tblDoorSector_tblSector'. Der Konflikt trat in der lsmdb-Datenbank, Tabelle 'dbo.tblSector' auf.
Die Anweisung wurde beendet.
       Source=Telerik.OpenAccess.Runtime
       Description=SQLState=;Die INSERT-Anweisung steht in Konflikt mit der FOREIGN KEY-Einschränkung 'FK_tblDoorSector_tblSector'. Der Konflikt trat in der lsmdb-Datenbank, Tabelle 'dbo.tblSector' auf.
Die Anweisung wurde beendet.
       ErrorCode=547
       Number=547
       StackTrace:
            at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeUpdate()
            at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeUpdate()
            at OpenAccessRuntime.Relational.metadata.RelationalLinkCollectionField.insertUnorderedLinkTableRows(OID oid, Object[] inserted, PreparedStatement psins, Boolean batch, String sql)
       InnerException: 

I tried also another way to test this.

...
ctx.Add(door);

ctx.Add(sector);

ctx.SaveChanges();

 

var sec = ctx.Sectors.SingleOrDefault(s => s.SectorId == sector.SectorId);

var doo = ctx.Doors.SingleOrDefault(d => d.DoorId == door.DoorId);

 

sec.Doors.Add(doo);

ctx.SaveChanges();
...

But with the second SaveChanges I get the same exception as above.

What could be the problem, Why I cannot insert data to the join table ???

I'm using the latest version of Open Access ORM (2012.1.214.1), Visual Studio 2010 SP1, .Net4, Win7 64bit.

Best regards
Jörg


 

[Chuck]

I am not sure that composite keys are supported, and it looks like that is what your sectors table is using.  This may be the main problem.

Secondly,

You shouldn't need to explictly add the door to the context.  It is automatically added when you add it to the sector.  Try removing:


ctx.Add(door);

Then add back in:

    door.Sectors.Add(sector);

Personally, I think this shouldn't be needed, but in reality I have experienced very inconsistant behavior unless you specify both sides of the relationship, and so it is now our practice to always include both objects in each others list.

[Jörg]

Hi Chuck,

thanks for the quick reply.

I have change the code to

sector.Doors.Add(door);
//door.Sectors.Add(sector);

//ctx.Add(door);
ctx.Add(sector);
ctx.SaveChanges();

 An I set the IsManaged value for the sector list in the door object to false, so only the Door list of the sector object is managed yet.

But I get the same exception.

regards
Jörg

[Chuck]

Thanks for the try.  After looking at your DB diagram I edited my original reply.  I think the problem is the composite key.

[IT-Als]

Hi you both,

For a consistent change tracking (that's what goes on when relationsships are managed) I experienced superb results by adding the object to the Context just after I new'ed it..

Like:

Model.Order pOrder = new Model.Order();
Context.Add(pOrder);
// OA "knows" about the Order instance now... thus can track it... and the objects you decide to add to associations
Model.OrderLine pOrderLine = new Model.OrderLine();
pOrder.Add(pOrderLine);

So if you do:

Sector sector = new Sector();
ctx.Add(sector);
Door door = new Door();
sector.Doors.Add(door);

in that order.. I am almost sure it works.. Let me know..

/ Henrik

[Chuck]

Henrik,

The following:

sector.doors.add(door)
door.sectors.add(sector)
context.add(sector)

Works for me 100% of the time, but I did not understand why the "door.sectors.add(sector)" was needed.  Now it makes perfect sense.  Until "sector" was added to the context it is still just a POCO and cannot be managed.  The reason I experienced "inconsistancy", was there were probably times I was working with existing objects that had already been added to the context.

Thanks for the clearing it up, makes perfect sense now.  I still don't think it will help Jörg, maybe this will:

http://www.telerik.com/help/openaccess-orm/object-identity-multiple-field.html

It looks like the composite key is supported, just with more work involved.

[Ralph Waldenmaier]

Hi,

First I'd like to thank Henrik for the good explanation. 

I reproduced this and got this example working with the upcoming Service Pack to be released soon:

Random random = new Random();
using (EntitiesModel ctx = new EntitiesModel())
{
    var door = new Door() { Id = random.Next(), Property1 = "SomeValue" };
    var sector = new Sector() { Id = random.Next(), Id2 = random.Next() };

    ctx.Add(door);
    door.Sectors.Add(sector);
    ctx.SaveChanges();
}

In my case the Sector class has the multiple id specified.
When the door object is added to the Context, it is sufficient to just add the sector object to the collection of sectors on the door object. The sector object will implicitly added to the Context. Hence the collections must be managed, in order to get this done.

I hope this information is useful for you. Feel free to ask if you have any other question.
Regards,
Ralph
the Telerik team

[Jörg]

Hello.

Thanks for all your replies.

I have installed the latest version of ORM and tried the suggestion from Ralph.

I have the following code:

var area1 = dataAccessLayer.GetAreaById(1);

Sector newSector = null;

if (area1 != null)
{

    newSector = new Sector()
    {
        AId = area1.AId,
        Desc = "description",
        Name = "sector name",
        Path = "",
        SectorId = nr,
    };

    Door newDoor = new Door()
    {
        DoorId = nr + 1000,
        Name = "door name" + nr,
        RoomNr = nr.ToString(),
    };


    dataAccessLayer.InsertSectorDoor(newSector, newDoor);

 
and in the data access layer I use the following code:

public void InsertSectorDoor(Sector sector, Door door)
{
    using (DbContext ctx = GetContext())
    {
        ctx.Add(sector);
        sector.Doors.Add(door);
        ctx.SaveChanges();
    }

}

I get the following exception:

The INSERT-Command is in conflict with a FOREIGN-Key constraint.


Telerik.OpenAccess.Exceptions.DataStoreException was unhandled
  Message=Insert link table row failed: Telerik.OpenAccess.RT.sql.SQLException: Die INSERT-Anweisung steht in Konflikt mit der FOREIGN KEY-Einschränkung 'FK_tblDoorSector_tblSector'. Der Konflikt trat in der lsmdb-Datenbank, Tabelle 'dbo.tblSector' auf.
Die Anweisung wurde beendet.
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeUpdate()
   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeUpdate()
   at OpenAccessRuntime.Relational.metadata.RelationalLinkCollectionField.insertUnorderedLinkTableRows(OID oid, Object[] inserted, PreparedStatement psins, Boolean batch, String sql)
Field: System.Collections.Generic.IList`1[Lsm.BusinessObjects.Model.Sector,Lsm.BusinessObjects, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]] Lsm.BusinessObjects.Model.Door._sectors
Instance: NewObjectOID@7 classIndex = 5 id = 2 realOID = GenericOID@428 Door colDoorId=1035
Value: NewObjectOID@7 classIndex = 6 id = 1 realOID = GenericOID@8 Sector colAId=1/colSectorId=35
INSERT INTO [tblDoorSector]([colDoorId], [colSectorId], [colAId]) VALUES (?, ?, ?)
(set event logging to all to see parameter values) Telerik.OpenAccess.RT.sql.SQLException: Die INSERT-Anweisung steht in Konflikt mit der FOREIGN KEY-Einschränkung 'FK_tblDoorSector_tblSector'. Der Konflikt trat in der lsmdb-Datenbank, Tabelle 'dbo.tblSector' auf.
Die Anweisung wurde beendet.
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeUpdate()
   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeUpdate()
   at OpenAccessRuntime.Relational.metadata.RelationalLinkCollectionField.insertUnorderedLinkTableRows(OID oid, Object[] inserted, PreparedStatement psins, Boolean batch, String sql)
  Source=Telerik.OpenAccess
  CanRetry=false
  StackTrace:
       at Telerik.OpenAccess.SPI.Backends.ThrowException(Exception e)
       at OpenAccessRuntime.ExceptionWrapper.Throw()
       at OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.handleException(Exception x)
       at OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.internalCommit(Boolean phase)
       at OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.commit()
       at OpenAccessRuntime.EnlistableObjectScope.CommitChanges()
       at Telerik.OpenAccess.OpenAccessContextBase.SaveChanges(ConcurrencyConflictsProcessingMode failureMode)
       at Telerik.OpenAccess.OpenAccessContextBase.SaveChanges()
       at SimonsVoss.Lsm.Dal.Dal.InsertSectorDoor(Sector sector, Door door) in D:\Users\multhaupj\Documents\Visual Studio 2010\Projects\SimonsVoss.Lsm.Example\SimonsVoss.Lsm.Dal\Dal.cs:line 132
       at SimonsVoss.Lsm.TestApp.Program.Main(String[] args) in D:\Users\multhaupj\Documents\Visual Studio 2010\Projects\SimonsVoss.Lsm.Example\SimonsVoss.Lsm.TestApp\Program.cs:line 76
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: Telerik.OpenAccess.RT.sql.SQLException
       Message=Die INSERT-Anweisung steht in Konflikt mit der FOREIGN KEY-Einschränkung 'FK_tblDoorSector_tblSector'. Der Konflikt trat in der lsmdb-Datenbank, Tabelle 'dbo.tblSector' auf.
Die Anweisung wurde beendet.
       Source=Telerik.OpenAccess.Runtime
       Description=SQLState=;Die INSERT-Anweisung steht in Konflikt mit der FOREIGN KEY-Einschränkung 'FK_tblDoorSector_tblSector'. Der Konflikt trat in der lsmdb-Datenbank, Tabelle 'dbo.tblSector' auf.
Die Anweisung wurde beendet.
       ErrorCode=547
       Number=547
       StackTrace:
            at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeUpdate()
            at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeUpdate()
            at OpenAccessRuntime.Relational.metadata.RelationalLinkCollectionField.insertUnorderedLinkTableRows(OID oid, Object[] inserted, PreparedStatement psins, Boolean batch, String sql)
       InnerException: 

Any ideas?

Best regards
Jörg

[Damyan Bogoev]

Hi Jörg,

It seems that the association between the Sector and the Door classes is not marked as managed. In order to achieve that please do the following steps:

1. Select the association from the visual designer;
2. Press F4 to open the Properties Grid window;
3. Expand the Target Property tree node and set the Is Managed to True;
4. Expand the Source Property tree node and set the Is Managed to True;
5. Save the diagram and rebuild the solution;

Hope that helps.

All the best,
Damyan Bogoev
the Telerik team

[Brad]

I just ran into this issue today too and it took a while figure out, but thankfully this thread helped me figure out that I just had to set IsManaged to true on the Target Property of the association.  Why is this not the default.  It seems to me that this would be the desired action the majority of the time.

[Doroteya]

Hello Brad,

Thank you for the feedback.

Indeed, setting isManaged to true is applicable in a lot of scenarios. There are, however, scenarios where that is simply unnecessary and may cause a slower performance. Given that, we decided the default setting to be false and allowed the developers to change it whenever they find it feasible.

I hope that helps. If you have additional questions, do not hesitate to get back to us.


All the best,
Doroteya
the Telerik team