Spatial not working when using Context.Database.SqlQuery<>

Spatial not working when using Context.Database.SqlQuery<>

Postby smokeing » Thu 13 Oct 2016 14:15

Hi,

Query:
Code: Select all
return Context.Database.SqlQuery<Entity>(
"SELECT
    t.geom AS Geom_try1
    (
        CASE WHEN t.geom IS NULL THEN
            NULL
        ELSE
            'SRID=' || NVL(t.geom.SDO_SRID, '0') || ';' || SDO_UTIL.TO_WKTGEOMETRY(t.geom)
        END
    ) AS Geom_try2
...
);


I have tried try1 and try2 variants. Both are throwing exception:
System.Data.Entity.Core.ProviderIncompatibleException : The provider did not return a 'DbSpatialServices' instance. In order to use the 'DbGeography' or 'DbGeometry' spatial types the EF provider being used must support spatial types and all prerequisites for the provider must be installed. See http://go.microsoft.com/fwlink/?LinkId=287183 for details.


dotConnect configuration:
Code: Select all
config.SpatialOptions.SpatialServiceType = SpatialServiceType.NetTopologySuite;


Spatials works ok when using EntityFramework calls.

dotConnect version: 9.1.97.0
smokeing
 
Posts: 23
Joined: Fri 09 Oct 2015 00:51

Re: Spatial not working when using Context.Database.SqlQuery<>

Postby Shalex » Tue 18 Oct 2016 09:32

Thank you for your report. We will investigate the issue and notify you about the result.
Shalex
Devart Team
 
Posts: 7540
Joined: Thu 14 Aug 2008 12:44

Re: Spatial not working when using Context.Database.SqlQuery<>

Postby Shalex » Wed 19 Oct 2016 10:36

The bug with materializing object, which includes the DbGeometry or DbGeography properties, when executing SQL query via DbContext.Database.SqlQuery<> in EF5 and EF6 is fixed. We will notify you when the corresponding build of dotConnect for Oracle is available for download.
Shalex
Devart Team
 
Posts: 7540
Joined: Thu 14 Aug 2008 12:44

Re: Spatial not working when using Context.Database.SqlQuery<>

Postby Shalex » Thu 20 Oct 2016 16:35

New build of dotConnect for Oracle 9.1.131 is available for download now: http://forums.devart.com/viewtopic.php?f=1&t=34458.
Shalex
Devart Team
 
Posts: 7540
Joined: Thu 14 Aug 2008 12:44

Re: Spatial not working when using Context.Database.SqlQuery<>

Postby smokeing » Mon 24 Oct 2016 15:33

Thank you very much! :D
smokeing
 
Posts: 23
Joined: Fri 09 Oct 2015 00:51

Re: Spatial not working when using Context.Database.SqlQuery<>

Postby smokeing » Tue 25 Oct 2016 18:12

Unfortunately last version of dotConnect broke our build.
We were upgrading from version 9.1.97.0 to 9.1.131.0. We had following references:
* NetTopologySuite - version 1.13.2.0
* GeoAPI - version 1.7.2.0
* SharpMap

Our Database dll were referencing all these dlls and dotConnect dlls. To get these dlls copied to deploy directory I had to make something like this:

Code: Select all
    public abstract class Dummy
    {
        public readonly Type DevartDataOracleEntitySpatials;
        public readonly Type SharpMap;
        public readonly Type BruTile;

        protected Dummy()
        {
            DevartDataOracleEntitySpatials = typeof(Devart.Data.Oracle.Entity.Spatials.OracleNetTopologySuiteSpatialServices);
            SharpMap = typeof(SharpMap.Map);
            BruTile = typeof(BruTile.Utilities);
        }
    }


I can no longer use Devart.Data.Oracle.Entity.Spatials.OracleNetTopologySuiteSpatialServices because it is defiend in two dotConnect dll's, but that's not the problem.

The problem is VisualStudio stopped copying NetTopologySuite and GeoAPI to deploy folder. I've extended Dummy constructor to

Code: Select all
            DevartDataOracleEntitySpatials = typeof(Devart.Data.Oracle.Entity.Spatials.OracleSharpMapSpatialServices);
            BruTile = typeof(BruTile.Utilities);
            NetTopologySuite = typeof(NetTopologySuite.NtsGeometryServices);
            NetTopologySuiteIoGeoTools = typeof(NetTopologySuite.IO.Handlers.PointHandler);
            NetTopologySuiteIoMsSqlSpatial = typeof(NetTopologySuite.IO.MsSqlSpatialReader);
            NetTopologySuiteIoPostGis = typeof(NetTopologySuite.IO.PostGisReader);
            GeoApi = typeof(GeoAPI.GeometryServiceProvider);


but this not work. I even referenced NetTopologySuite and GeoApi in our main project, but VS still refuses to copy.

I've found warning in Build output:

