Invalid Column name Error - Please Advise

Good Day,

I am new to Lightspeed and I am experiencing an issue only with certain tables. I think the issue is related to how lightspeed is camel casing the column names when mapping to properties as the code is being generated. I have the latest build from 9/27/2012.

For example, I have a table with a Column "EMP" which gets generated as "Emp" and while there are no Build errors, there are runtime SQL exceptions stating: "Invalid column name: "Emp". Another example is "ItemSKUNumber" becomes "ItemSkuNumber", which is also invalid within the same table at runtime. I read in the forums that I can customize my code generation template and I can see in FieldProperties.vm where the CamelCase method is called, but I am unsure how I should modify this file to address this issue. In essence, I want the table column name and property to match, with the private members to just be _. Any ideas on how to resolve this would be appreciated, because while I am working with a small subset of tables, there are possibly hundreds of more tables I would need to include in my model and I want to avoid manually fixing each table modeled into its class.

Thank you and have a good day,

Albert

Hi Albert,

Check that the "Column Name" property has been assigned the value of the actual column name in SQL by inspecting the properties for the Entity Property in the designer. Column Name is used to map from the name of the field we generate to the underlying SQL column name when creating queries.

Did you create these tables by dragging them on from the server explorer? If so the column name property should be being assigned automatically - let us know if this does not appear to be the case!

Jeremy

Good Day Jeremy,

Yes, I just did a simple drag and drop from the server explorer. Here is a sql generated create script for an example table.

CREATE TABLE [dbo].[_acAddOnItemDeletes2]( ID int IDENTITY(1,1),
[ContractKey] int NULL, [LineItemKey] bigint NULL, [UnitSystemKey] int NULL, [MiscID] varchar NULL, [EffectiveDate] [datetime] NULL, [StartDate] [datetime] NULL, [StopDate] [datetime] NULL, [SOLineItemKey] bigint NULL, [LineItemGroup] varchar NULL, [ItemKey] int NULL, [Description] varchar NULL, [Quantity] numeric(12,3) NULL, [Cost] numeric(16,3) NULL, [Price] numeric(16,2) NULL, [LocationKey] int NULL, [EmployeeKey] int NULL, [DateLastEdited] [datetime] NULL, [ServiceChangeKey] int NULL ) ON [PRIMARY]

After I drag and drop.

MiscID becomes MiscId and generates the runtime error mentioned before, the same with SOLineItemKey which becomes SoLineItemKey.

Here is the class that LS generates.

using System;

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

namespace LSObjects { [Serializable] [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")] [System.ComponentModel.DataObject] public partial class _acAddOnItemDeletes2 : Entity { #region Fields

private System.Nullable<int> _contractKey;
private System.Nullable<long> _lineItemKey;
private System.Nullable<int> _unitSystemKey;
[ValidateLength(0, 80)]
private string _miscId;
private System.Nullable<System.DateTime> _effectiveDate;
private System.Nullable<System.DateTime> _startDate;
private System.Nullable<System.DateTime> _stopDate;
private System.Nullable<long> _soLineItemKey;
[ValidateLength(0, 20)]
private string _lineItemGroup;
private System.Nullable<int> _itemKey;
[ValidateLength(0, 100)]
private string _description;
private System.Nullable<decimal> _quantity;
private System.Nullable<decimal> _cost;
private System.Nullable<decimal> _price;
private System.Nullable<int> _locationKey;
private System.Nullable<int> _employeeKey;
private System.Nullable<System.DateTime> _dateLastEdited;
private System.Nullable<int> _serviceChangeKey;

#endregion

#region Field attribute and view names

/// <summary>Identifies the ContractKey entity attribute.</summary>
public const string ContractKeyField = "ContractKey";
/// <summary>Identifies the LineItemKey entity attribute.</summary>
public const string LineItemKeyField = "LineItemKey";
/// <summary>Identifies the UnitSystemKey entity attribute.</summary>
public const string UnitSystemKeyField = "UnitSystemKey";
/// <summary>Identifies the MiscId entity attribute.</summary>
public const string MiscIdField = "MiscId";
/// <summary>Identifies the EffectiveDate entity attribute.</summary>
public const string EffectiveDateField = "EffectiveDate";
/// <summary>Identifies the StartDate entity attribute.</summary>
public const string StartDateField = "StartDate";
/// <summary>Identifies the StopDate entity attribute.</summary>
public const string StopDateField = "StopDate";
/// <summary>Identifies the SoLineItemKey entity attribute.</summary>
public const string SoLineItemKeyField = "SoLineItemKey";
/// <summary>Identifies the LineItemGroup entity attribute.</summary>
public const string LineItemGroupField = "LineItemGroup";
/// <summary>Identifies the ItemKey entity attribute.</summary>
public const string ItemKeyField = "ItemKey";
/// <summary>Identifies the Description entity attribute.</summary>
public const string DescriptionField = "Description";
/// <summary>Identifies the Quantity entity attribute.</summary>
public const string QuantityField = "Quantity";
/// <summary>Identifies the Cost entity attribute.</summary>
public const string CostField = "Cost";
/// <summary>Identifies the Price entity attribute.</summary>
public const string PriceField = "Price";
/// <summary>Identifies the LocationKey entity attribute.</summary>
public const string LocationKeyField = "LocationKey";
/// <summary>Identifies the EmployeeKey entity attribute.</summary>
public const string EmployeeKeyField = "EmployeeKey";
/// <summary>Identifies the DateLastEdited entity attribute.</summary>
public const string DateLastEditedField = "DateLastEdited";
/// <summary>Identifies the ServiceChangeKey entity attribute.</summary>
public const string ServiceChangeKeyField = "ServiceChangeKey";


#endregion

#region Properties



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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

#endregion

}

///

/// Provides a strong-typed unit of work for working with the acObjects model. ///

[System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")] public partial class acObjectsUnitOfWork : Mindscape.LightSpeed.UnitOfWork {

public System.Linq.IQueryable<_acAddOnItemDeletes2> _acAddOnItemDeletes2s
{
  get { return this.Query<_acAddOnItemDeletes2>(); }
}

}

}

Just create a context and try to access that objects in that entity, and it will error out at runtime, but the compiler sees it just fine.

Thank you for your time, I hope this helps,

Albert

Thanks for the extra detail - which database provider are you targetting?

Jeremy

MS SQL Server 2008 R2.

Thank you,

-- Albert

Do you have a setting forcing case sensitivity?

e.g. Presumably if you load up Management Studio and issue an adhoc query like this then it will fail?

select [_acAddOnItemDeletes2].[miscid] from _acAddOnItemDeletes2

Jeremy

yes sir.

We do have case sensitivity.

Albert

That query will fail in management studio for us.

Thanks for the clarification. Unfortunately for now you will need to manually set the column name for each column where this is an issue as our schema extractor is not detecting for this case.

Jeremy

Thank you Jeremy for your help. So there is no way to use the existing code templates to workaround this issue? Potentially there could be hundreds of tables in my domain model, that this could be a real problem for me if I have to go through each table, column by column.

Please Advise.

Thanks,

Albert

Hi Albert,

No unfortunately not. I can certainly appreciate this makes it fairly unworkable with a large number of tables and columns, I wish I had better news for you but unfortunately we dont detect for this condition at this time. I have made a note of this so we will certainly look at improving this as a future enhancement.

Jeremy

Jeremy,

Understood. Thank you for looking into this.

Albert