Schema

Hi Guys,

I have two tables with the same name under two different Schemas howver when the sql is generated it is not prefixing the table name with Schema hence it's throqwing an error.  Do u know how to force the table name to be prefixed with the schema?

Thanks

In the designer, ensure that the entity's Schema property is set.

If you're coding entities by hand, use TableAttribute.Schema.

 Hi Ivan,

the entity's scheme property has been set when the data model is created. I also checked that TableAttribute.Schema is set correctly in the auto-generated model cs file.

Thanks

 

Hmm, it should be prefixing the table name in the SQL then.  Can you post the code of the two entities in question, and the query that isn't working correctly?

Here is code to insert a record to AP.ApFile. 

 

public void SaveAustPostFile(AustPostFileDto austPostFileDto)

{

using (var uow = lightSpeedContext.CreateUnitOfWork())

{

#if DEBUG

uow.Context.Logger = new TraceLogger();

#endif

using (var transaction = uow.BeginTransaction())

{

var test = new ApFile()

{

FileName = "TestFileName",

FileIdentifier = "TestFileID",

FileDate = DateTime.Now,

PrincipalName = "Test PN",

PrincipalNumber = 1,

StateCode = "NSW",

FileSequence = 1,

FileTotalAmount = 1,

RecordCount = 1,

ProcessedCount = 0,

CreatedBy = "Integration Test",

CreatedDate = DateTime.Now,

};

uow.Add(test);

uow.SaveChanges();

transaction.Commit();

}

}

}

 

The generated query does not have the schema :

 

INSERT INTO AP_FILE

(

  AP_FILE_ID,

  CREATED_BY,

  CREATED_DATE,

  FILE_DATE,

  FILE_IDENTIFIER,

  FILE_NAME,

  FILE_SEQUENCE,

  FILE_TOTAL_AMOUNT,

  MODIFIED_BY,

  MODIFIED_DATE,

  PRINCIPAL_NAME,

  PRINCIPAL_NUMBER,

  PROCESSED_COUNT,

  RECORD_COUNT,

  STATE_CODE

)

VALUES

(

  4111,

  'Integration Test',

  '5/04/2011 1:41:25 PM',

  '5/04/2011 1:41:25 PM',

  'TestFileID',

  'TestFileName',

  1,

  1,

  NULL,

  NULL,

  'Test PN',

  1,

  0,

  1,

  'NSW'

)

 

 

 

 

I just tried an equivalent Add and it is coming up with the schema for me.  Could you post the ApFile entity code please?  Thanks!  (Just the generated code for that entity will do fine; we don't need the .lsmodel or any other entities.)

[Serializable]

  [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")]

  [System.ComponentModel.DataObject]

  [Table("AP_FILE", IdColumnName="AP_FILE_ID", Schema="AP")]

  public partial class ApFile : Entity<int>

  {

    #region Fields

 

    [Column("FILE_NAME")]

    [ValidatePresence]

    [ValidateLength(0, 1000)]

    private string _fileName;

    [Column("FILE_IDENTIFIER")]

    [ValidatePresence]

    [ValidateLength(0, 20)]

    private string _fileIdentifier;

    [Column("FILE_DATE")]

    private System.DateTime _fileDate;

    [Column("PRINCIPAL_NUMBER")]

    private int _principalNumber;

    [Column("PRINCIPAL_NAME")]

    [ValidatePresence]

    [ValidateLength(0, 30)]

    private string _principalName;

    [Column("STATE_CODE")]

    [ValidatePresence]

    [ValidateLength(0, 3)]

    private string _stateCode;

    [Column("FILE_SEQUENCE")]

    private decimal _fileSequence;

    [Column("FILE_TOTAL_AMOUNT")]

    private int _fileTotalAmount;

    [Column("RECORD_COUNT")]

    private System.Nullable<decimal> _recordCount;

    [Column("PROCESSED_COUNT")]

    private System.Nullable<int> _processedCount;

    [Column("CREATED_BY")]

    [ValidatePresence]

    [ValidateLength(0, 61)]

    private string _createdBy;

    [Column("CREATED_DATE")]

    private System.DateTime _createdDate;

    [Column("MODIFIED_BY")]

    [ValidateLength(0, 61)]

    private string _modifiedBy;

    [Column("MODIFIED_DATE")]

    private System.Nullable<System.DateTime> _modifiedDate;

 

    #endregion

 

    #region Field attribute and view names

 

    /// <summary>Identifies the FileName entity attribute.</summary>

    public const string FileNameField = "FileName";

    /// <summary>Identifies the FileIdentifier entity attribute.</summary>

    public const string FileIdentifierField = "FileIdentifier";

    /// <summary>Identifies the FileDate entity attribute.</summary>

    public const string FileDateField = "FileDate";

    /// <summary>Identifies the PrincipalNumber entity attribute.</summary>

    public const string PrincipalNumberField = "PrincipalNumber";

    /// <summary>Identifies the PrincipalName entity attribute.</summary>

    public const string PrincipalNameField = "PrincipalName";

    /// <summary>Identifies the StateCode entity attribute.</summary>

    public const string StateCodeField = "StateCode";

    /// <summary>Identifies the FileSequence entity attribute.</summary>

    public const string FileSequenceField = "FileSequence";

    /// <summary>Identifies the FileTotalAmount entity attribute.</summary>

    public const string FileTotalAmountField = "FileTotalAmount";

    /// <summary>Identifies the RecordCount entity attribute.</summary>

    public const string RecordCountField = "RecordCount";

    /// <summary>Identifies the ProcessedCount entity attribute.</summary>

    public const string ProcessedCountField = "ProcessedCount";

    /// <summary>Identifies the CreatedBy entity attribute.</summary>

    public const string CreatedByField = "CreatedBy";

    /// <summary>Identifies the CreatedDate entity attribute.</summary>

    public const string CreatedDateField = "CreatedDate";

    /// <summary>Identifies the ModifiedBy entity attribute.</summary>

    public const string ModifiedByField = "ModifiedBy";

    /// <summary>Identifies the ModifiedDate entity attribute.</summary>

    public const string ModifiedDateField = "ModifiedDate";

 

 

    #endregion

 

    #region Relationships

 

    [ReverseAssociation("ApFile")]

    private readonly EntityCollection<ApTransactionMaster> _apTransactionMasters = new EntityCollection<ApTransactionMaster>();

    [ReverseAssociation("ApFile")]

    private readonly EntityCollection<ApTdDeciphaFile> _apTdDeciphaFiles = new EntityCollection<ApTdDeciphaFile>();

 

 

    #endregion

 

    #region Properties

 

    [System.Diagnostics.DebuggerNonUserCode]

    public EntityCollection<ApTransactionMaster> ApTransactionMasters

    {

      get { return Get(_apTransactionMasters); }

    }

 

    [System.Diagnostics.DebuggerNonUserCode]

    public EntityCollection<ApTdDeciphaFile> ApTdDeciphaFiles

    {

      get { return Get(_apTdDeciphaFiles); }

    }

 

 

    [System.Diagnostics.DebuggerNonUserCode]

    public string FileName

    {

      get { return Get(ref _fileName, "FileName"); }

      set { Set(ref _fileName, value, "FileName"); }

    }

 

    [System.Diagnostics.DebuggerNonUserCode]

    public string FileIdentifier

    {

      get { return Get(ref _fileIdentifier, "FileIdentifier"); }

      set { Set(ref _fileIdentifier, value, "FileIdentifier"); }

    }

 

    [System.Diagnostics.DebuggerNonUserCode]

    public System.DateTime FileDate

    {

      get { return Get(ref _fileDate, "FileDate"); }

      set { Set(ref _fileDate, value, "FileDate"); }

    }

 

    [System.Diagnostics.DebuggerNonUserCode]

    public int PrincipalNumber

    {

      get { return Get(ref _principalNumber, "PrincipalNumber"); }

      set { Set(ref _principalNumber, value, "PrincipalNumber"); }

    }

 

    [System.Diagnostics.DebuggerNonUserCode]

    public string PrincipalName

    {

      get { return Get(ref _principalName, "PrincipalName"); }

      set { Set(ref _principalName, value, "PrincipalName"); }

    }

 

    [System.Diagnostics.DebuggerNonUserCode]

    public string StateCode

    {

      get { return Get(ref _stateCode, "StateCode"); }

      set { Set(ref _stateCode, value, "StateCode"); }

    }

 

    [System.Diagnostics.DebuggerNonUserCode]

    public decimal FileSequence

    {

      get { return Get(ref _fileSequence, "FileSequence"); }

      set { Set(ref _fileSequence, value, "FileSequence"); }

    }

 

    [System.Diagnostics.DebuggerNonUserCode]

    public int FileTotalAmount

    {

      get { return Get(ref _fileTotalAmount, "FileTotalAmount"); }

      set { Set(ref _fileTotalAmount, value, "FileTotalAmount"); }

    }

 

    [System.Diagnostics.DebuggerNonUserCode]

    public System.Nullable<decimal> RecordCount

    {

      get { return Get(ref _recordCount, "RecordCount"); }

      set { Set(ref _recordCount, value, "RecordCount"); }

    }

 

    [System.Diagnostics.DebuggerNonUserCode]

    public System.Nullable<int> ProcessedCount

    {

      get { return Get(ref _processedCount, "ProcessedCount"); }

      set { Set(ref _processedCount, value, "ProcessedCount"); }

    }

 

    [System.Diagnostics.DebuggerNonUserCode]

    public string CreatedBy

    {

      get { return Get(ref _createdBy, "CreatedBy"); }

      set { Set(ref _createdBy, value, "CreatedBy"); }

    }

 

    [System.Diagnostics.DebuggerNonUserCode]

    public System.DateTime CreatedDate

    {

      get { return Get(ref _createdDate, "CreatedDate"); }

      set { Set(ref _createdDate, value, "CreatedDate"); }

    }

 

    [System.Diagnostics.DebuggerNonUserCode]

    public string ModifiedBy

    {

      get { return Get(ref _modifiedBy, "ModifiedBy"); }

      set { Set(ref _modifiedBy, value, "ModifiedBy"); }

    }

 

    [System.Diagnostics.DebuggerNonUserCode]

    public System.Nullable<System.DateTime> ModifiedDate

    {

      get { return Get(ref _modifiedDate, "ModifiedDate"); }

      set { Set(ref _modifiedDate, value, "ModifiedDate"); }

    }

 

    #endregion

  }

Thanks.  Unfortunately I'm still unable to reproduce the problem.  When I try your code I get the following generated SQL:

INSERT INTO AP.AP_FILE

which is obviously what you want and expect.

The only thing I can think of is if there is something in your configuration that I'm missing, but the only thing that should be able to interfere with the schema is LightSpeedContext.NamingStrategy.  Do you have a naming strategy specified in code or config?

Assuming that isn't it, can you provide us with a complete, buildable project (console application or NUnit test) that exhibits the problem?  We don't need the database, just the C# project.

Thanks Ivan. 

I use MultiSequenceNamingStrategy, and this is Oracle DB. I cannot provide the complete project due to the security restriction.

 

 

Can you check your MultiSequenceNamingStrategy.GetSchemaName implementation?  It may be returning null or String.Empty instead of defaultName.  You could also pop a breakpoint on it to check that the correct defaultName is being passed in.

Sorry, that method name should be MultiSequenceNamingStrategy.GetSchema (as you probably already guessed of course...!).

Thanks for the reply. DefaultName currently is null. where can I set this value?

objectName contains the full name of the sequence, so I am able to get the schema name from it as a walkaround. I am still insterested if we can set defaultname before GetSchema is invoked? thanks

defaultName should be coming from the TableAttribute.Schema (or LightSpeedContext.Schema if TableAttribute.Schema is not set).

But I think you may be looking at the wrong call to GetSchema.  You mentioned "the full name of the sequence," but the sequence is not the problem.  We need to know what GetSchema returns when it is called for the AP_FILE table.

Could you post your MultiSequenceNamingStrategy implementation?

Better still, could you post a small, complete (buildable) project (console application or NUnit) that exhibits the problem?  I realise you can't post your whole project and that is fine, we don't want to see the whole project anyway: you can pull out just the entity class (you can comment out the associations so it compiles on its own), configuration and test code into a standalone program, verify that the standalone program has the same problem as the real project, and send us just the standalone program.  (You can attach a zip file via the Options tab.)

You are correct that GetSchema returns empty string for tables in our current MultiSequenceNamingStrategy implementation. Thanks for your help.