This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
I am working with an existing schema, which I can't modify, where a table has three columns and 2 of them form a composite primary key. I have tried the methods described both automatically (http://www.mindscape.co.nz/forums/Post.aspx?ThreadID=3197&PostID=10555) and by hand (http://www.mindscape.co.nz/blog/index.php/2009/12/22/composite-keys-in-lightspeed-3/), and yet I still cannot do a simple query on the table without Lightspeed attempting to find an "ID" column. The simple fact is that there is no ID column, nor can I add any other tables to the schema. How can I access this table? I've searched through the forums and documentation without any luck. I've tried assigning a Transient IDColumn, but the generated query still attempts to find the column in the real database. |
|
|
It's hard to diagnose the problem without seeing what you are currently trying. It sounds like the entity is still using an Int32 identity rather than the composite key identity, but without seeing your .lsmodel or the generated code it's not possible to be sure why this is happening. The easiest way to get what you want should be to drag the table from the Server Explorer onto the designer. This will put the PK properties into the "Keys" box and the remaining property into the "Entity Properties" box, and will generate an appropriate entity definition. If that doesn't work, show us your entity definition and your query. |
|
|
Hi Ivan, The easiest way to get what you want should be to drag the table from the Server Explorer onto the designer. This will put the PK properties into the "Keys" box and the remaining property into the "Entity Properties" box, and will generate an appropriate entity definition Do you mean that when looking at my entity in the designer, I should only have one column under "properties" and the other two columns under "identity"? Looking at the LightSpeed Model window, under "Entity Properties" I have all 3 columns, and then under "Keys" I have the 2 columns that should form the composite index. Here's the main Entity class
[Serializable] [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")] [System.ComponentModel.DataObject] [Table("OCALA_STRUCTURE_LOOKUP")] public partial class OcalaStructureLookup : Entity<OcalaStructureLookupId> { #region Fields [Column("WORK_REQUEST_NAME")] [ValidatePresence] [ValidateLength(0, 10)] private string _workRequestName; [Column("WORK_LOCATION_NUMBER")] [ValidateLength(0, 20)] private string _workLocationNumber; [Column("POLE_NUMBER")] [ValidateLength(0, 10)] private string _poleNumber; #endregion #region Field attribute and view names /// <summary>Identifies the WorkRequestName entity attribute.</summary> public const string WorkRequestNameField = "WorkRequestName"; /// <summary>Identifies the WorkLocationNumber entity attribute.</summary> public const string WorkLocationNumberField = "WorkLocationNumber"; /// <summary>Identifies the PoleNumber entity attribute.</summary> public const string PoleNumberField = "PoleNumber"; #endregion #region Properties public string WorkRequestName { get { return Get(ref _workRequestName, "WorkRequestName"); } set { Set(ref _workRequestName, value, "WorkRequestName"); } } public string WorkLocationNumber { get { return Get(ref _workLocationNumber, "WorkLocationNumber"); } set { Set(ref _workLocationNumber, value, "WorkLocationNumber"); } } public string PoleNumber { get { return Get(ref _poleNumber, "PoleNumber"); } set { Set(ref _poleNumber, value, "PoleNumber"); } } #endregion } And the automatically generated composite key struct
[Serializable] [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")] public partial struct OcalaStructureLookupId {
public OcalaStructureLookupId( string workRequestName , string workLocationNumber ) { _workRequestName = workRequestName; _workLocationNumber = workLocationNumber; }
#region Fields
private readonly string _workRequestName; private readonly string _workLocationNumber;
#endregion
#region Properties
public string WorkRequestName { get { return _workRequestName; } } public string WorkLocationNumber { get { return _workLocationNumber; } }
#endregion
#region Dictionary support
public override int GetHashCode() { int hashCode = 0; hashCode = 19 * hashCode + (_workRequestName == null ? 0 : _workRequestName.GetHashCode()); hashCode = 19 * hashCode + (_workLocationNumber == null ? 0 : _workLocationNumber.GetHashCode()); return hashCode; }
#endregion } |
|
|
[quote user="aanandhan"]Do you mean that when looking at my entity in the designer, I should only have one column under "properties" and the other two columns under "identity"?[/quote] Yes, that's right. Not sure why it's bringing in the other two columns as properties, or whether this has anything to do with your problem, but you should delete the duplicate columns from the Properties section. (We'd be interested to get hold of your CREATE TABLE statement to see if we can reproduce the duplication.) Can you try that and let us know if you still see the Id column in the generated SQL? If so, can you post your query code? Thanks!
|
|
|
Also, the specific error I'm getting is this:
Oracle.DataAccess.Client.OracleException: ORA-06550: line 20, column 34: PLS-00302: component 'ID' must be declared ORA-06550: line 20, column 11: PL/SQL: ORA-00904: "OCALA_STRUCTURE_LOOKUP"."ID": invalid identifier ORA-06550: line 4, column 1: PL/SQL: SQL Statement ignored.
I tried setting the context.Logger to new TraceLogger(), but I'm not seeing the actual SQL call being output anywhere. |
|
|
Hi, Removing the two columns as properties doesn't appear to make a difference. Here is the CREATE TABLE CREATE TABLE PROCESS.OCALA_STRUCTURE_LOOKUP ( WORK_REQUEST_NAME VARCHAR2(10 BYTE) NOT NULL, WORK_LOCATION_NUMBER VARCHAR2(20 BYTE), POLE_NUMBER VARCHAR2(10 BYTE) ) TABLESPACE PROCESSFW PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( ..... ) LOGGING How can I see the generated SQL from Lightspeed? |
|
|
With TraceLogger, the SQL should be being output to the Visual Studio Output window, or to any other trace listeners if configured. |
|
|
Odd. It doesn't show up under Debug or Test Output windows (i'm running the code through a unit test using TestDriven.Net) |
|
|
By the way, as part of deleting those duplicate properties, you'll need to copy across the Column Name settings to the corresponding identity properties. It looks like they haven't been picked up when inferring the composite key. |
|
|
[quote user="ivan"] By the way, as part of deleting those duplicate properties, you'll need to copy across the Column Name settings to the corresponding identity properties. It looks like they haven't been picked up when inferring the composite key. [/quote] I did that as well as part of deleting the original properties. The OcalaStructureLookupId class now has [Column("WORK_REQUEST_NAME")] private readonly string _workRequestName [Column("WORK_LOCATION_NUMBER")] private readonly string _workLocationNumber |
|
|
Under TestDriven.NET, you can use ConsoleLogger instead of TraceLogger and it will appear in the Output > Test window. |
|
|
Here is my .lsmodel http://dl.dropbox.com/u/85953/OcalaTelventModel.lsmodel
|
|
|
[quote user="ivan"] Under TestDriven.NET, you can use ConsoleLogger instead of TraceLogger and it will appear in the Output > Test window. [/quote] That's what I thought..I tried it originally and didn't get anything. Is there anything besides the following?
LightSpeedContext<OcalaTelventModelUnitOfWork> context = new ..... context.Logger = new ConsoleLogger();
// Testing code here var unitOfWork = context.CreateUnitOfWork(); var item = unitOfWork.OcalaStructureLookups.First(); Assert.NotNull(item); |
|
|
No, that looks fine. You shouldn't need any more than that. But based on your code I think I've found the issue. It looks like there's a bug with paging queries (including First()) over composite keys on databases that use RowNumber-column-based paging (including Oracle). Try changing your query to unitOfWork.OcalaStructureLookups.ToList().First() -- this will be inefficient because it downloads all the entities, but it should keep you going until we can get you a fix. |
|
|
Please let me know if there's any other information I can provide to help solve this problem :) |
|
|
[quote user="ivan"] No, that looks fine. You shouldn't need any more than that. But based on your code I think I've found the issue. It looks like there's a bug with paging queries (including First()) over composite keys on databases that use RowNumber-column-based paging (including Oracle). Try changing your query to unitOfWork.OcalaStructureLookups.ToList().First() -- this will be inefficient because it downloads all the entities, but it should keep you going until we can get you a fix. [/quote] Ahh! Yes that works! Thank you :) I actually want to do a proper query on the code anyway, not just a First(). I thought it would be simpler to test like this until the generated sql was working, but I guess I picked the wrong method :) |
|
|
[quote user="ivan"] No, that looks fine. You shouldn't need any more than that. But based on your code I think I've found the issue. It looks like there's a bug with paging queries (including First()) over composite keys on databases that use RowNumber-column-based paging (including Oracle). Try changing your query to unitOfWork.OcalaStructureLookups.ToList().First() -- this will be inefficient because it downloads all the entities, but it should keep you going until we can get you a fix. [/quote] FYI, it appears that .Single() is also broken. |
|
|
Cool, glad to hear that will get you going for now. We've located the problem and it will be fixed in the next nightly build (available for download from about 1200 GMT). This will also fix Single: the issue is generic to any query that requires paging in the database e.g. Skip or Take. In the meantime, if you do need a paging query, then you should be able to work around the bug by adding an explicit OrderBy clause e.g. unitOfWork.OcalaStructureLookups.OrderBy(o => o.Id.WorkRequestName).First() Thanks for drawing our attention to this issue! |
|