No way to resolve conflict between "NetTopologySuite, Version=1.13.2.0, Culture=neutral, PublicKeyToken=f580a05016ebada1" and "NetTopologySuite, Version=1.14.0.0, Culture=neutral, PublicKeyToken=null". Choosing "NetTopologySuite, Version=1.14.0.0, Culture=neutral, PublicKeyToken=f580a05016ebada1" arbitrarily.


I've checked - only one version of NetTopologySuite is referenced, and it is 1.13.2.0.

What is going on?

Tried to update NetTopologySuite. I had to remove SharpMap (we aren't using it anyway, does dotConnect need it?) for this.

DLL IS NOW COPIED.

Now i'm getting exception:

Exception thrown: 'System.IO.FileLoadException' in Devart.Data.Oracle.Entity.Spatials.EF6.dll

Additional information: Could not load file or assembly 'NetTopologySuite, Version=1.13.2.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)


Exception is thrown despite assemblyBinding is present:

Code: Select all
      <dependentAssembly>
        <assemblyIdentity name="NetTopologySuite" publicKeyToken="f580a05016ebada1" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-1.14.0.0" newVersion="1.14.0.0" />
      </dependentAssembly>


Please guys help, i really don't know what's going on. I have wasted 6 hours already.
smokeing
 
Posts: 23
Joined: Fri 09 Oct 2015 00:51

Re: Spatial not working when using Context.Database.SqlQuery<>

Postby smokeing » Wed 26 Oct 2016 10:23

I made further investigation over this.
NetTopologySuite and GeoApi (and it turned out ProjNet too) aren't referenced in their old versions because:

6> No way to resolve conflict between "NetTopologySuite, Version=1.14.0.0, Culture=neutral, PublicKeyToken=f580a05016ebada1" and "NetTopologySuite, Version=1.13.2.0, Culture=neutral, PublicKeyToken=null". Choosing "NetTopologySuite, Version=1.14.0.0, Culture=neutral, PublicKeyToken=f580a05016ebada1" arbitrarily.
6> No way to resolve conflict between "GeoAPI, Version=1.7.4.0, Culture=neutral, PublicKeyToken=a1a0da7def465678" and "GeoAPI, Version=1.7.2.0, Culture=neutral, PublicKeyToken=null". Choosing "GeoAPI, Version=1.7.4.0, Culture=neutral, PublicKeyToken=a1a0da7def465678" arbitrarily.
6> No way to resolve conflict between "ProjNet, Version=1.3.0.3, Culture=neutral, PublicKeyToken=null" and "ProjNet, Version=1.2.3882.19088, Culture=neutral, PublicKeyToken=a1f5bdcd9c6f5740". Choosing "ProjNet, Version=1.3.0.3, Culture=neutral, PublicKeyToken=null" arbitrarily.


It is choosing arbitrarily versions which don't exist anywhere!

I changed verbosity to diagnostic to figure out what tries to reference version 1.14.0.0:

1> References which depend on "NetTopologySuite, Version=1.14.0.0, Culture=neutral, PublicKeyToken=f580a05016ebada1" []. (TaskId:18)
1> D:\Repo\packages\dotConnect.Oracle.9.1.131.0\lib\Devart.Data.Oracle.Entity.EF6.dll (TaskId:18)
1> Project file item includes which caused reference "D:\Repo\packages\dotConnect.Oracle.9.1.131.0\lib\Devart.Data.Oracle.Entity.EF6.dll". (TaskId:18)
1> Devart.Data.Oracle.Entity.EF6, Version=9.1.131.0, Culture=neutral, PublicKeyToken=09af7300eec23701, processorArchitecture=MSIL (TaskId:18)
1> Devart.Data.Oracle.Entity.Spatials.EF6, Version=9.1.131.0, Culture=neutral, processorArchitecture=MSIL (TaskId:18)
1> References which depend on "NetTopologySuite, Version=1.13.2.0, Culture=neutral, PublicKeyToken=null" []. (TaskId:18)
1> D:\Repo\packages\dotConnect.Oracle.9.1.131.0\lib\Devart.Data.Oracle.Entity.Spatials.EF6.dll (TaskId:18)
1> Project file item includes which caused reference "D:\Repo\packages\dotConnect.Oracle.9.1.131.0\lib\Devart.Data.Oracle.Entity.Spatials.EF6.dll". (TaskId:18)
1> Devart.Data.Oracle.Entity.Spatials.EF6, Version=9.1.131.0, Culture=neutral, processorArchitecture=MSIL (TaskId:18)


Same warnings are thrown for previous version of dotConnect (9.1.97), but then Devart.Data.Oracle.Entity.EF6 weren't referencing NTS, and version 1.13.2.0 was chosen arbitrarily - msbuild was able to found NTS 1.13.2.0 and dll was able to copy successfully.
smokeing
 
Posts: 23
Joined: Fri 09 Oct 2015 00:51

Re: Spatial not working when using Context.Database.SqlQuery<>

Postby Pinturiccio » Thu 27 Oct 2016 12:34

Please create a test project, which reproduces the issue. You can archive your file and upload it to our ftp server (ftp://ftp.devart.com/ , credentials: anonymous/anonymous) or to any file exchange server so that we could download it from there. You can send us the password to the archive via our contact form.

Please also send us DDL/DML scripts reqiered for the project.
Pinturiccio
Devart Team
 
Posts: 1936
Joined: Wed 02 Nov 2011 09:44

Re: Spatial not working when using Context.Database.SqlQuery<>

Postby smokeing » Sat 29 Oct 2016 03:00

I have prepared test projects.

A - our old configuration
B - after nuget references upgrade.
smokeing
 
Posts: 23
Joined: Fri 09 Oct 2015 00:51

Re: Spatial not working when using Context.Database.SqlQuery<>

Postby Pinturiccio » Tue 01 Nov 2016 15:45

We have answered you via e-mail.
Pinturiccio
Devart Team
 
Posts: 1936
Joined: Wed 02 Nov 2011 09:44

Re: Spatial not working when using Context.Database.SqlQuery<>

Postby smokeing » Fri 19 May 2017 15:12

Hello,

the problem with copying dll's to output directory returned when we wanted to upgrade dotConnect.
I think the issue is created by reference:
Devart.Data.Oracle.Entity.EF6 -> NetTopologySuite 1.14.0.0
Devart.Data.Oracle.Entity.EF6 -> GeoAPI 1.7.4.0

Image

As you can see Entity.Spatials.EF6 and Entity.EF6 reference same dlls but in different versions.
Would it be a problem for you to align those versions?

My proposal is version forced by SharpMap dll:
"= 1.13.2" for NTS
"= 1.7.2" for GeoAPI
(https://www.nuget.org/packages/SharpMap/)

I tried to remove SharpMap from our project, but although we use NTS as spatial provider, it needs SharpMap.dll.
smokeing
 
Posts: 23
Joined: Fri 09 Oct 2015 00:51

Re: Spatial not working when using Context.Database.SqlQuery<>

Postby Shalex » Mon 22 May 2017 11:17

Please refer to v9.2 release notes (full revision history):
  • Signed versions of NetTopologySuite GIS library are supported in EF5/EF6
  • NetTopologySuite 1.14 is supported in EF5/EF6
  • GeoAPI 1.7.4 is supported in EF5/EF6
  • The NetTopologySuiteSigned and NetTopologySuiteUnsigned members are added to the SpatialServiceType enum in EF5/EF6
  • The behaviour is changed: the functionality of SpatialServiceType.NetTopologySuiteSigned is moved to Devart.Data.Oracle.Entity.EF5.dll / Devart.Data.Oracle.Entity.EF6.dll
So
  • if you want to use signed NetTopologySuite 1.14 or higher:
    -> add references to
    * Devart.Data.dll
    * Devart.Data.Oracle.dll
    * Devart.Data.Oracle.Entity.EF6.dll
    -> remove reference to Devart.Data.Oracle.Entity.Spatials.EF6.dll (if any) from your project
    -> set config.SpatialOptions.SpatialServiceType = SpatialServiceType.NetTopologySuiteSigned;
  • in case of unsigned NetTopologySuite up to v1.13.2:
    -> additionally add reference to Devart.Data.Oracle.Entity.Spatials.EF6.dll to your project
    -> set config.SpatialOptions.SpatialServiceType = SpatialServiceType.NetTopologySuiteUnsigned;
Does this help?
Shalex
Devart Team
 
Posts: 7540
Joined: Thu 14 Aug 2008 12:44

Re: Spatial not working when using Context.Database.SqlQuery<>

Postby smokeing » Tue 23 May 2017 11:47

Now I have these dlls referenced:
Image
(NTS 1.13

If i understand correctly I have to leave only this ones:
Image
(NTS 1.14)
?
smokeing
 
Posts: 23
Joined: Fri 09 Oct 2015 00:51

Re: Spatial not working when using Context.Database.SqlQuery<>

Postby Shalex » Tue 23 May 2017 12:25

The Devart.Data.Linq.dll and Devart.Data.Oracle.Linq.dll assemblies are used only by LinqConnect ORM. If you are working with Entity Framework, remove references to them (classes from these assemblies are not employed).

NTS 1.13:
* Devart.Data.dll
* Devart.Data.Oracle.dll
* Devart.Data.Oracle.Entity.EF6.dll
* Devart.Data.Oracle.Entity.Spatials.EF6.dll

NTS 1.14:
* Devart.Data.dll
* Devart.Data.Oracle.dll
* Devart.Data.Oracle.Entity.EF6.dll
Shalex
Devart Team
 
Posts: 7540
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle