One-To-One relationship creates circular association - cannot insert 'sibling'

I have been struggling all day to get a 1:1 relationship between 2 'sibling' tables to be automatically handled by Lightspeed. I am evaluating it for purchase but I have been stuck on the very first thing I tried to do. Here is the scenario:

1) There are two tables - A and B. Each has a string SOMEKEY as its primary key (this is Oracle)

2) The primary key SOMEKEY is defined identically for both tables

3) There is a foreign key from B to A. Hence, A must exist. B does not have to exist except a business rule mandates that the sibling B for A always be created. It holds 'extra' customisable attributes to extend the default attributes of A.

4) Dropping A on the design surface generates a model.

5) I need a key I generate so I have changed the entity to accept a new primary key as a string in the constructor.

6) Dropping B on the design surface creates a many-to-one relationship between B and A. I change this to one-to-one with the properties dialog. As near as I can tell all this does is change the name of the association thingmy from Bs to B (i.e. singular)

7) An 'association' is generated in B for A as well as in A for B. On one end the name is As, on the other the name is SOMEKEY ( I am simplifying. There are _ fields and what not). This is the bewildering part.

8) Since I am declaring the new primary key string myself and passing it to the new entity, I have deleted what appears to be auto-generated SomeKeyID from A AND from B. I initialise the key on both at the same time BEFORE attaching them to the context. The values look correct in debug mode. The key is there as the Id in the context for both.

9) The words 'Foreign Key' never appear anywhere in the model. i think that B should have SOMEKEY explicitly defined as a foreign key. Unfortunately the express evaluation version does not seem to permit the use of a tool to define the links between A and B as a FK relationship. Maybe I need to buy LS first....

10) Call to SaveChanges.. A appears to be correct in the SQL that is created but B has extra field SOMEKEY and Oracle blows large chunks.

11) Can you please post a short working example that works for a one-to-one like this?

One other note: A separate thread provided a solution for user-defined keys called an 'ExtendedEntity' which subsumes a bunch of suggestions about natural key handling into an abstract base class. It seems to work fine both for single string natural keys as well as user-defined composite keys. All hail to that contributor.

The class is used in step (5) of the above. It looks like this:

/// <summary>
/// Extended type permits initialization of key in new Object(id)
/// </summary>
/// <see cref="http://www.mindscapehq.com/forums/thread/4767"/>
/// <typeparam name="T"></typeparam>
[Serializable]
public abstract class ExtendedEntity<T> : Entity<T>
{
    [Transient]
    protected T _externalId = default(T);

    public ExtendedEntity()
        : base()
    { }

    public ExtendedEntity(T externalId)
        : base()
    {
        this._externalId = externalId;
    }

    protected override object GeneratedId()
    {
        if (!EqualityComparer<T>.Default.Equals(this._externalId, default(T)))
            return this._externalId;

        return base.GeneratedId();
    }
}

}

This is nice, but EVERY time the design is saved from the Lightspeed surface, it obliterates the use of this class and sets all entities back to the default. It would be very helpful if it did NOT do this. With 3 or 4 entities it is a trivial matter to fix them up, but if I implement this software I will have dozens of entities many of which are legacy and CANNOT BE Id-ified.

Are you able to post some more details about the error that is occurring when you call SaveChanges?

In terms of persisting the base class, you can do this by adding a new External Class Reference via the LightSpeed Model tool pane which specifies the details of the ExtendedEntity class, and then you can specify the ExtendedEntity as the base class for your entities by setting the Base Class property on the entity properties.

Jeremy

Thank you for the reply and thank you for your assistance in this matter. I am sure it is something wondrously bone-headed that I am doing. I welcome your constructive criticism.

ENTITY MODELS

The first bits are the entity models. Table Bridge is 'A' and table Userbrdg is 'B' (the sibling/child) in the above discussion. These instantiate properly as far as I can tell by inspecting the properties during the debug session and what not. Note the reference to ExtendedEntity which permits supplying the string natural key on instantiation. There are partials providing constructors in support of the ExtendedEntity base class for each entity as shown.

Please be careful to note that the field BridgeId on the entity Bridge is an alternate key column on table BRIDGE named BRIDGE_ID. It is not coming from LS nor should LS do anything with it. It is simply an attribute that serves as an alternate key for the table in the database. It is my problem to ensure it is quantified and to check for uniqueness in the target.

The primary key field name for both tables in BRKEY in the database as noted below. This field is defined as VARCHAR2(15) in Oracle.

So the basic sequence is that the entities new() up fine, I can quantify the fields, and add them to the unit of work, but when I execute SaveChanges I get either of two exceptions:

