SqlServer Intersects problem with Well-known text translate to varbinary geom

[liao770913]

Model:

public partial class Acc
    {
        public Acc()
        {
            AccDetail = new HashSet<AccDetail>();
            CollisionPoints = new HashSet<CollisionPoints>();
        }

        public long KeyId { get; set; }
        
	...

        virtual public AccPoints AccPoints { get; set; }
        
	...

    }

public partial class AccPoints
    {
        public AccPoints()
        {
            ...
        }

        public int KeyId { get; set; }
        public long? AccKeyId { get; set; }
        
	...

        public IGeometry Geom { get; set; }
        
	...

        public virtual Acc AccKey { get; set; }
	...
    }

Context:

modelBuilder.Entity<AccPoints>(entity =>
        {
            entity.HasKey(e => e.KeyId);

            ...

            entity.Property(e => e.AccKeyId).HasColumnName("Acc_KeyId");

            ...

            entity.Property(e => e.Geom).HasColumnType("geometry");

            ...

        });

Code 1 :

string WKT = "POLYGON ((13491441.240446748 2890466.2246039393, 13567266.772505643 2890466.2246039393, 13567266.772505643 2892912.2095090649, 13491441.240446748 2892912.2095090649, 13491441.240446748 2890466.2246039393))";
    var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(3857);
    var reader = new NetTopologySuite.IO.WKTReader(geometryFactory);
    var geom = (GeoAPI.Geometries.IPolygon)reader.Read(WKT);
    geom.SRID = 3857;

var tmp1 = _SQLContext.Acc.Where(b => b.DataStatus == "N" && b.AccPoints.Geom.Intersects(geom)).Select(b => b.KeyId).ToList();

Code 1 Translate SQL:

exec sp_executesql N'SELECT [a].[KeyId]
FROM [Acc] AS [a]
LEFT JOIN (
    SELECT [a0].*
    FROM [AccPoints] AS [a0]
    WHERE [a0].[DataStatus] = ''N''
) AS [t] ON [a].[KeyId] = [t].[Acc_KeyId]
WHERE ([a].[DataStatus] = ''N'') AND (([a].[DataStatus] = ''N'') AND ([t].[Geom].STIntersects(@__geom_0) = 1))',N'@__geom_0 varbinary(112)',@__geom_0=0x110F000001040500000067D2BF1C710D464161BDB1279EBB694167D2BF1C710D4641C15DB858A4E069416B31D11A38124641C15DB858A4E069416B31D11A3812464161BDB1279EBB694167D2BF1C710D464161BDB1279EBB694101000000020000000001000000FFFFFFFF0000000003

Code 2 :

string WKT = "POLYGON ((13491441.240446748 2890466.2246039393, 13567266.772505643 2890466.2246039393, 13567266.772505643 2892912.2095090649, 13491441.240446748 2892912.2095090649, 13491441.240446748 2890466.2246039393))";
    var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(3857);
    var reader = new NetTopologySuite.IO.WKTReader(geometryFactory);
    var geom = (GeoAPI.Geometries.IPolygon)reader.Read(WKT);
    geom.SRID = 3857;

var tmp2 = _SQLContext.Acc.Where(b => b.DataStatus == "N" &&
            _SQLContext.AccPoints.Where(c => c.Geom.Intersects(geom)).Select(ug => ug.AccKeyId).Contains(b.KeyId)
        ).Select(b => b.KeyId).ToList();

Code 2 Translate SQL:

exec sp_executesql N'SELECT [a].[KeyId]
FROM [Acc] AS [a]
WHERE ([a].[DataStatus] = ''N'') AND (([a].[DataStatus] = ''N'') AND [a].[KeyId] IN (
    SELECT [a0].[Acc_KeyId]
    FROM [AccPoints] AS [a0]
    WHERE ([a0].[DataStatus] = ''N'') AND ([a0].[Geom].STIntersects(@__geom_0) = 1)
))',N'@__geom_0 varbinary(112)',@__geom_0=0x110F000001040500000061BDB1279EBB694167D2BF1C710D4641C15DB858A4E0694167D2BF1C710D4641C15DB858A4E069416B31D11A3812464161BDB1279EBB69416B31D11A3812464161BDB1279EBB694167D2BF1C710D464101000000020000000001000000FFFFFFFF0000000003

Try to Test Geometry:

declare @geom1 geometry
set @geom1 = 0x110F000001040500000067D2BF1C710D464161BDB1279EBB694167D2BF1C710D4641C15DB858A4E069416B31D11A38124641C15DB858A4E069416B31D11A3812464161BDB1279EBB694167D2BF1C710D464161BDB1279EBB694101000000020000000001000000FFFFFFFF0000000003
select @geom1.STAsText()

-- Result: POLYGON ((2890466.2246039393 13491441.240446748, 2890466.2246039393 13567266.772505643, 2892912.2095090649 13567266.772505643, 2892912.2095090649 13491441.240446748, 2890466.2246039393 13491441.240446748))

declare @geom2 geometry
set @geom2 = 0x110F000001040500000061BDB1279EBB694167D2BF1C710D4641C15DB858A4E0694167D2BF1C710D4641C15DB858A4E069416B31D11A3812464161BDB1279EBB69416B31D11A3812464161BDB1279EBB694167D2BF1C710D464101000000020000000001000000FFFFFFFF0000000003
select @geom2.STAsText()

-- Result: POLYGON ((13491441.240446748 2890466.2246039393, 13567266.772505643 2890466.2246039393, 13567266.772505643 2892912.2095090649, 13491441.240446748 2892912.2095090649, 13491441.240446748 2890466.2246039393))

Issues:

"Code 1" translate wrong geometry, because coordinate x y are opposited

[bricelam]

It looks like the type inference isn't working with nav expansion. In other words, the variable is being interpreted as GEOGRAPHY (the default) instead of GEOMETRY (as it should be inferred from the column type).

Code 2 (manually expanding the navigation property into a join) is the correct workaround.

[bricelam]

This may already have been fixed in 3.0.0 by @smitpatel

[liao770913]

Thank you