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 have read on the blog that support is being added for sqlgeography, is this available in the current released version... If not when is this expected to be in as I'm keen on this feature. thanks in advance. |
|
|
Full support for SqlGeography is coming in LightSpeed 3. We can take a look at backporting the runtime support to LightSpeed 2.2 if that would be useful: you won't get the designer support but you'll be able to use SqlGeography fields via handwritten or partial classes. Let us know if this would be useful for you. |
|
|
Hi Ivan, Thanks for responding. When is LightSpeed 3 planned for release? At present I'm working on a prototype and don't plan a to Prod release for another 4 months... That said, I'd like to start experimenting with LightSpeed and for the prototype don't mind using the nightly build, does this currently include that feature? Thanks in advance Kamran
|
|
|
We're close to starting the LightSpeed 3 beta, and planning for a beta period of a few weeks. The nightly build doesn't yet include the SqlGeography feature but I'll see if we can get it added for you -- I will post back here shortly. |
|
|
Looking forward to it, thanks Ivan.
|
|
|
Another question on a slightly different topic, does LightSpeed provide support for Enums that are stored in a database as non int types?
|
|
|
No. LightSpeed expects enums to be represented as integral types in the database. (If you are asking about integral types other than int, e.g. byte or smallint, I believe this should work.) |
|
|
Okay, SqlGeography and SqlGeometry will be available in LightSpeed 2.x nightlies dated 6 Oct 2009 and above, available from about 1430 GMT. In order to use a SqlGeography or SqlGeometry field, you must decorate it with the new ValueFieldAttribute, e.g. [ValueField] // must have this! If you don't do this, LightSpeed will ignore the field, resulting in values not being loaded, and NULLs being inserted or updated (or errors if the columns are non-nullable). If you see these symptoms, check that ValueField has been applied (and is applied to the field not the property). LightSpeed 2.x does not include designer support for these types, so you will need to declare spatial fields in a partial class. You may be able to suppress designer synchronisation warnings by creating a property in the designer of type Object and setting its Generation to None (I have not tested this though!). Designer support will come in LightSpeed 3. Please let us know if you run into any problems. |
|
|
Will try this today, thanks Ivan, much appreciated.
|
|
|
Hi Ivan
I've had a go with the the following build Nightly Build for 07 Oct 2009 - LightSpeedExpress-20091007.msi
I am getting the following error: UdtTypeName property must be set for UDT parameters.
I found this link that indicates a potential fix: http://devlicio.us/blogs/sergio_pereira/archive/2008/06/11/udttypename-and-net-data-types-in-sql.aspx
Stacktrace below for you reference:
at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
at System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters)
at System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)
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 ..(IUnitOfWork , IDbCommand , )
at ..(IUnitOfWork , IDbCommand )
at ..(Entity , )
at ..(Entity , ICollection`1 )
at ..(Entity , List`1 )
at ..(UnitOfWork , IEnumerable`1 , IEnumerable`1 )
at Mindscape.LightSpeed.UnitOfWork.SaveChanges(Boolean reset)
at Mindscape.LightSpeed.UnitOfWorkBase.SaveChanges()
at LightSpeedPTTest.Program.Main(String[] args) in C:\dev\temp\LightSpeedPTTest\LightSpeedPTTest\Program.cs:line 32
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ExecutionContext.runTryCode(Object userData)
at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
|
|
|
Hi Ivan
I've had a go with the the following build Nightly Build for 07 Oct 2009 - LightSpeedExpress-20091007.msi
I am getting the following error: UdtTypeName property must be set for UDT parameters.
I found this link that indicates a potential fix: http://devlicio.us/blogs/sergio_pereira/archive/2008/06/11/udttypename-and-net-data-types-in-sql.aspx
Stacktrace below for you reference:
at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
at System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters)
at System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)
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 ..(IUnitOfWork , IDbCommand , )
at ..(IUnitOfWork , IDbCommand )
at ..(Entity , )
at ..(Entity , ICollection`1 )
at ..(Entity , List`1 )
at ..(UnitOfWork , IEnumerable`1 , IEnumerable`1 )
at Mindscape.LightSpeed.UnitOfWork.SaveChanges(Boolean reset)
at Mindscape.LightSpeed.UnitOfWorkBase.SaveChanges()
at LightSpeedPTTest.Program.Main(String[] args) in C:\dev\temp\LightSpeedPTTest\LightSpeedPTTest\Program.cs:line 32
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ExecutionContext.runTryCode(Object userData)
at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
|
|
|
Hi Ivan
I've had a go with the the following build Nightly Build for 07 Oct 2009 - LightSpeedExpress-20091007.msi
I am getting the following error: UdtTypeName property must be set for UDT parameters.
I found this link that indicates a potential fix: http://devlicio.us/blogs/sergio_pereira/archive/2008/06/11/udttypename-and-net-data-types-in-sql.aspx
|
|
|
That's strange -- we are setting the UdtTypeName and it's working in our test environment. Can you check that you have the LightSpeedContext.DataProvider set to SqlServer2008 and not to an earlier SqlServer* version? If so can you provide us with a small console project that reproduces the problem? Thanks! |
|
|
SQLite has a spatial add-on called spatilite. Would you also be able to use that? |
|
|
As far as I could see, spatialite is available only as a native package, rather than an extension to System.Data.SQLite (or its own ADO.NET provider). (One of the linked tutorials implied there was a .NET version but I couldn't find it.) If there's a way to bolt spatialite onto the side of System.Data.SQLite, or if there's a spatialite ADO.NET provider, then we'd be happy to take a look at this. (We'd probably need to update LightSpeed -- I don't think it would "just work.") If you could provide us with a link to the relevant downloads or instructions then that would be really handy. Thanks! |
|
|
Just wanted to update you guys, I got lightspeed working with the SqlGeography type. It was the dataprovider value that was incorrectly set. Thanks guys, it seems to work quite well, looking forward to the release of v3. |
|
|
Ivan, I was able to get Spatialite to work with no modification to the work that you have done with SqlServer 2008.Here is the code that I used to load the spatialite module with the connection that you have alread established with the sqlite lightspeed connection (under the hood System.Data.SQLite).I did have to create a view that returned the geometry column AsText or AsBinary that would then enable me to use the SQLServer Type Geometry object, I also decorated the field type as stated above.Is there any way to use linq to return the geometry column AsText or AsBinary so I don't have to create the views? using (LightSpeedModel1UnitOfWork uow = Repository.Context.CreateUnitOfWork()){IDbCommand cmd = uow.Context.DataProviderObjectFactory.CreateCommand();cmd.CommandText = "SELECT load_extension('libspatialite-1.dll');";uow.PrepareCommand(cmd);cmd.ExecuteNonQuery();IQueryable<SQLiteAndLinqTest.TractorRow> TR= (from t in uow.TractorRows select t);foreach (var row in TR){Console.WriteLine(row.Geom.ToString());}} P.S. If you want I can document the dll's needed and steps to repeat my actions as it took a day our so to figure this all out.
|
|
|
Instructions to get SQLiteSpatial working. 1. Download the latest System.Data.Sqlite Driver (Version 1.0.65 July 26, 2009) from the following URL: 2. Download the precompiled binaries for SpatialLite (Version 2.3.1). 4. I added the additional 3 linked dll that you will find in the zip file that you downloaded to my project as existing files and marked them to copy if newer. 5. I created a view of the original spatial table so I could return the geometry column as text (I.E. AsText(geomColumn) AS geom) full view would look like this... CREATE VIEW TractorRow I’m hoping that you know of a way to use link to query that column AsText or AsBinary so we don’t need to create this view. Your feedback would be appreciated. 6. I then used your examples to create the data model of my DataBase including the view table. The View Table was Empty when I dragged it onto the designer so I had to add the Entity Properties by hand as System.Data.SQLite driver didn’t like the view because I used asText which does not exist in SQLite (Don’t worry, once we load the Spatialite module all is good). 7. I modified the output cs file your code generates by changing the field and properties of the geometry column from the view table to be SqlGeography. I also added the [ValueField] decorator you mentioned. 8. I used the following code to load spatiality into the System.Data.SQLite DBConnection that your context loads and queried the DB... using (LightSpeedModel1UnitOfWork uow = Repository.Context.CreateUnitOfWork())
|
|
|
In step 4 above post should be 4 dll's... Sorry for the mistake. |
|
|
I'm getting an error when using Linq expressions for SqlGeography objects. I have a expression similar to this:
IEnumerable<Supplier> supplierInRange = from s in uow.Suppliers where s.Location.STDistance(location).Value <= rangeInMetres select s; return supplierInRange.ToList();
I get the following error:
at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractMethodCriteria(IList`1 rangeVariables, QuerySubexpressionCollection subexpressions, MethodCallExpression expression) at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore(IList`1 rangeVariables, QuerySubexpressionCollection subexpressions, Expression expression) at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(IList`1 rangeVariables, QuerySubexpressionCollection subexpressions, Expression expression) at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractMemberCriteria(IList`1 rangeVariables, QuerySubexpressionCollection subexpressions, MemberExpression member) at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ConvertToQueryExpression(IList`1 rangeVariables, QuerySubexpressionCollection subexpressions, Expression expression) at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractBinaryCriteria(IList`1 rangeVariables, QuerySubexpressionCollection subexpressions, BinaryExpression expression) at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore(IList`1 rangeVariables, QuerySubexpressionCollection subexpressions, Expression expression) at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(IList`1 rangeVariables, QuerySubexpressionCollection subexpressions, Expression expression) at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore(IList`1 rangeVariables, QuerySubexpressionCollection subexpressions, Expression expression) at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(IList`1 rangeVariables, QuerySubexpressionCollection subexpressions, Expression expression) at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore(IList`1 rangeVariables, QuerySubexpressionCollection subexpressions, Expression expression) at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(IList`1 rangeVariables, QuerySubexpressionCollection subexpressions, Expression expression) at Mindscape.LightSpeed.Linq.Sqo.Where.Evaluate(ExpressionVisitor visitor, MethodCallExpression expression) at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitMethodCall(MethodCallExpression exp) at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Visit(Expression exp) at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Build(Expression translation, LinqQueryProvider provider) at Mindscape.LightSpeed.Linq.LinqQueryProvider.GetExecutionPlan(Expression expression) 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() at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)....
Whereas the following code works fine:
List<Supplier> suppliersInRange = new List<Supplier>(); foreach (Supplier supplier in uow.Suppliers) { if (supplier.Location.STDistance(location) <= rangeInMetres) { suppliersInRange.Add(supplier); } } return suppliersInRange;
Any ideas?
|
|
|
STDistance is a client side method which exists on the SqlGeography object. In the context of the LINQ expression you are asking for this to be executed as part of the server query which is not supported (as the error indicates). In your second example, you are enumerating through a list of objects and then executing the method on those objects client side. So you could change your original query to be:
IEnumerable<Supplier> supplierInRange = from s in uow.Suppliers.ToList() where s.Location.STDistance(location).Value <= rangeInMetres select s; return supplierInRange.ToList(); but keep in mind that this will pull back the whole set of Suppliers first, and then will perform the remainder of the query against those objects locally. Given you dont have any other filters on the query however this probably your best option to achieve this as a LINQ query. Jeremy |
|
|
I've added a feature that will allow you to execute CLR member functions server-side, and this will be included in the next nightly build (30 April 2010). LightSpeed still doesn't know about the spatial type functions, but with this new feature you'll be able to map them yourself using ServerFunctionDescriptor: MethodInfo distanceMethod = typeof(SqlGeography).GetMethod("STDistance"); Notice the . in the function name in the second line (".STDistance"). This is very important because it tells LightSpeed that this is a CLR member function rather than a built-in SQL function or UDF. Without this LightSpeed will generate the wrong SQL syntax! With this registration in place, you can use the STDistance function (and other geometry and geography member functions) in your query and have it executed in the database, so that you do not incur the overhead of fetching and materialising suppliers who are out of range. Please let us know if you run into any problems (and please include the code which registers the STDistance method, your LINQ query code, and a log of the generated SQL -- thanks!). |
|
|
Hi Ivan, tried this, works a treat. Thanks for the very quick turnaround. |
|
|
Think I may have spoke too soon. The STDistance works, but I also need STIntersects, had a problem with this:
//register geography server methods MethodInfo distanceMethod = typeof(SqlGeography).GetMethod("STDistance"); MethodInfo intersectsMethod = typeof(SqlGeography).GetMethod("STIntersects"); //LINQ Query IEnumerable<OperatingZone> zonesInArea = from z in uow.OperatingZones return zonesInArea.ToList();
//ERROR Invalid operator for data type. Operator equals equal to, type equals geography. //LOG
SELECT --> Time: 0 ms The Where clause in the sql doesn't look like its being generated correctly. |
|
|
Ah, Boolean functions, my old nemesis... but with a new twist. As a workaround, this works for me if you explicitly compare Value to true: from z in uow.OperatingZones We'll try to get this fixed (it looks like the LINQ provider is being thrown off by the fact that STIntersects returns a SqlBoolean rather than a CLR Boolean), but since there's a reasonably easy workaround I can't make any promises on how soon this will be -- sorry! |
|
|
Hi Ivan Thanks again for turning this around. Tried this, it looks a little odd but works fine so I'm happy with this. Kamran |
|