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
|
Hi guys, As discussed I have a property of type TimeSpan and a column of type time(0). Currently when I run my tests I get the following exception: failed: Mindscape.LightSpeed.LightSpeedException : Unable to materialize field [DefaultSleepTime] on type [FrogParking.Model.Entities.Organization]. Check your table has an Id column and that your mappings are correct. See inner exception for details.
I'll give it a run with an int and see how that goes - just posting here so you remember :) Cheers, James |
|
|
Hey guys, Chucking it in a bigint got me half off the way but it didn't work for nullable types. I ended up just using a DateTime on both sides and then just using the TimeOfDay - I don't need lots of precision so that should work fine :) Cheers, James |
|
|
Can you expand on "didn't work"? Even if you're going to go with the DateTime approach, I'd like to fix the "official" TimeSpan mapping if there's something wrong with it. |
|
|
Hey Ivan, This is what gets spat our with a TimeSpan? and a bigint in the db:
----> System.InvalidCastException : Object cannot be cast from DBNull to other types. at Mindscape.LightSpeed.LightSpeedException.(Exception , String , Object[] ) at Mindscape.LightSpeed.Model.FieldModel.Materialize(IDataProviderAdapter , Object , Object[] , Int32& ) at Mindscape.LightSpeed.Mapping.EntityLoader.(UnitOfWorkBase , String , Entity , Object[] , ) at Mindscape.LightSpeed.Mapping.EntityLoader.LoadEntityFromReader(AliasedTypeModel , Object , Boolean ) at ..(AliasedTypeModel , QueryExpression , Order , Group , String , Boolean , Boolean ) at ..(LightSpeedContext , List`1 , QueryExpression , Order , Group , String , Boolean , Boolean ) at ..(AliasedTypeModel , IList , String , Boolean ) at ..(TypeModel , Query , IList ) at ..(Query , TypeModel , IList ) at ..(Query , IList ) at Mindscape.LightSpeed.UnitOfWork.Find(Query query, IList results) at Mindscape.LightSpeed.UnitOfWorkBase.Find(Query query) at Mindscape.LightSpeed.Linq.Plan.SingleQueryPlan.ExecuteImmediate(IUnitOfWork unitOfWork, Type returnType) at Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute(Expression expression) at Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Execute(Expression expression) at Mindscape.LightSpeed.Linq.LinqQuery`1.GetEnumerator() ...
--InvalidCastException at System.DBNull.System.IConvertible.ToInt64(IFormatProvider provider) at System.Convert.ToInt64(Object value, IFormatProvider provider) at Mindscape.LightSpeed.Model.FieldModel.<>c__DisplayClassb3.<CreateValueSetter>b__71(IDataProviderAdapter , Object , Object[] , Int32 ) at Mindscape.LightSpeed.Model.FieldModel.Materialize(IDataProviderAdapter , Object , Object[] , Int32& ) Hopefully that helps :)
|
|
|
Hi, I am experienceing a similar issue. My entity has a property of type TimeSpan. On SQL Server 2008 I am storing it on column of type Time(0). I've also tried Time(7) but receive this error: Operand type clash: bigint is incompatible with time How do I get around this problem? Thanks. |
|
|
Did anyone find a simple, workable solution, to mapping to a SQL Server 2008 Time(7) type? I have many Time(7) columns in my database, but am unable to map them to Timespan or any other entity type. |
|
|
Hi Mark, I am looking at adding in native support for this into the SQL Server 2008 provider. All going well I should be able to push this out soon so will post an update once this has progressed a bit further :)
|
|
|
Hi Jeremy, That is fantastic news. Don't mean to be a pain, and I realize things like this take time, but do you have a time frame. I just need to decide whether to do a workaround for the interim, or wait for the full support. Thanks Mark |
|
|
Hi Mark, Ive already added the support for this (and it works great!) but we need a way of allowing you to control the behavior to allow for backwards compatibility. Currently the expectation is that you would be using a bigint (long) to back a TimeSpan and its quite likely many people have gone down this route (even with SQL 2008) so I dont want this new behavior to be the default. So all going well this should be available within a day or so in the nightlies :) This will obviously be specific to the SqlServer2008 provider and will not impact any other providers. Will keep you posted!
|
|
|
Hi Mark, I have committed the initial updates for this and they will be available in the next nightly build for you. Designer support will follow on from this later but for now you can add a [Column(ConverterType = typeof(Mindscape.LightSpeed.FieldConverters.SqlServerTimeFieldConverter))] attribute to your field declarations. If you are generating these via the designer currently you may need to set those specific fields to Generation: None and then move the field and property declarations into a partial class for now.
|
|
|
Hi Mark, We have added in designer support for this now and this will be available in the next nightly build for you.
|
|
|
That is great news, thanks. Will download it tonight. Best regards Mark |
|
|
Jeremy, I'm getting an error when I try and update a time field. I'm using the most recent nightly build and I of course am using the SQL 2008 provider. Help!!! Jason |
|
|
Hi Jason, Are you able to elaborate on the issue you are having? Also can you check that the field in question has a [Column(ConverterType = typeof(Mindscape.LightSpeed.FieldConverters.SqlServerTimeFieldConverter))] attribute applied to it in the generated code.
|
|
|
Thank you for your rapid response, Jeremy! I'm truly in a bind, so I very much appreciate this. I can confirm the code you posted is present. See the below screenshot. Thank you, Jason |
|
|
Whats the issue you are having?
|
|
|
Jeremy, To elaborate then... I am attempting to do a routine insert into the view, which I've done plenty of times prior. I've added a field to the underlying database table, which is in the 'time' format. This is incidentally the first time I've used this field type using Lightspeed. I deleted the database view, refreshed my sources, then added it back into the Lightspeed interface. I then proceeded to mark all fields which do not get updated/inserted with "Load Only = true". I compiled, and it was successful. I ran it, and got this error. I have not had a successful test. I also tried manually entering the underlying field name in the "column name" area of the designer. This rendered the project unable to compile, and gave me the error that it was a duplicate field, oddly enough. I did thorough searching for the duplicate field and found no such thing. Please advise... Thank you again, Jason |
|
|
What are the details of the error itself though - can you provide a stack trace of the error you are getting at runtime?
|
|
|
Jeremy, Absolutely. The following is the stack trace...
|
|
|
Hi Jason, Unfortunately that is just showing there was an error returned from SQL Server. Can you provide some detail about that please. Alternatively if its possible for you to send over a small repro project with some sample data for this we can have a look into this locally.
|
|
|
Jeremy, This should be what you're looking for then... System.Data.SqlClient.SqlException was caught
Message=Invalid column name 'OrderImportTime'.
Source=.Net SqlClient Data Provider
ErrorCode=-2146232060
Class=16
LineNumber=8
Number=207
Procedure=""
Server=.
State=1
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at ...?()
at Mindscape.LightSpeed.Profiling.Interceptor.ExecuteCommand(Func
|
|
|
Do you have a column called OrderImportTime in your view? Presumably this is the name of the property you have added to the entity and it appears to be missing from the view or table being updated on. One suggestion I would make would be to log the SQL being sent so you can execute this in Management Studio locally to replicate the issue in SQL and diagnose it from there. This is usually highly useful when you run into these types of issues for identifying what is going on. You can either do this using SQL Profiler or by attaching a logger class to your LightSpeedContext as described here: http://www.mindscapehq.com/documentation/lightspeed/Testing-and-Debugging/Logging
|
|
|
Jeremy, My context logger gave me the following... INSERT INTO VwOrderFull ( OrderCreateDate, OrderCreateTime, OrderDeptId, OrderImportDate, OrderImportSourceId, OrderImportTime, OrderImportUserId, OrderIsCapRuleApplied, OrderIsRoundingRuleApplied, OrderItemId, OrderLocation, OrderLocationAtImport, OrderNumber, OrderQtyCurrent, OrderQtyCurrentAtImport, OrderQtyOriginal, OrderRecordCode, OrderStoreId ) VALUES ( '6/27/2012 12:00:00 AM', '00:00:00', 2, '7/3/2012 12:00:00 AM', 1, '15:37:09.0351455', 1, False, False, 15157, 'C', 'C', 1139, 25, 25, 25, 579570, 21 ); SELECT scope_identity() --> Time: 0 ms A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in Mindscape.LightSpeed Now... I did as you suggested, and copied the query into SQL Management Studio to run it. I noticed it was yielding a syntax error on the field err'ing out. When I took a look at the underlying view, it was of course there, but I did in fact notice something different. I now see the issue and the following are my observations:
Solved. :) Great job walking me through this sequence of troubleshooting. What are your thoughts from here regarding a fix? Thank you, Jason |
|
|
Hi Jason, All good - I am a bit unclear about what you have mentioned above though: "Every field which contains an underscore, shows the property within visual studio as not having an underscore. When the SQL is generated, the underscore is restored properly, and no issue ensues for the insert/update." Yet your SQL statement is: "INSERT INTO VwOrderFull ( OrderCreateDate, OrderCreateTime, OrderDeptId, OrderImportDate, OrderImportSourceId, OrderImportTime, OrderImportUserId, OrderIsCapRuleApplied, OrderIsRoundingRuleApplied, OrderItemId, OrderLocation, OrderLocationAtImport, OrderNumber, OrderQtyCurrent, OrderQtyCurrentAtImport, OrderQtyOriginal, OrderRecordCode, OrderStoreId ) VALUES ( '6/27/2012 12:00:00 AM', '00:00:00', 2, '7/3/2012 12:00:00 AM', 1, '15:37:09.0351455', 1, False, False, 15157, 'C', 'C', 1139, 25, 25, 25, 579570, 21 );" There are no fields with underscores in that statement. Did you fix up the SQL statement directly, if so what did you need to change to make it work?
|
|
|
Jeremy, Lol. Apparently, when I paste SQL into this forum submission editor, it takes my underscores out (More underscore issues? lol). Allow me to post a screenshot of what it is supposed to look like... (By the way, I tried using the 'Code Sample' functionality, but it would only insert the very first line into the pane) To answer your question however, I'll elaborate... When I have a view in SQL Server (any version), and that view contains fields which contain an underscore, those fields do not show an underscore when surfaced in the Lightspeed designer. Once I drag the view object onto the Lightspeed designer surface, it shows all of the fields but without an underscore. This is no problem at all. Normally, whenever I do inserts/updates against such a view which contains fields that have underscores, even though the designer shows the field to have no underscore, the resulting SQL from the logger shows it to have the underscore again, which is correct with regard to the exact mapping of that field from Lightspeed to the backing field of the view in SQL Server. The final observation made was that the mapping is correct for every field accept a 'time' field which contains an underscore.
|
|
|
Great - that makes more sense :) Check that you have a column name set for the entity property which matches the database name (so it should be the underscored version of the name). You should see that this has been set on the other fields which have an underscore. The column name is used where there is a mapping that needs to occur between the CLR property name and the database field name. Because the designer normalises the naming to match CLR naming standards we set a column name mapping when we import database fields which dont match the CLR name.
|
|
|
Jeremy, Put another way, the "OrderImportTime" SQL generated should actually be "Order_ImportTime". This is the only field which has its SQL not generated properly. The culprit is the SQL generation, due to the aforementioned reasons. Hope that helps! Thank you, Jason |
|
|
Jeremy, To address what you suggested... I've actually tried this. That's what I meant in one of my first posts with regard to what I've tried to remedy the situation. I'll submit here a screenshot of what I get when I implement that solution...
|
|
|
Jeremy, It also bares worth noting that all field types EXCEPT 'time', whose field name contains an underscore, map properly without the need to modify the auto-gen code.
|
|
|
Thanks, I will have a look into what might be up with that. In the meantime if you go to the generated code and manually set the column name in code on the existing Column attribute I am presuming this should solve the issue as a workaround for now. It would appear that there may be an issue with it trying to set a second column name attribute (one for the name, one for the convertor type for the Time data type), I will have a look into that as well and let you know if I can reproduce this here.
|
|
|
Jeremy, Where/how might I do that? I do not see this attribute located in the auto-gen code.
|
|
|
You have a [Column(ConverterType = typeof(Mindscape.LightSpeed.FieldConverters.SqlServerTimeFieldConverter))] on the field currently in the generated code. Add a Name = "Order_ImportTime" to this.
|
|
|
Jeremy, I may be missing something, but here is what I mean... Are you referring perhaps to a different location that the attribute is applied? Thank you, Jason |
|
|
Jeremy, I was able to implement what you proposed as a workaround for now. I just left off the explicit "Name" and declared the attribute parameter implicitly. Thank you very much for looking into this so quickly. It truly means a lot! Sincerely, Jason |
|
|
Thanks Jason, also as an update I have added a fix for the duplicate Column attribute being generated and this will be available in the next nightly build.
|
|
|
Jeremy, Thank you! Does it now function like all of the other field types, and I do not have to specify the field name explicitly, but rather the mapping name conversion now occurs automatically? |
|
|
Yes that right - it should be mapping the column name from the database automatically now (this was related to the other issue).
|
|
|
Wow, that was fast. Much appreciated Jeremy. Thank you, Jason |
|