Problem with sqltypes SqlGeography and inheritance

ServiceStack.OrmLite SQL Server
1

Hi, I have a problem with SqlGeography and inheritance.
When used in a class that inherit from another, SqlGeography doesn’t work in OrmLite queries with Limit.
Error: The type “geography” is not comparable. It cannot be used in ORDER BY clause.

I’ve managed to narrow the problem even more. It depends on where you put the ID property.
I have this 2 different data model and a test to pass:

  • The 1st model has only 1 class with ID and GEOGRAPHY together (test PASSED)
  • The 2nd model has a super class with ID and a sub class with GEOGRAPHY (test on subclass NOT PASSED)

1st model - WORKING

public class GeoTest        
{   
   public long Id { get; set; }              
   public SqlGeography Location { get; set; }             
   public SqlGeography NullLocation { get; set; }              
   public SqlGeometry Shape { get; set; }        
}

2nd model - NOT WORKING

public class GeoSuper         
{             
   public long Id { get; set; } 
   public string Other { get; set; }        
}  

public class GeoTest : GeoSuper     
{
   public SqlGeography Location { get; set; } 
   public SqlGeography NullLocation { get; set; }  
   public SqlGeometry Shape { get; set; }         
}

TEST:

[TestMethod]
public void LimitTest()
{
   InsertData(100);
   List<GeoTest> data = null;
   using (var db = dbFactory.OpenDbConnection())
   {
      data = db.Select(db.From<GeoTest>().Limit(0,int.MaxValue));
   }

   Assert.IsNotNull(data);
   Assert.IsTrue(data.Count == 100);
 }

private void InsertData(int count)
{
    using (var db = dbFactory.OpenDbConnection())
    db.DropAndCreateTable<GeoTest>();
    for (int i = 0; i < count; i++)
    {
       db.Insert(new GeoTest() {Id = i,Location = RandomPosition()});
    }
}

private SqlGeography RandomPosition()
{
   SqlGeography result;
   Random rand = new
   Random(DateTime.Now.Millisecond * (int)DateTime.Now.Ticks); 
   double lat = Math.Round(rand.NextDouble() * 160 - 80, 6);
   double lon = Math.Round(rand.NextDouble() * 360 - 180, 6);
   result = SqlGeography.Point(lat, lon, 4326);
   result.MakeValid();
   return result;
}

So, recap of conditions:

  • Using SqlGeography
  • type Id on super class
  • Query with Limit
2

The issue is that inheritance changes the order the fields are selected and querying a limit adds an implicit order for SQL Server 2012 providers on the first column which is usually the primary key, you can get around this issue by adding an explicit OrderBy, e.g:

db.Select(db.From<GeoTest>().Limit(0,int.MaxValue).OrderBy(x => x.Id));

I’ll look into making it work by default for inherited base types containing primary keys in SQL Server 2012.

3

Ok this should be resolved in this commit where SQL Server 2012 queries with limits (>1) without an explicit OrderBy are now ordered by PrimaryKey instead of the first column.

This change is available from v4.0.55 that’s now available on MyGet.