Page 1 of 1

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

Posted: Thu 13 Oct 2016 14:15
by smokeing
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

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

Posted: Tue 18 Oct 2016 09:32
by Shalex
Thank you for your report. We will investigate the issue and notify you about the result.

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

Posted: Wed 19 Oct 2016 10:36
by Shalex
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.

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

Posted: Thu 20 Oct 2016 16:35
by Shalex
New build of dotConnect for Oracle 9.1.131 is available for download now: viewtopic.php?f=1&t=34458.

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

Posted: Mon 24 Oct 2016 15:33
by smokeing
Thank you very much! :D

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

Posted: Tue 25 Oct 2016 18:12
by smokeing
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.

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

Posted: Wed 26 Oct 2016 10:23
by smokeing
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.

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

Posted: Thu 27 Oct 2016 12:34
by Pinturiccio
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.

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

Posted: Sat 29 Oct 2016 03:00
by smokeing
I have prepared test projects.

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

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

Posted: Tue 01 Nov 2016 15:45
by Pinturiccio
We have answered you via e-mail.

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

Posted: Fri 19 May 2017 15:12
by smokeing
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.

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

Posted: Mon 22 May 2017 11:17
by Shalex
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?

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

Posted: Tue 23 May 2017 11:47
by smokeing
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)
?

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

Posted: Tue 23 May 2017 12:25
by Shalex
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