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
|
Hello, I have a table in an existing database which does not have an ID column and none of the columns are unique. What makes a record unique is a combination of two columns. I have created an entity in my application model which represents that table and its Identity Column Name is blank since there is no such column. When I try to run selects against that entity though, I get an exception saying: ORA-06550: line 5, column 22:
so it is looking for the ID column. I can not alter the database table since many other applications run off that table. So how can I get around this?
Thanks
Susan
|
|
|
Hello Susan, LightSpeed does not support composite keys: it depends on there being a single column it can use as an identifier (by default named Id, but this can be overridden using Identity Column Name). The only way to get your scenario to work under LightSpeed is to add a unique column that LightSpeed can use as its identity. Other applications that run off this table would presumably be able to ignore this column, though if those other apps also update the table then you would need to ensure that it gets populated without having to change the other apps. I think in Oracle this would be done using an INSERT trigger, though there might be a simpler method that I'm not aware of. (You would of course also need to create unique values for existing records when creating the column.) Another rather ghastly possibility, if touching the existing table is absolutely not an option, ***might*** be to create an updateable view which combines the two composite key columns into a single Id column. I am not sure how this could handle inserts though, as it would somehow need to crack the supplied Id into two parts in order to update the underlying table. You might be able to work around this by also surfacing both composite key columns and in your entity ensuring that the corresponding properties are appropriately set (so that the view can discard the Id on insert, but it will be magically correct when selecting back). But this approach seems fraught with peril and my Oracle knowledge is nowhere near good enough to be sure whether it is even possible. |
|
|
Hi Ivan, thanks for the suggestions. I actually decided to go with the second option which is to link my data Entity to a view and have the view select rownum, in addition to the rest of the fields from my table(the one with the composite key). So I end up with a view which has a column called ID and it's unique. See I really only use that table to select - I never need to update it from my application. So I created the Entity and then in the Table name property I specified my view name and in the Identity_column_Name I specified ID since that is the name of teh column in my view. But the first strange thing is that if I right-click on my model and pick Update database , one ofthe changes is to create a table with a name = to my view name which makes me believe that it does not look into views but only at tables in my DB. Also if I ignore that and run my application and run a LINQ query against that entity such as : var fieldTagRecords = from fieldTagRecord in uow.VqEmProcedureDatas select fieldTagRecord; I get an exception:
Unable to materialize field [RecordKey] on type [EnvironmentalMonitoring.Models.VqEmProcedureData]. Check your table has an Id column and that your mappings are correct. See inner exception for details. Record_key is the first column after the ID column in my view.
So I guess the more general question here is :
How can I make a dataEntity which is linked to a view given that the view has a unique column. Thanks very much for helping me out Susan |
|
|
Hello Susan, You are right, the designer considers only tables when deciding whether an entity type needs to have a backing table created for it. This is because views are non-synchronisable -- e.g. if we had to add a column to a view, how would we know how to define that column? However we could obviously do better in terms of exclusing view-backed entities from synchronisation and I have logged a bug for us to improve this. In the meantime, since you don't need to save entities of this type, you might be able to exclude the view-backed entity from synchronisation by marking it as Transient. This has the side-effect of excluding the entity from synchronisation. However please test it -- it is possible marking the entity as Transient will interfere with loading entities from the database. (It will *definitely* interfere with saving it, so this approach will *not* work in the general case.) Regarding the exception when querying, can you check that the RecordKey field has the correct Column Name setting please? Your view column name appears to have an underscore in it (RECORD_KEY) so the column name does need to be specified. We should do this automatically when you drag but just worth a quick check. Assuming the Column Name is correctly set, could you post: * The inner exception associated with the exception you get. * The CREATE VIEW statement for your view (and the CREATE TABLE statement for the backing table). * The C# code for the entity (you can copy this from the .lsmodel.cs generated file). If any of this is sensitive then you can email it via the contact form (About Us > Contact Us). Thanks! |
|