Unnecessary SQL Update

Description

when I'm working with MsSqlSpatial.Provider and I work with any Geometry Type, I get an unecessary Update that is generated to update the geometry Property.

I'm using Dynamic-Update so I can confirm that this only because the Geometry column.

Environment

None

Activity

Show:

Ricardo Stuven October 6, 2010 at 10:02 AM

Fixed in revision 1351.

David Faivre July 12, 2009 at 1:36 PM

Like Jason, I use the MsSql2008 type. You need to make sure that you're:

1.) Making the geometry type valid
2.) You probably want to use the SqlGeometry.STEquals() function to get the most accurate equality comparison.

I really clobbered my patch in, by calling out to a protected equality function in the GeometryTypeBase class:

bool IUserType.Equals(object a, object b)
{
return this.UserTypeEq(a, b);
}

protected virtual bool UserTypeEq(object a, object b)
{
return Util.EqualsHelper.Equals(
(a is IGeometry ? this.FromGeometry(a) : (T)a),
(b is IGeometry ? this.FromGeometry(b) : (T)b));
}

Then I override it in my MsSql2008 geometry type:

protected override bool UserTypeEq(object a, object b)
{
return this.FromGeometry(a).MakeValid().STEquals(this.FromGeometry(b).MakeValid()).IsTrue;
}

Definitely not a pretty solution - but I wanted to raise the issue that the MsSql2008 geometry types have special functions when it comes to equality.

Jason Finch April 28, 2009 at 7:55 PM

I had the same problem as Mark, isDirty flagged for any entity with a Geography type.

My underlying type was not byte[] but Microsoft.SqlServer.Types.SqlGeography
When the IUserType.Equals() fires and casts it IGeometry to SqlGeography the NHibernate.Util.EqualsHelper fails to compare equality.

Changing code to what Mark listed in GeometryTypeBase.
if(a is IGeometry && b is IGeometry)
return a.Equals(b);

Making this change the bottom test would pass, without it nhibernate always considered the enties dirty.

[Test]
public void NHibernate_EqualsHelper_For_Geography()
{
//IUserType.Equals casts IGeometry to this.FromGeometry(a) which becomes Microsoft.SqlServer.Types.SqlGeography
//Test fails
SqlGeography sqlGeography1 = SqlGeography.Point(-17.518056,146.018056,4326);
SqlGeography sqlGeography2 = SqlGeography.Point(-17.518056,146.018056,4326);
bool isEqual = NHibernate.Util.EqualsHelper.Equals(sqlGeography1, sqlGeography2);
Assert.IsTrue(isEqual);
}

[Test]
public void SqlGeography_IsEqual_Using_STEquals()
{
//Test Passes
SqlGeography sqlGeography1 = SqlGeography.Point(-17.518056, 146.018056, 4326);
SqlGeography sqlGeography2 = SqlGeography.Point(-17.518056, 146.018056, 4326);
SqlBoolean isEqual = sqlGeography1.STEquals(sqlGeography2);
Assert.IsTrue(isEqual.IsTrue);
}

[Test]
public void SpatialRow_Should_Not_Be_Dirty()
{
RhinoTransaction trans = uow.BeginTransaction();
DetachedCriteria crit = DetachedCriteria.For<GeoEntity>().SetMaxResults(1);
var result1 = Repository<GeoEntity>.FindOne(crit);
bool isDirty = UnitOfWork.CurrentSession.IsDirty();
trans.Commit();
Assert.IsFalse(isDirty);
}

Mark Bastian April 7, 2009 at 6:07 AM

This sounds like the same issue I've encountered, but in my case with MySql. I posted a question about it on the nhforge users group last November and have since been contacted by two other people with the same issue.

It appears the problem is that the NHibernate.Spatial.Type.GeometryTypeBase method IUserType.Equals(object a, object b) is not returning true when two geometries are actually equal. In my configuration the underlying objects are of type byte[] and Util.EqualsHelper.Equals method does not handle comparisons on arrays.

I patched the beta2 source code with a change to support byte[] comparisons when necessary and that solved the problem for me. Just today I got the latest source and applied it again with the same successful results.

I'm not sure it's the optimum solution, but my alternate method is this:

bool IUserType.Equals(object a, object b)
{
object aa = a is IGeometry ? this.FromGeometry(a) : (T)a;
object bb = b is IGeometry ? this.FromGeometry(b) : (T)b;
if (aa is byte[] && bb is byte[])
{
if (((byte[])aa).Length != ((byte[])bb).Length)
return false;
for (int i = 0; i < ((byte[])aa).Length; i++)
{
if (((byte[])aa)[i] != ((byte[])bb)[i])
return false;
}
return true;
}
else
return Util.EqualsHelper.Equals(aa, bb);
}

In all my testing, a and b are always of type IGeometry and IGeometry.Equals seems to return accurate results as well; so an alternate implementation could possibly be
... if(a is IGeometry && b is IGeometry)
return a.Equals(b);
...

Ricardo Stuven March 29, 2009 at 4:44 PM

This ticket will be reopen if a unit test reproducing the issue is provided.

Fixed

Details

Assignee

Reporter

Components

Affects versions

Priority

Who's Looking?

Open Who's Looking?

Created February 18, 2009 at 12:42 PM
Updated October 6, 2010 at 10:02 AM
Resolved October 6, 2010 at 10:02 AM
Who's Looking?