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

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
smokeing
Posts: 23
Joined: Fri 09 Oct 2015 00:51

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

Post by 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

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Tue 18 Oct 2016 09:32

Thank you for your report. We will investigate the issue and notify you about the result.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

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

Post by 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
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Thu 20 Oct 2016 16:35

New build of dotConnect for Oracle 9.1.131 is available for download now: viewtopic.php?f=1&t=34458.

smokeing
Posts: 23
Joined: Fri 09 Oct 2015 00:51

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

Post by 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<>

Post by 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<>

Post by 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.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

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

Post by 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.

smokeing
Posts: 23
Joined: Fri 09 Oct 2015 00:51

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

Post by smokeing » Sat 29 Oct 2016 03:00

I have prepared test projects.

A - our old configuration
B - after nuget references upgrade.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

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

Post by Pinturiccio » Tue 01 Nov 2016 15:45

We have answered you via e-mail.

smokeing
Posts: 23
Joined: Fri 09 Oct 2015 00:51

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

Post by 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.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

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

Post by 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?

smokeing
Posts: 23
Joined: Fri 09 Oct 2015 00:51

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

Post by 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)
?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

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

Post by 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

Post Reply