Problems with Inserting into Oracle and Entity Names -- capitalizations...

Greetings. I am new to LightSpeed and am loving it immensely! Amazing product and such great support.

So far selects and updates are working perfectly in the model. Inserts are having problems now.

Here is kind of a log of a few changes I have made and the generated SQL by LightSpeed where applicable.

I shall be so very appreciative for your help! Thank you so kindly.

        Agraha

Situation 1

I have NSM_MAIN entity with EntityColumn name as NMS_MAIN_Id and with the column name as NMS_MAIN_Id.
All the code references the column also as NMS_MAIN_ID

When I try an insert, this error occurs:

unitOfWork.SaveChanges();
INSERT INTO SUPPLYCHAIN.NMS_MAIN
(
  NMS_MAIN_Id,
  APPROVAL_DATE,
  APPROVED_BY,
  DATA_FILE_ID,
  INS_DATE,
  IS_APPROVED,
  ITEM_NUMBER,
  NMS_MAIN_ID,
  PRODUCT_NAME,
  TOTAL_DP
)
VALUES
(
  51,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  0,
  NULL,
  NULL
)

--> Time:   0 ms

Situation 2

I change the Id to ID everywhere, including in code.Except only in 1 place,
as reference

stil the insert is like this: nmsDs.NmsMainId, but that is for the related DS table.


INSERT INTO SUPPLYCHAIN.NMS_MAIN
(
  NMS_MAIN_Id,
  APPROVAL_DATE,
  APPROVED_BY,
  DATA_FILE_ID,
  INS_DATE,
  IS_APPROVED,
  ITEM_NUMBER,
  NMS_MAIN_ID,
  PRODUCT_NAME,
  TOTAL_DP
)
VALUES
(
  71,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  0,
  NULL,
  NULL
)

--> Time:   0 ms

Step 3

Change IDentity ColumnName from NMS_MAIN_Id to NMS_MAIN_ID

Try to run, it compiles, but then when runs get this error:
Unable to materialize field [NMSMAINID] on type [NAL.Model.NMSMAIN]. Check your table has an Id column and that your mappings are correct. See inner exception for details.

Step 4
In the diagram, in the relationship, change BackInference from NmsMain to NMSMAIN and Collection from NmsDs to NMSDS

Then had errors in the code, not knowing NmsNainId.

Step 5

So I changed the reference in code to the nms entity to NMSMAINId from NmsMainId.

Try to run, it compiles, but then when runs get this error:
Unable to materialize field [NMSMAINID] on type [NAL.Model.NMSMAIN]. Check your table has an Id column and that your mappings are correct. See inner exception for details.
Same error as after Step 3

As you can tell, I am trying many items, but not sure how to handle this so it compiles and does correct insertions! Thank you again!

Hello Agraha,

I'm not sure what error you're seeing in your Situations 1 and 2, but it sounds like there's some sort of capitalisation issue that is making Oracle unable to recognise the NMS_MAIN_Id column, is that right?

By default Oracle column names are not case-sensitive, but I believe there is an option when creating an Oracle table to make it case-sensitive.  It sounds like that option may be set on your table.  If so, you need to do two things on the LightSpeed side:

1. Set LightSpeedContext.QuoteIdentifiers to true.  This causes LightSpeed to quote all names, which is required in Oracle to preserve their case (as I understand it).

2. Where the property and column casing don't match, add Column Name settings (or ColumnAttribute if writing the model by hand) to force correct casing.

3. Set Identity Column Name (or TableAttribute.IdColumnName if writing the model by hand), again using the casing from the database.

Regarding your situation 4, if you change the backreference name, this gets used with an Id suffix as the foreign key column name.  E.g. if the backreference is named Parent, LightSpeed will look for a foreign key column named ParentId.  To change the foreign key column name, set the Backreference Id Column Name of the association -- this overrides the default XxxId column name.  (The FK *property* in LightSpeed will still be called XxxId, but it will be mapped to a different column in the database.)

If none of this helps then could you post a *minimal* model (just one or two tables) and table definitions (CREATE TABLE scripts) that reproduce this problem and we will look into it for you.

Thank you so much  for your quick reply, Ivan.

I know it seems that the Oracle column names are acting as if they are case-sensitive, but they are not. I am having problems only with the Identity Column Name setting. All the rest of the mappings for all the fields, including for the actual identity fields, NMS_DS_ID and NMS_MAIN_ID, are mapping just fine, using the settings as read by MindScape's visual interface for the LightSpeed model inside Visual Studio.

However, if I do not change the Identity Column Names from NMS_DS_ID to Nms_Ds_Id and from NMS_MAIN_ID to Nms_Main_Id, I get this error:

Unable to materialize field [NmsStatusId] on type [NAL.Model.NmsDs]. Check your table has an Id column and that your mappings are correct. See inner exception for details.

In fact, that is why I changed the setting of the Identity Column Names in the first place to Nms_Ds_Id and Nms_Main_Id. Then the errors go away and all works for selects, updates and deletes--but not for inserts.

The same happens when I go into the Model.cs code and change what works for all but inserts.

So this works for all but inserts:

 [Table("NMS_MAIN", IdColumnName="Nms_Main_Id", Schema="SUPPLYCHAIN")]

This gives me the run-time error when I try to run the applicaiton:

 [Table("NMS_MAIN", IdColumnName="NMS_MAIN_ID", Schema="SUPPLYCHAIN")]

Here are a couple table definitions from the Oracle database. I hope it well be helpful!

I am not certain if you wanted a little more, as far as the model, so I am also attaching what has been generated from LightSpeed's visual interface modeler for the same two tables.

I apologise in advance for all the code!!! I hope this is what you are requesting me to send.

I appreciate your product and your help immensely, Ivan.

          Agraha

--****ddl for nms_main

CREATE TABLE nms_main
    (nms_main_id                    NUMBER(28,0) NOT NULL,
    item_number                    VARCHAR2(512),
    product_name                   VARCHAR2(1024),
    ins_date                       DATE,
    is_approved                    NUMBER(5,0) DEFAULT 0,
    approved_by                    VARCHAR2(256),
    approval_date                  DATE,
    data_file_id                   NUMBER,
    total_dp                       NUMBER)
  PCTFREE     10
  INITRANS    1
  MAXTRANS    255
  TABLESPACE  supply_chain_data
  STORAGE   (
    INITIAL     65536
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )

ALTER TABLE nms_main
ADD CONSTRAINT pk_nms_main PRIMARY KEY (nms_main_id)
USING INDEX
  PCTFREE     10
  INITRANS    2
  MAXTRANS    255
  TABLESPACE  supply_chain_data
  STORAGE   (
    INITIAL     65536
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )

-- Foreign Key
ALTER TABLE nms_main
ADD CONSTRAINT fk_nms_data_file FOREIGN KEY (data_file_id)
REFERENCES data_file (data_file_id)
/

--****ddl for nms_ds

CREATE TABLE nms_ds
    (nms_ds_id                      NUMBER(28,0) NOT NULL,
    nms_main_id                    NUMBER(28,0),
    item_number                    VARCHAR2(512),
    product_name                   VARCHAR2(1024),
    x_batch                        VARCHAR2(512),
    uom                            VARCHAR2(64),
    batch                          VARCHAR2(512),
    amount                         VARCHAR2(512),
    retest                         VARCHAR2(512),
    expiry                         VARCHAR2(512),
    ins_date                       DATE,
    total_from_file                NUMBER,
    nms_status_id                  NUMBER(28,0))
  PCTFREE     10
  INITRANS    1
  MAXTRANS    255
  TABLESPACE  supply_chain_data
  STORAGE   (
    INITIAL     65536
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
/

-- Constraints for NMS_DS
ALTER TABLE nms_ds
ADD CONSTRAINT pk_nms_ds PRIMARY KEY (nms_ds_id)
USING INDEX
  PCTFREE     10
  INITRANS    2
  MAXTRANS    255
  TABLESPACE  supply_chain_data
  STORAGE   (
    INITIAL     65536
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
/

-- Foreign Key
ALTER TABLE nms_ds
ADD CONSTRAINT fk_nms_main FOREIGN KEY (nms_main_id)
REFERENCES nms_main (nms_main_id)
/
ALTER TABLE nms_ds
ADD CONSTRAINT fk_nms_status FOREIGN KEY (nms_status_id)
REFERENCES nms_status (nms_status_id)
/

*****************************************************************

Generated code inside Visual Studio for NMS_MAIN object

  [Serializable]
  [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")]
  [Table("NMS_MAIN", IdColumnName="Nms_Main_Id", Schema="SUPPLYCHAIN")]
  [System.ComponentModel.DataObject]
  public partial class NmsMain : Entity<int>
  {
    #region Fields
 
    [Column("NMS_MAIN_ID")]
    private int _nmsMainId;
    [Column("ITEM_NUMBER")]
    [ValidateLength(0, 512)]
    private string _itemNumber;
    [Column("PRODUCT_NAME")]
    [ValidateLength(0, 1024)]
    private string _productName;
    [Column("INS_DATE")]
    private System.Nullable<System.DateTime> _insDate;
    [Column("IS_APPROVED")]
    private System.Nullable<int> _isApproved;
    [Column("APPROVED_BY")]
    [ValidateLength(0, 256)]
    private string _approvedBy;
    [Column("APPROVAL_DATE")]
    private System.Nullable<System.DateTime> _approvalDate;
    [Column("TOTAL_DP")]
    private System.Nullable<decimal> _totalDp;
    [Column("DATA_FILE_ID")]
    private System.Nullable<int> _dataFileId;

    #endregion
   
    #region Field attribute and view names
   
    /// <summary>Identifies the NmsMainId entity attribute.</summary>
    public const string NmsMainIdField = "NmsMainId";
    /// <summary>Identifies the ItemNumber entity attribute.</summary>
    public const string ItemNumberField = "ItemNumber";
    /// <summary>Identifies the ProductName entity attribute.</summary>
    public const string ProductNameField = "ProductName";
    /// <summary>Identifies the InsDate entity attribute.</summary>
    public const string InsDateField = "InsDate";
    /// <summary>Identifies the IsApproved entity attribute.</summary>
    public const string IsApprovedField = "IsApproved";
    /// <summary>Identifies the ApprovedBy entity attribute.</summary>
    public const string ApprovedByField = "ApprovedBy";
    /// <summary>Identifies the ApprovalDate entity attribute.</summary>
    public const string ApprovalDateField = "ApprovalDate";
    /// <summary>Identifies the TotalDp entity attribute.</summary>
    public const string TotalDpField = "TotalDp";
    /// <summary>Identifies the DataFileId entity attribute.</summary>
    public const string DataFileIdField = "DataFileId";


    #endregion
   
    #region Relationships

    [ReverseAssociation("NmsMain")]
    private readonly EntityCollection<NmsDs> _nmsDs = new EntityCollection<NmsDs>();
    [ReverseAssociation("NmsMains")]
    private readonly EntityHolder<DataFile> _dataFile = new EntityHolder<DataFile>();


    #endregion
   
    #region Properties

    public EntityCollection<NmsDs> NmsDs
    {
      get { return Get(_nmsDs); }
    }

    public DataFile DataFile
    {
      get { return Get(_dataFile); }
      set { Set(_dataFile, value); }
    }


    public int NmsMainId
    {
      get { return Get(ref _nmsMainId, "NmsMainId"); }
      set { Set(ref _nmsMainId, value, "NmsMainId"); }
    }

    public string ItemNumber
    {
      get { return Get(ref _itemNumber, "ItemNumber"); }
      set { Set(ref _itemNumber, value, "ItemNumber"); }
    }

    public string ProductName
    {
      get { return Get(ref _productName, "ProductName"); }
      set { Set(ref _productName, value, "ProductName"); }
    }

    public System.Nullable<System.DateTime> InsDate
    {
      get { return Get(ref _insDate, "InsDate"); }
      set { Set(ref _insDate, value, "InsDate"); }
    }

    public System.Nullable<int> IsApproved
    {
      get { return Get(ref _isApproved, "IsApproved"); }
      set { Set(ref _isApproved, value, "IsApproved"); }
    }

    public string ApprovedBy
    {
      get { return Get(ref _approvedBy, "ApprovedBy"); }
      set { Set(ref _approvedBy, value, "ApprovedBy"); }
    }

    public System.Nullable<System.DateTime> ApprovalDate
    {
      get { return Get(ref _approvalDate, "ApprovalDate"); }
      set { Set(ref _approvalDate, value, "ApprovalDate"); }
    }

    public System.Nullable<decimal> TotalDp
    {
      get { return Get(ref _totalDp, "TotalDp"); }
      set { Set(ref _totalDp, value, "TotalDp"); }
    }

    /// <summary>Gets or sets the ID for the <see cref="DataFile" /> property.</summary>
    public System.Nullable<int> DataFileId
    {
      get { return Get(ref _dataFileId, "DataFileId"); }
      set { Set(ref _dataFileId, value, "DataFileId"); }
    }

    #endregion
  }

Generated code inside Visual Studio for NMS_DS object:

  [Serializable]
  [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")]
  [Table("NMS_DS", IdColumnName="Nms_Ds_Id", Schema="SUPPLYCHAIN")]
  [System.ComponentModel.DataObject]
  public partial class NmsDs : Entity<int>
  {
    #region Fields
 
    [Column("NMS_DS_ID")]
    private int _nmsDsId;
    [Column("ITEM_NUMBER")]
    [ValidateLength(0, 512)]
    private string _itemNumber;
    [Column("PRODUCT_NAME")]
    [ValidateLength(0, 1024)]
    private string _productName;
    [Column("X_BATCH")]
    [ValidateLength(0, 512)]
    private string _xBatch;
    [ValidateLength(0, 64)]
    private string _uom;
    [ValidateLength(0, 512)]
    private string _batch;
    [ValidateLength(0, 512)]
    private string _amount;
    [ValidateLength(0, 512)]
    private string _retest;
    [ValidateLength(0, 512)]
    private string _expiry;
    [Column("INS_DATE")]
    private System.Nullable<System.DateTime> _insDate;
    [Column("TOTAL_FROM_FILE")]
    private System.Nullable<decimal> _totalFromFile;
    [Column("NMS_MAIN_ID")]
    private System.Nullable<int> _nmsMainId;
    [Column("NMS_STATUS_ID")]
    private System.Nullable<int> _nmsStatusId;

    #endregion
   
    #region Field attribute and view names
   
    /// <summary>Identifies the NmsDsId entity attribute.</summary>
    public const string NmsDsIdField = "NmsDsId";
    /// <summary>Identifies the ItemNumber entity attribute.</summary>
    public const string ItemNumberField = "ItemNumber";
    /// <summary>Identifies the ProductName entity attribute.</summary>
    public const string ProductNameField = "ProductName";
    /// <summary>Identifies the XBatch entity attribute.</summary>
    public const string XBatchField = "XBatch";
    /// <summary>Identifies the Uom entity attribute.</summary>
    public const string UomField = "Uom";
    /// <summary>Identifies the Batch entity attribute.</summary>
    public const string BatchField = "Batch";
    /// <summary>Identifies the Amount entity attribute.</summary>
    public const string AmountField = "Amount";
    /// <summary>Identifies the Retest entity attribute.</summary>
    public const string RetestField = "Retest";
    /// <summary>Identifies the Expiry entity attribute.</summary>
    public const string ExpiryField = "Expiry";
    /// <summary>Identifies the InsDate entity attribute.</summary>
    public const string InsDateField = "InsDate";
    /// <summary>Identifies the TotalFromFile entity attribute.</summary>
    public const string TotalFromFileField = "TotalFromFile";
    /// <summary>Identifies the NmsMainId entity attribute.</summary>
    public const string NmsMainIdField = "NmsMainId";
    /// <summary>Identifies the NmsStatusId entity attribute.</summary>
    public const string NmsStatusIdField = "NmsStatusId";


    #endregion
   
    #region Relationships

    [ReverseAssociation("NmsDs")]
    private readonly EntityHolder<NmsMain> _nmsMain = new EntityHolder<NmsMain>();
    [ReverseAssociation("NmsDs")]
    private readonly EntityHolder<NmsStatus> _nmsStatus = new EntityHolder<NmsStatus>();


    #endregion
   
    #region Properties

    public NmsMain NmsMain
    {
      get { return Get(_nmsMain); }
      set { Set(_nmsMain, value); }
    }

    public NmsStatus NmsStatus
    {
      get { return Get(_nmsStatus); }
      set { Set(_nmsStatus, value); }
    }


    public int NmsDsId
    {
      get { return Get(ref _nmsDsId, "NmsDsId"); }
      set { Set(ref _nmsDsId, value, "NmsDsId"); }
    }

    public string ItemNumber
    {
      get { return Get(ref _itemNumber, "ItemNumber"); }
      set { Set(ref _itemNumber, value, "ItemNumber"); }
    }

    public string ProductName
    {
      get { return Get(ref _productName, "ProductName"); }
      set { Set(ref _productName, value, "ProductName"); }
    }

    public string XBatch
    {
      get { return Get(ref _xBatch, "XBatch"); }
      set { Set(ref _xBatch, value, "XBatch"); }
    }

    public string Uom
    {
      get { return Get(ref _uom, "Uom"); }
      set { Set(ref _uom, value, "Uom"); }
    }

    public string Batch
    {
      get { return Get(ref _batch, "Batch"); }
      set { Set(ref _batch, value, "Batch"); }
    }

    public string Amount
    {
      get { return Get(ref _amount, "Amount"); }
      set { Set(ref _amount, value, "Amount"); }
    }

    public string Retest
    {
      get { return Get(ref _retest, "Retest"); }
      set { Set(ref _retest, value, "Retest"); }
    }

    public string Expiry
    {
      get { return Get(ref _expiry, "Expiry"); }
      set { Set(ref _expiry, value, "Expiry"); }
    }

    public System.Nullable<System.DateTime> InsDate
    {
      get { return Get(ref _insDate, "InsDate"); }
      set { Set(ref _insDate, value, "InsDate"); }
    }

    public System.Nullable<decimal> TotalFromFile
    {
      get { return Get(ref _totalFromFile, "TotalFromFile"); }
      set { Set(ref _totalFromFile, value, "TotalFromFile"); }
    }

    /// <summary>Gets or sets the ID for the <see cref="NmsMain" /> property.</summary>
    public System.Nullable<int> NmsMainId
    {
      get { return Get(ref _nmsMainId, "NmsMainId"); }
      set { Set(ref _nmsMainId, value, "NmsMainId"); }
    }

    /// <summary>Gets or sets the ID for the <see cref="NmsStatus" /> property.</summary>
    public System.Nullable<int> NmsStatusId
    {
      get { return Get(ref _nmsStatusId, "NmsStatusId"); }
      set { Set(ref _nmsStatusId, value, "NmsStatusId"); }
    }

    #endregion
  }

Okay, I think I see the problem:

[quote user="Agraha"]

  [Table("NMS_MAIN", IdColumnName="Nms_Main_Id", Schema="SUPPLYCHAIN")]
  public partial class NmsMain : Entity<int>
  {
    [Column("NMS_MAIN_ID")]
    private int _nmsMainId;

[/quote]

This causes both the Id property (implicit in Entity base class) and the _nmsMainId field (aka the NmsMainId property) to back onto the same column (NMS_MAIN_ID), and LightSpeed gets confused.

What you need to do is delete the NmsMainId property from your entity model.  You don't need it because the ID is available through the Id property.  (The Id property isn't shown in the model because it's inherent in the fact of being a LightSpeed entity: it's declared on the Entity<TId> base class.)  Once you've done that, only the Id will be mapped to the NMS_MAIN_ID column, and the problems caused by the duplicate mapping should go away.

It looks like you'll have similar issues with other entities in your model e.g. the NmsDs class has a duplicate mapping via its NmsDsId property.  You'll have to delete these properties in those other entities as well.

Normally the designer should figure out that NMS_MAIN_ID (etc.) was the primary key column and not create a NmsMainId property (only the Identity Column Name override), so this error wouldn't occur, but it's possible something went awry in this case.

Greetings, Ivan! It is working just perfectly now. Thank you a million times.

            Agraha