1

I have this query (Simple example, not the real one, I need to use the INTERSECT):

SELECT Id,Geo FROM Locations WHERE Id=1
INTERSECT 
SELECT Id,Geo FROM Locations WHERE Id>10

The column Geo is geometry type, and when I execute this query I get this error:

The data type geometry cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.

However if I cast the property as nvarchar, it works:

SELECT Id, CAST(Geo AS NVARCHAR(MAX)) as Geo 
FROM Locations 
WHERE Id = 1
INTERSECT 
SELECT Id, CAST(Geo AS NVARCHAR(MAX)) as Geo 
FROM Locations 
WHERE Id > 10

Any ideas of how to do that cast with Linq IQueryable??

var query1= _context.Set<Location>().Where(x => x.Id = 1);
var query2= _context.Set<Location>().Where(x => x.Id > 10);
var result= query1.Intersect(query2).ToList();

I have something like that, but I don't now how to do the cast of the property Geo. When I do the .ToList() I get the same SqlError:

The data type geometry cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.

I'm using C# with Core 3.1

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dani Hernandez
  • 57
  • 9
  • Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. In particular, you need to implement the SQL `SELECT` in the LINQ as `Select(loc => new { loc.Id, Geo = loc.Geo.ToString() })` after each `Where`. – NetMage Dec 30 '19 at 20:21
  • Based on just the id column, those two result sets will never intersect. I know you said it was ah example, but what are you actually trying to do? If you're looking at geometric intersection, do you need STIntersects()? – Ben Thul Dec 31 '19 at 07:50

2 Answers2

0

Maybe you can implement IComparer Interface on Geometry class. https://learn.microsoft.com/en-us/dotnet/api/system.collections.generic.icomparer-1?view=netframework-4.8

Atahan Ceylan
  • 106
  • 2
  • 5
  • I already tried that, not working. Geometry is from NetTopologySuite library and and implements IComparable. https://nettopologysuite.github.io/html/class_net_topology_suite_1_1_geometries_1_1_geometry.html – Dani Hernandez Dec 30 '19 at 13:50
  • Can you try to implement IEqualityComparer https://learn.microsoft.com/en-us/dotnet/api/system.collections.generic.iequalitycomparer-1?view=netframework-4.8 – Atahan Ceylan Dec 30 '19 at 14:21
0

Finally, the only solution that works for my case is to create another table, for example, LocationGeo with a 1-1 relationship.

I do the Intersect or Union in the Location table with Linq and a Include of the new table.

Dani Hernandez
  • 57
  • 9