1. I get a Circular Association exception with the current code, where a field on userbrdg related to the association is commented out. This field is named bridgeId (unfortunately given my comment about).
2. If I uncomment it, this field gets added to the INSERT statement but it does not exist on the target table so I get a different SQL exception, I tried naming it explicitly to BRKEY but of course that just makes two BRKEY columns in the INSERT, one of which is for the LightSpeed Id and correct, the other generated from the association-related field and not only redundant but null as well.
3. In either case, the LightSpeed Id looks right and quantifies the primary key column as required.
4. For grins I tried just dumping the association altogether to insert these siblings but in that case the dependent record (B / Userbrdg) got inserted BEFORE its parent / older sibling, and a referential integrity error was raised by Oracle. I am not sure how the context decides what order to process its New entities but it seems to be from the last to the first for some reason. I did not bother to juggle the entity initialization sequence to see if I could fool it, since the whole point is to have LS deal with the hierarchy.

I have attached the entire ClassLibrary project all zipped up as of right now for your inspection. I can't see any point to sending you the consuming code beyond the fragments included below but I can do so if you need the consuming class in its entirety. I have also attached the CREATE TABLE statements from Oracle from the database which will make dummy tables for you if you want them. You will have to set the right Oracle tablespace in the create script.

// LightSpeedModel.cs fragment with comments

using System;

using Mindscape.LightSpeed;
using Mindscape.LightSpeed.Validation;
using Mindscape.LightSpeed.Linq;

namespace LBISLsDataAccess
{
  [Serializable]
  [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")]
  [System.ComponentModel.DataObject]
  [Table("Bridge", IdColumnName="BRKEY")]
  public partial class Bridge : ExtendedEntity<string>
  {
    #region Fields

    [Column("BRIDGE_ID")]
    [ValidatePresence]
    [ValidateLength(0, 30)]
    private string _bridgeId;
    [Column("STRUCT_NUM")]
    [ValidateLength(0, 15)]
    private string _structNum;
    [ValidateLength(0, 50)]
    private string _strucname;
    [ValidateLength(0, 24)]
    private string _featint;
    ...
    ... // goes on and on and on and on....
    ...
    [Column("PRECISE_LAT")]
    private System.Nullable<double> _preciseLat;
    [Column("PRECISE_LON")]
    private System.Nullable<double> _preciseLon;

    #endregion

    #region Field attribute and view names


    /// <summary>Identifies the BridgeId entity attribute.</summary>
    public const string BridgeIdField = "BridgeId";
    /// <summary>Identifies the StructNum entity attribute.</summary>
    public const string StructNumField = "StructNum";
    /// <summary>Identifies the Strucname entity attribute.</summary>
    public const string StrucnameField = "Strucname";
    /// <summary>Identifies the Featint entity attribute.</summary>
    public const string FeatintField = "Featint";
    /// <summary>Identifies the FhwaRegn entity attribute.</summary>
    ...
    ...
    ...
    /// <summary>Identifies the PreciseLat entity attribute.</summary>
    public const string PreciseLatField = "PreciseLat";
    /// <summary>Identifies the PreciseLon entity attribute.</summary>
    public const string PreciseLonField = "PreciseLon";


   #endregion

   #region Relationships

    [ReverseAssociation("Bridge")]
    private readonly EntityHolder<Userbrdg> _userbrdg = new EntityHolder<Userbrdg>();


    #endregion

    #region Properties

    [System.Diagnostics.DebuggerNonUserCode]
    public Userbrdg Userbrdg
    {
     get { return Get(_userbrdg); }
     set { Set(_userbrdg, value); }
    }


    [System.Diagnostics.DebuggerNonUserCode]
    public string BridgeId
    {
     get { return Get(ref _bridgeId, "BridgeId"); }
     set { Set(ref _bridgeId, value, "BridgeId"); }
    }

    [System.Diagnostics.DebuggerNonUserCode]
    public string StructNum
    {
      get { return Get(ref _structNum, "StructNum"); }
      set { Set(ref _structNum, value, "StructNum"); }
    }

    [System.Diagnostics.DebuggerNonUserCode]
    public string Strucname
    {
      get { return Get(ref _strucname, "Strucname"); }
      set { Set(ref _strucname, value, "Strucname"); }
    }

    [System.Diagnostics.DebuggerNonUserCode]
    public string Featint
    {
      get { return Get(ref _featint, "Featint"); }
      set { Set(ref _featint, value, "Featint"); }
    }

...
...
...

    [System.Diagnostics.DebuggerNonUserCode]
    public System.Nullable<double> PreciseLat
    {
      get { return Get(ref _preciseLat, "PreciseLat"); }
      set { Set(ref _preciseLat, value, "PreciseLat"); }
    }

    [System.Diagnostics.DebuggerNonUserCode]
    public System.Nullable<double> PreciseLon
    {
      get { return Get(ref _preciseLon, "PreciseLon"); }
      set { Set(ref _preciseLon, value, "PreciseLon"); }
    }

    #endregion
  }

   [Serializable]
   [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")]
   [System.ComponentModel.DataObject]
   [Table(IdColumnName="BRKEY")]
   public partial class Userbrdg : ExtendedEntity<string>
   {
     #region Fields

     [Column("OWNER_KDOT")]
     [ValidateLength(0, 2)]
     private string _ownerKdot;
     [Column("MAINT_AREA")]
     [ValidateLength(0, 2)]
     private string _maintArea;
     [Column("CUSTODIAN_KDOT")]
     [ValidateLength(0, 2)]
     ...
     ...
     ...
      [Column("ENV_NOTATION_1")]
     [ValidateLength(0, 2)]
     private string _envNotation1;
     [Column("ENV_NOTATION_2")]
     [ValidateLength(0, 2)]
     private string _envNotation2;
     [Transient]
     private string _bridgeId;

     #endregion

     #region Field attribute and view names

    /// <summary>Identifies the OwnerKdot entity attribute.</summary>
    public const string OwnerKdotField = "OwnerKdot";
    /// <summary>Identifies the MaintArea entity attribute.</summary>
    public const string MaintAreaField = "MaintArea";
    /// <summary>Identifies the CustodianKdot entity attribute.</summary>
    public const string CustodianKdotField = "CustodianKdot";


    /// <summary>Identifies the EnvNotation2 entity attribute.</summary>
    public const string EnvNotation2Field = "EnvNotation2";

The problem association-related field is created by LS here

This is what gets generated by LS for the association. If I leave this uncommented, I get an extra unknown field generated in my SQL. When I uncomment it, I get the circular association exception shown at the end. I have tried setting it to [Transient] to no avail

    /// <summary>Identifies the BridgeId entity attribute.</summary>
   // public const string BridgeIdField = "BridgeId";

    #endregion

    #region Relationships

    [ReverseAssociation("Userbrdg")]
    private readonly EntityHolder<Bridge> _bridge = new EntityHolder<Bridge>();


    #endregion

    #region Properties

    [System.Diagnostics.DebuggerNonUserCode]
    public Bridge Bridge
    {
      get { return Get(_bridge); }
      set { Set(_bridge, value); }
    }


    [System.Diagnostics.DebuggerNonUserCode]
    public string OwnerKdot
    {
      get { return Get(ref _ownerKdot, "OwnerKdot"); }
      set { Set(ref _ownerKdot, value, "OwnerKdot"); }
    }

    [System.Diagnostics.DebuggerNonUserCode]
    public string MaintArea
    {
      get { return Get(ref _maintArea, "MaintArea"); }
      set { Set(ref _maintArea, value, "MaintArea"); }
    }

    [System.Diagnostics.DebuggerNonUserCode]
    public string CustodianKdot
    {
      get { return Get(ref _custodianKdot, "CustodianKdot"); }
      set { Set(ref _custodianKdot, value, "CustodianKdot"); }
    }
...
...
...
    [System.Diagnostics.DebuggerNonUserCode]
    public string EnvNotation1
    {
      get { return Get(ref _envNotation1, "EnvNotation1"); }
      set { Set(ref _envNotation1, value, "EnvNotation1"); }
    }

    [System.Diagnostics.DebuggerNonUserCode]
    public string EnvNotation2
    {
      get { return Get(ref _envNotation2, "EnvNotation2"); }
      set { Set(ref _envNotation2, value, "EnvNotation2"); }
    }

    /// <summary>Gets or sets the ID for the <see cref="Bridge" /> property.</summary>
    [System.Diagnostics.DebuggerNonUserCode]
    public string BridgeId
    {
      get { return Get(ref _bridgeId, "BridgeId"); }
      set { Set(ref _bridgeId, value, "BridgeId"); }
    }

    #endregion
  }



using System;
using System.Linq;

namespace LBISLsDataAccess
{
    public partial class Bridge
    {

        public string Brkey
        {
            get { return Id; }
        }


        public Bridge()
            : base()
        { }

        public Bridge(string id)
            : base(id)
        { }


    }
}

using System;
using System.Linq;

namespace LBISLsDataAccess
{
    public partial class Userbrdg
    {
        public Userbrdg()
            : base()
        { }

        public Userbrdg(string id)
            : base(id)
        { }

        public string Brkey
        {
            get
            {
                return Id;
            }
        }

    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using Mindscape.LightSpeed;

namespace LBISLsDataAccess
{

    /// <summary>
    /// Extended type permits initialization of key in new Object(id)
    /// </summary>
    /// <see cref="http://www.mindscapehq.com/forums/thread/4767"/>
    /// <typeparam name="T"></typeparam>
    [Serializable]
    public abstract class ExtendedEntity<T> : Entity<T>
    {
        [Transient]
        protected T _externalId = default(T);

        public ExtendedEntity()
            : base()
        { }

        public ExtendedEntity(T externalId)
            : base()
        {
            this._externalId = externalId;
        }

        protected override object GeneratedId()
        {
            if (!EqualityComparer<T>.Default.Equals(this._externalId, default(T)))
                return this._externalId;

            return base.GeneratedId();
        }
    }


}

Relevant consuming code

protected void CreateNewBridgeRecords()
        {

            _context = new LightSpeedContext<LBISLsModelUnitOfWork>("LBIS");
            using (var dbContext = _context.CreateUnitOfWork())
            {
#if DEBUG
                _context.VerboseLogging = true;
                _context.Logger = new IntegratedLogger();

#endif
                //var audits

                var auditdt = System.DateTime.Now;
                var userkey = GetUserKeyForSession("1");

                // all-important
                var theNewBrkey = this.CreateBridgeIdentifiers1.GetNewBrkey().ToString();


                #region BRIDGEMODEL

                // 1) Bridge
                // set primary key
                Debug.Assert(!String.IsNullOrEmpty(theNewBrkey), @"The new record''s Brkey was not generated.");

                var newbr = new LBISLsDataAccess.Bridge(theNewBrkey);




                // set other identifiers



                newbr.BridgeId = this.CreateBridgeIdentifiers1.GetNewBridgeID(); // BRIDGE_ID
                newbr.StructNum = this.CreateBridgeIdentifiers1.GetNewStructNum();// Struct_Num
                newbr.Strucname = this.CreateBridgeIdentifiers1.GetNewLPABridgeID();// LPA ID is actually stored in STRUC_NAME

                // location
                newbr.Facility = this.FacilityTextBox.Text;
                newbr.Featint = this.FeatIntTextBox.Text;
...
...
...
try
              {
                  newbr.PreciseLon = Convert.ToDouble(this.gps_longitude.Text);
                  newbr.PreciseLat = Convert.ToDouble(this.gps_latitude.Text);
              }
              catch (InvalidCastException invCast)
              {
                  throw invCast;
              }
              catch
              { throw; }
...
...
...

Adding the Bridge entity SUCCEEDS

... ... ...

                // make the record
                dbContext.Add(newbr);
                if (!String.IsNullOrEmpty(newbr.Id))
                    Log.Debug("New Id = " + newbr.Id.ToString());
                Log.Debug("New BRIDGE.BRKEY = " + newbr.Brkey.ToString());

... ...

Creating the Userbrdg sibling record succeeds

... ... ... // sibling (1) one-to-one relationship var newusrbr = new LBISLsDataAccess.Userbrdg(theNewBrkey); ... ... ...

              // audits.
                newusrbr.Createdatetime = auditdt;
                newusrbr.Modtime = auditdt;
                newusrbr.Createuserkey = userkey;
                newusrbr.Userkey = userkey;

                newusrbr.KdotLatitude = newbr.PreciseLat;
                newusrbr.KdotLongitude = newbr.PreciseLon;

... ... ...

Adding the Userbrdg entity SUCCEEDS

                // make the record
                dbContext.Add(newusrbr);

               // id shows up as expected.    
                if (!String.IsNullOrEmpty(newusrbr.Id))
                {
                    Log.Debug("New USERBRDG.BRKEY = " + newusrbr.Brkey.ToString());
                    Log.Debug("New USERBRDG.ID = " + newusrbr.Id.ToString());
                }

...
...
...

SaveChanges then fails

                try
                {
                    dbContext.SaveChanges();
                }

                catch (Mindscape.LightSpeed.LightSpeedException ltspdex)
                {

                    _context.Logger.LogDebug(ltspdex.Message);
                    _context.Logger.LogDebug(ltspdex.StackTrace);
                    throw;
                }   

                catch (Mindscape.LightSpeed.Validation.ValidationException lsvldex)
                {

                    _context.Logger.LogDebug(lsvldex.Message);
                    _context.Logger.LogDebug(lsvldex.StackTrace);
                    throw;
                }

                catch (Exception e)
                {
                    throw;
                }


...
...
...

Exception is Raised

Exception:Thrown: "Circular associations are not supported by LightSpeed: [Userbrdg.Bridge and Bridge.Userbrdg]" (Mindscape.LightSpeed.LightSpeedException)
A Mindscape.LightSpeed.LightSpeedException was thrown: "Circular associations are not supported by LightSpeed: [Userbrdg.Bridge and Bridge.Userbrdg]"
Time: 10/28/2015 07:46:57
Thread:Worker Thread[21572]

>   LBISLsDataAccess.dll!LBISLsDataAccess.ExtendedEntity`1..ctor(T externalId = {unknown})  C#
    LBISLsDataAccess.dll!LBISLsDataAccess.Bridge..ctor(string id = {unknown})   C#
    LBIS8.dll!LBIS8.CreateBridgeDialogMC.CreateNewBridgeRecords()   C#
    LBIS8.dll!LBIS8.CreateBridgeDialogMC.CreateButton_Click(object sender = {unknown}, System.EventArgs e = {unknown})  C#
    Telerik.Web.UI.dll!Telerik.Web.UI.RadButton.OnClick(Telerik.Web.UI.ButtonClickEventArgs e = {unknown})  C#
    Telerik.Web.UI.dll!Telerik.Web.UI.RadButton.RaisePostBackEvent(string eventArgument = {unknown})    C#
    Telerik.Web.UI.dll!Telerik.Web.UI.RadButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(string eventArgument = {unknown})    C#
    System.Web.dll!System.Web.UI.Page.RaisePostBackEvent(System.Web.UI.IPostBackEventHandler sourceControl = {unknown}, string eventArgument = {unknown})   C#
    System.Web.dll!System.Web.UI.Page.RaisePostBackEvent(System.Collections.Specialized.NameValueCollection postData = {unknown})   C#
    System.Web.dll!System.Web.UI.Page.ProcessRequestMain(bool includeStagesBeforeAsyncPoint = {unknown}, bool includeStagesAfterAsyncPoint = {unknown}) C#
    System.Web.dll!System.Web.UI.Page.ProcessRequest(bool includeStagesBeforeAsyncPoint = {unknown}, bool includeStagesAfterAsyncPoint = {unknown}) C#
    System.Web.dll!System.Web.UI.Page.ProcessRequest()  C#
    System.Web.dll!System.Web.UI.Page.ProcessRequestWithNoAssert(System.Web.HttpContext context = {unknown})    C#
    System.Web.dll!System.Web.UI.Page.ProcessRequest(System.Web.HttpContext context = {unknown})    C#
    App_Web_250beq0g.dll!ASP.createbridgedialogmc_aspx.ProcessRequest(System.Web.HttpContext context = {unknown})   C#
    System.Web.dll!CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() C#
    System.Web.dll!System.Web.HttpApplication.ExecuteStep(IExecutionStep step = {unknown}, ref bool completedSynchronously = {unknown}) C#
    System.Web.dll!PipelineStepManager.ResumeSteps(System.Exception error = {unknown})  C#
    System.Web.dll!System.Web.HttpApplication.BeginProcessRequestNotification(System.Web.HttpContext context = {unknown}, System.AsyncCallback cb = {unknown})  C#
    System.Web.dll!System.Web.HttpRuntime.ProcessRequestNotificationPrivate(System.Web.Hosting.IIS7WorkerRequest wr = {unknown}, System.Web.HttpContext context = {unknown})    C#
    System.Web.dll!System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(System.IntPtr rootedObjectsPointer = {unknown}, System.IntPtr nativeRequestContext = {unknown}, System.IntPtr moduleData = {unknown}, int flags = {unknown}) C#
    System.Web.dll!System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(System.IntPtr rootedObjectsPointer = {unknown}, System.IntPtr nativeRequestContext = {unknown}, System.IntPtr moduleData = {unknown}, int flags = {unknown})   C#
    System.Web.dll!System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(System.IntPtr rootedObjectsPointer = {unknown}, System.IntPtr nativeRequestContext = {unknown}, System.IntPtr moduleData = {unknown}, int flags = {unknown}) C#
    System.Web.dll!System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(System.IntPtr rootedObjectsPointer = {unknown}, System.IntPtr nativeRequestContext = {unknown}, System.IntPtr moduleData = {unknown}, int flags = {unknown})   C#

Actually my summary is not quite right.

The above code actually fails when I comment out the bridgeID LS field related to the association. It tries to Add(...) the Bridge entity to the context and fails with the circular association exception. I only get to the SaveChanges failure if I do not try to comment out the association field bridgeId that is generating the extra column. In that case it gets through all the stuff but there is no love at the end.

BTW: The External Class References works as indicated. Thank you.

This is the duplicate column name version of the problem:

Here is the output when I drop the association and recreate it from the Model Designer. I have attached the model and generated cs for this case. It is so close to working but generates this bogus extra column.

2015-10-28 09:57:34,228 [15] DEBUG LOGGER [(null)] - New Id = 123456789012345

2015-10-28 09:57:34,229 [15] DEBUG LOGGER [(null)] - New BRIDGE.BRKEY = 123456789012345 2015-10-28 09:57:38,760 [15] DEBUG LOGGER [(null)] - New USERBRDG.BRKEY = 123456789012345 2015-10-28 09:57:38,761 [15] DEBUG LOGGER [(null)] - New USERBRDG.ID = 123456789012345 2015-10-28 09:57:38,761 [15] DEBUG LOGGER [(null)] - Attempting to save new bridge 123456789012345... 2015-10-28 09:57:54,933 [15] DEBUG LOGGER [(null)] - BEGIN

INSERT INTO Bridge ( BRKEY, Adminarea, Altirload, Altirmeth, Altorload, Altormeth, Appspans, BBBRDGEID, BBPCT, BRIDGEID, BRIDGELIFECYCLEPHASE, BRIDGESTATUS, Bridgegroup, Btrigger, Bridgemed, County, Createdatetime, Createuserkey, Crewhrs, Custodian, DECKAREA, Deckwidth, DEFOPRAT, Designappr, Designload, Designmain, District, Dkmembtype, Dkprotect, Dkstructyp, Dksurftype, Docrefkey, Facility, FCDETAIL, Featint, FHWAREGN, FIPSSTATE, Flaggerhrs, Hclrult, Hclrurt, Helperhrs, Histsign, Impact, Implen, Irfactor, Irload, Irtype, Latitude, Length, Lftbrnavcl, Lftcurbsw, Location, Longitude, Mainspans, Materialappr, Materialmain, Maxspan, Modtime, NFHWAREG, Navcntrol, Navhc, Navvc, Nbiimpcost, Nbirwcost, Nbislen, Nbitotcost, Nbiyrcost, Nextinspid, Notes, Nstatecode, ONOFFSYS, Orfactor, Orload, Ortype, Otherload, Owner, Paralstruc, Placecode, Posting, PRECISELAT, PRECISELON, Propwork, RATERINI, Ratingdate, Refhuc, Refvuc, REQOPRAT, Rtcurbsw, Servtypon, Servtypund, Skew, Snooperhrs, Spcrewhrs, Spequiphrs, Srstatus, Strflared, Strucname, STRUCTNUM, Sumlanes, Tempstruc, TOT_LENGTH, Traceflag, Truck1Ir, Truck1Or, Truck2Ir, Truck2Or, Truck3Ir, Truck3Or, Userkey, Userkey1, Userkey10, Userkey11, Userkey12, Userkey13, Userkey14, Userkey15, Userkey2, Userkey3, Userkey4, Userkey5, Userkey6, Userkey7, Userkey8, Userkey9, Vclrover, Vclrunder, Workby, Yearbuilt, Yearrecon ) VALUES ( '123456789012345', '', NULL, NULL, NULL, NULL, 0, NULL, NULL, '123456789012345', NULL, NULL, '', NULL, NULL, '', '10/28/2015 09:57:13', '5568', NULL, '', NULL, NULL, NULL, '', NULL, '', '', NULL, NULL, NULL, NULL, '4d89fe65-17db-419b-86f2-a338acb9be90', '', NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, '', '', NULL, '10/28/2015 09:57:13', NULL, NULL, NULL, NULL, NULL, NULL, 'Y', NULL, NULL, NULL, 'Starter BRIDGE record created on 10/28/2015 - ', NULL, 'Y', NULL, NULL, NULL, NULL, '', NULL, '', NULL, 39.050934, -95.678382, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '123456789012345', '123456789012345', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '5568', '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL );

INSERT INTO Userbrdg ( BRKEY, ATTACHDESC1, ATTACHDESC2, ATTACHDESC3, ATTACHTYPE1, ATTACHTYPE2, ATTACHTYPE3, AVGHI, BOXHEIGHT_CULV,

BRKEY,

BRIDGEMEDKDOT, Createdatetime, Createuserkey, CULVFILLDEPTH, CULVWINGTYPE, CUSTODIANKDOT, DESIGNREFPOST, DESIGNLOADKDOT, DESIGNLOADTYPE, DRAINAGEAREA, ENVNOTATION1, ENVNOTATION2, FUNCTIONTYPE, GRAILAPRLT, GRAILAPRRT, GRAILENDTREAT, GRAILEXITLT, GRAILEXITRT, GRAILTYPE, HORIZUNDRSIGN, IRLOADADJ3, IRLOADADJ33, IRLOADADJ3S2, IRLOADADJH, IRLOADADJT130, IRLOADADJT170, IRLOADLFD3, IRLOADLFD33, IRLOADLFD3S2, IRLOADLFDH, IRLOADLFDHS, IRLOADLFDT130, IRLOADLFDT170, IRLOADWSD3, IRLOADWSD33, IRLOADWSD3S2, IRLOADWSDH, IRLOADWSDHS, IRLOADWSDT130, IRLOADWSDT170, KDOTLATITUDE, KDOTLONGITUDE, LASTPAINTSUPE, MAINTAREA, MEDIANWIDTH, Modtime, MULTIDRAINAGE, Notes, Orientation, ORLOADADJ3, ORLOADADJ33, ORLOADADJ3S2, ORLOADADJH, ORLOADADJT130, ORLOADADJT170, ORLOADLFD3, ORLOADLFD33, ORLOADLFD3S2, ORLOADLFDH, ORLOADLFDHS, ORLOADLFDT130, ORLOADLFDT170, ORLOADWSD3, ORLOADWSD33, ORLOADWSD3S2, ORLOADWSDH, ORLOADWSDHS, ORLOADWSDT130, ORLOADWSDT170, OWNERKDOT, PAINTSURFAREA, POSTEDLOADA, POSTEDLOADB, POSTEDLOADC, POSTEDSIGNTYPE, RATINGADJ, RATINGCOMMENT, RESERVOIRADJ, RESTRICTLOAD, ROADTYPESIGN, ROTANGLEDEG, ROTANGLEMIN, ROTDIRECTION, SIGNTYPEQ1, SIGNTYPEQ2, SIGNTYPEQ3, SIGNTYPEQ4, SKEWDIRECTION, SKEWMINUTES, STREAMSIGN, SUPERPAINTSYS, SUPRSTRUCTTOS, Userkey, VERTCLRSIGN, VERTUNDRSIGN, Wateropen ) VALUES ( '123456789012345', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

NULL,

NULL, '10/28/2015 09:57:13', '5568', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 39.050934, -95.678382, NULL, NULL, NULL, '10/28/2015 09:57:13', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '5568', NULL, NULL, NULL );

END;

--> Parameter Direction DbType Value --> --------- --------- ---------- ----- --> :p0 Input AnsiString 123456789012345 --> :p1 Input AnsiString
--> :p2 Input Int32 0 --> :p3 Input DateTime 10/28/2015 09:57:13 --> :p4 Input AnsiString 5568 --> :p5 Input AnsiString 4d89fe65-17db-419b-86f2-a338acb9be90 --> :p6 Input AnsiString Y --> :p7 Input AnsiString Starter BRIDGE record created on 10/28/2015 - --> :p8 Input Double 39.050934 --> :p9 Input Double -95.678382

--> Time: 0 ms

2015-10-28 09:57:55,147 [15] DEBUG LOGGER [(null)] - at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc) at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals) at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor) at System.Data.OracleClient.OracleCommand.ExecuteNonQuery() at ...?() at Mindscape.LightSpeed.Profiling.Interceptor.ExecuteCommand(Func1 baseExecutor, IUnitOfWork unitOfWork, IDbCommand command) at ..?(IUnitOfWork , IDbCommand , Func1 ) at ..?(IUnitOfWork , IDbCommand ) at ..?( ) at ..?(IEnumerable1 ) at ..() at ..?(ICollection1 ) at ..?(UnitOfWorkBase , IEnumerable1 , IEnumerable1 ) at Mindscape.LightSpeed.UnitOfWork.SaveChanges(Boolean reset) at Mindscape.LightSpeed.UnitOfWorkBase.SaveChanges() at LBIS8.CreateBridgeDialogMC.CreateNewBridgeRecords() in d:\svnsandbox\Dreamhost\clients\1600 - KDOT BLP\LBIS2.1\development\working\LBIS8\CreateBridgeDialogMC.aspx.cs:line 394 2015-10-28 09:57:55,157 [15] DEBUG LOGGER [(null)] - ORA-06550: line 270, column 3: PL/SQL: ORA-00957: duplicate column name ORA-06550: line 268, column 1: PL/SQL: SQL Statement ignored

2015-10-28 09:57:55,158 [15] DEBUG LOGGER [(null)] - System.Data.OracleClient.OracleException (0x80131938): ORA-06550: line 270, column 3: PL/SQL: ORA-00957: duplicate column name ORA-06550: line 268, column 1: PL/SQL: SQL Statement ignored

at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc) at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals) at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor) at System.Data.OracleClient.OracleCommand.ExecuteNonQuery() at ...?() at Mindscape.LightSpeed.Profiling.Interceptor.ExecuteCommand(Func1 baseExecutor, IUnitOfWork unitOfWork, IDbCommand command) at ..?(IUnitOfWork , IDbCommand , Func1 ) at ..?(IUnitOfWork , IDbCommand ) at ..?( ) at ..?(IEnumerable1 ) at ..() at ..?(ICollection1 ) at ..?(UnitOfWorkBase , IEnumerable1 , IEnumerable1 ) at Mindscape.LightSpeed.UnitOfWork.SaveChanges(Boolean reset) at Mindscape.LightSpeed.UnitOfWorkBase.SaveChanges() at LBIS8.CreateBridgeDialogMC.CreateNewBridgeRecords() in d:\svnsandbox\Dreamhost\clients\1600 - KDOT BLP\LBIS2.1\development\working\LBIS8\CreateBridgeDialogMC.aspx.cs:line 394

I am sure I have gone over-the-top. You did want some more details though.... :D

I may have to start a new thread because a Many-to-one related table with natural keys in a composite Id is also getting a bogus column generated that makes the insert fail. All looks good but the relationship forces. the insert to include the relationship identifier... Fail.

So for the time being and in order to make some progress I have just removed all associations and done all the inserts as separate transactions, with a fallback to delete the entire hierarchy if any part fails to save properly (i.e. hand enforcing referential integrity which is making the value of Lightspeed questionable)

Thanks for the extra detail. The circular reference exception will be being thrown because we expect to find a backing field for the associated FK on one side of a one-one relationship, without this column here it sees this as two separate relationships which in turn causes the circular reference exception to be raised.

So the field has to be there and should be mapped against the relevant database column which holds this value. I am expecting the duplicate column issue is also coming from this being present as well because I am guessing you may already have that column surfaced against an existing entity property - if that is the case you will want to remove that property. Which column would hold this mapping?

Jeremy

The field is named BRKEY in the parent and the child. I am getting an extra BRKEY in the child. I do not think I have an entity property BRKEY yet because the first thing I did was remove any attempt to make this an attribute on the entity when I ran into this problem. So what I would like to do is:

1) Have the SQL NOT include this extra BRKEY. Similarly if I had a 3 or 4 or 5 part composite key, not generate all those (when I get there....)

2) Be able to refer to the BRKEY on the entity in a simple way like BRIDGE.BRKEY instead of Bridge.Id.Brkey although I understand that is often necessary in Linq etc. I can live with that.

Apparently this problem also is affecting many-to-one relationships as I have just found out.

Your model has BRKEY defined on Userbrdg as both the Id column name and the FK column name for the associated Bridge, so this is why the column is being included twice in the generated SQL. Having the column mapped in this way is not a valid configuration.

What is the column that is storing the FK in the database?

Jeremy

Bridge is the parent. Userbrdg is the parallel table that has no meaning if a parent record does not exist. Both have BRKEY as the PK. Userbrdg has a FK to bridge through BRKEY even though it is the PK for Userbrdg. That is the dilemma. Same problem obtains for N:1 tables.

Right that make sense, unfortunately this type of mapping is not supported by LightSpeed. An alternative approach you could look at is to not have an strict association defined between the two entities and manually implement a property to fetch the associated Userbrdg record when required based on the Id of the parent Bridge record.

e.g. Have a property defined on Bridge called Userbrdg:

public Userbrdg Userbrdg
{
  get { return UnitOfWork.FindById<Userbrdg>(Id); }
}

Jeremy

Why are you still awake? It is late here and way too early there.

So this embedded query gives me the related Userbrdg and I just nuke it if I need to in order to maintain Ref Int... ditto for other tables that are in N:1 relationships. This is of course a legacy database. Maybe I can STEAL A COLUMN to use for this but it is probably easier to just keep it tidy by hand.....

So, nutshell, parallel tables with natural keys in a 1:1 relationship are not supported. Ditto for 1:N tables related through a natural key. A wee bit disappointing. In both cases an extra duplicate field is dropped into the SQL.

This is only for INSERTs. Who knows what happens with UPDATEs....

Not really good news I am afraid. Means that I have to hand tend all the RI. Given that, is there a way to make sure that all transactions fire in the desired order and that they are all within the scope of a single transaction. Why does this matter? Because the unit of work does what it does in whatever order it sees fit, apparently, which has led to the dependent row(s) being inserted before the parent, which is not possible.

Here is the real hierarchy of which I only shared the first association.

1. BRIDGE>>>> 1:1 USERBRDG
2. BRIDGE >>>> 1:N INSPECTIONS
3. BRIDGE >>>> 1:N ROADWAYS
4. BRIDGE >>>> N:1 STRUCTURE_UNITS

and each of the 1:N tables has a sibling which is the customisable table users can change to add columns and so on, like this:

1. INSPECTIONS >>>> 1:1 USERINSP
2. ROADWAYS >>>> 1:1 USERRWAY
3. STRUCTURE_UNITS >>>> 1:1 USERSTRUNIT

All 8 of these have the field BRKEY in the primary key.

Not to put too fine a point on it, but I have N United States states that might be interested in what I am working on and they are all using this same database design, since the mid-nineties. Granted, time for an update...

Maybe STEAL A COLUMN is the way to go. :(

This property can then be used to perform updates and deletes on the child etc.? How about inserting if it cannot find it? Would you be able to offer a short example of usages?

Thanks

The example I gave only caters for selection of the child, you would need to handle inserts and updates separate to this.

Jeremy

Yes I gathered that. Thank you. I do not need to do this very often so hand-weeding is acceptable. Thank you