MattE wrote:

Also, it occurs to me you have problems with fetching AND saving, or just with saving?

I'll test and find out. I only tried a fetch ..

Ok. And using the spatial types in a where clause I presume? (as fetching the entity worked fine here)

If you don't set UdtTypename, but set the SqlDbType to UDT

I'm sorry I'm not with you. where would I do this, assuming I'm using Linq to LLBLGEN, and the mapped function store referred to above? This doesn't provide for defining the UdtTypeName. thanks!

In the ado.net test you wrote above

Ok. And using the spatial types in a where clause I presume? (as fetching the entity worked fine here)

Its not necessarily just in a where clause. The linq expression is actually intended to evaluate the distance between two geography instances using the spatial STDistance function (which is what I have attempted to map)

In the ado.net test you wrote above

Sorry - being dense again:

OK, when I modify the ADO.net to just use UDT like this:

        SqlConnection cn = Helper.CreateConnection();

        string sql = "select dbo.getdistancespatial(@a,@b) ";

        SqlGeography a = SpatialHelper.CreateGeogPoint(0.0, 0.0);
        SqlGeography b = SpatialHelper.CreateGeogPoint(1.0, 1.0);

        cn.Open();
        SqlCommand cmd = new SqlCommand(sql, cn);

        SqlParameter paramA = new SqlParameter("@a",SqlDbType.Udt);
        paramA.Value = a;

        SqlParameter paramB = new SqlParameter("@a", SqlDbType.Udt);
        paramB.Value = b;

        cmd.Parameters.Add(paramA);
        cmd.Parameters.Add(paramB);

        cmd.ExecuteScalar();
            

I get this:

An exception of type 'System.ArgumentException' occurred in System.Data.dll but was not handled in user code

Additional information: UdtTypeName property must be set for UDT parameters.

Sorry my bad, should have looked in our SQL Server DQE : you should set the type to NVarChar and pass the spatial typed value as-is.

We do that in the runtime for UDTs as well, this way they can be saved without problems. That's not helping you with the fetches indeed... We should look into that to see whether we can come up with something that works

Ok, If i change the type to NVarChar like so:

SqlParameter paramB = new SqlParameter("@a", SqlDbType.NVarChar );

I get this:

[InvalidCastException: Object must implement IConvertible.]
   System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) +7601097
   System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType) +4874600

[InvalidCastException: Failed to convert parameter value from a SqlGeography to a String.]
   System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType) +4874287
   System.Data.SqlClient.SqlParameter.GetCoercedValue() +32
   System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc) +100
   System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters) +203
   System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc) +237
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteScalar() +139
   test_test.Page_Load(Object sender, EventArgs e) in d:\dev\SafariNow.Web\test\test.aspx.cs:108
   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
   System.Web.UI.Control.OnLoad(EventArgs e) +99
   System.Web.UI.Adapters.ControlAdapter.OnLoad(EventArgs e) +15
   System.Web.UI.Control.LoadRecursive() +8687917
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +627

Which is the same error returned when using Linq to LLBLGen

So .. I guess that narrows it down.. ?

Hi Frans,

Not meaning to rush you, but .... any news?

We have a release pending in a week's time, so if no luck, I will roll my code back in the meantime...

thanks,

Matthew

Err... weekend?

We'll look into it today: - fetching with a filter - saving a value.

I have a hard time creating these dreaded SqlGeography instances for inserts/filters. I see you use some sort of helper, but I can't find a simple code snippet which creates a simple SqlGeography instance without resorting through a external scripts and other drivel.

We tested the fetching of values in our revision earlier mentioned in this thread, but not saving/filtering.

If you could attach the code necessary to test this straightforward ( so we don't need geography data in external files etc.), it would be great. (e.g. the helper class to create geography instances in memory). I wonder how MS imagined using this in queries, if it's so hard to create a simple SqlGeography instance....

Weekend?

sorry! not meaning to druk you. (not sure whether that translates...)

Here is the code to create a SqlGeography instance:

 public static SqlGeography CreateGeogPoint(double lat, double lng)
        {
            try
            {
                SqlGeographyBuilder gb = new SqlGeographyBuilder();
                gb.SetSrid(4326);
                gb.BeginGeography(OpenGisGeographyType.Point);
                gb.BeginFigure(lat, lng);
                gb.EndFigure();
                gb.EndGeography();
                return gb.ConstructedGeography;
            }
            catch (Exception e)
            {
                EventLogger.LogError(e, "CreateGeogPoint failed");
                return null;
            }
        }

Thanks..

What's SqlGeographyBuilder ? Is that a class in the MS type lib? Sorry for the naive questions, but it otherwise takes a long time to get a working repro...

When we have code to create these instances in memory from simple data (e.g. a set of doubles) we expect to have a result within 24 hours, so very likely tomorrow (tuesday)

What's SqlGeographyBuilder ? Is that a class in the MS type lib? Sorry for the naive questions, but it otherwise takes a long time to get a working repro...

Yes, it is:

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.types.sqlgeographybuilder.aspx

Sorry, should have included the namespace...

Thanks. Saving at least works properly:

var location = CreateGeogPoint(-34.2323, 15.23232);
var toSave = new GeoTestEntity();
toSave.Location = location;
using(var adapter = new DataAccessAdapter())
{
    adapter.SaveEntity(toSave, true);
}

Generated Sql query: 
    Query: INSERT INTO [TestDB].[dbo].[GeoTest] ([Location]) VALUES (@p2) ;SELECT @p1=SCOPE_IDENTITY()
    Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Output. Value: <undefined value>.
    Parameter: @p2 : String. Length: 25. Precision: 0. Scale: 0. Direction: Input. Value: "POINT (15.23232 -34.2323)".

Will now look into querying

(edit) Querying indeed fails.

var location = CreateGeogPoint(-34.2323, 15.23232);
var toSave = new GeoTestEntity();
toSave.Location = location;
using(var adapter = new DataAccessAdapter())
{
    adapter.SaveEntity(toSave, true);

    var linqMetaData = new LinqMetaData(adapter, new GeoLocationFunctionMappings());
    var toFetch = (from g in linqMetaData.GeoTest
                    where GeoLocationFunctions.STEquals(g.Location, location)==1
                    orderby g.Id descending
                    select g).FirstOrDefault();
    Debug.Assert(toFetch.Id == toSave.Id);
}

I went for a simpler approach:

    public class GeoLocationFunctionMappings : FunctionMappingStore
    {
        public GeoLocationFunctionMappings()
        {
            // define the mapping. SQLServer 2000 needs the schema to be present for the function call,
            // so we specify that as well.
            Add(new FunctionMapping(typeof(GeoLocationFunctions), "STEquals", 2, "{0}.STEquals({1})"));
        }
    }

    public class GeoLocationFunctions
    {
        //stub method
        public static int STEquals(SqlGeography pointA, SqlGeography pointB)
        {
            return 0;
        }
    }

Looking into why this fails.

I found it. The problem is a bit tedious, but there's a way to fix this. The thing is that the value ends up in a DbFunctionCall, and there's no way to find out it's a 'UDT' value, it's just a value. So it ends up as a varchar as the value isn't a known type. To add the SqlGeometry and SqlGeography as known types will make sure it works. I'll patch the DQE and will attach a new build to this thread.

Please use the attached DQE for fixing this issue.

Thanks very much!

    public class GeoLocationFunctionMappings : FunctionMappingStore
    {
        public GeoLocationFunctionMappings()
        {
            // define the mapping. SQLServer 2000 needs the schema to be present for the function call,
            // so we specify that as well.
            Add(new FunctionMapping(typeof(GeoLocationFunctions), "STEquals", 2, "{0}.STEquals({1})"));
        }
    }

    public class GeoLocationFunctions
    {
        //stub method
        public static int STEquals(SqlGeography pointA, SqlGeography pointB)
        {
            return 0;
        }
    }

Ok - that's pretty neat. - thanks again.

Strangely I cannot use this function mapping:

Add(new FunctionMapping(typeof(GeoLocationFunctions), "STEquals", 2, "{0}.STEquals({1})"));

When calling this:

        LinqMetaData lmd = new LinqMetaData(null, new GeoFunctionMappings());

        SqlGeography geography = SpatialHelper.CreateGeogPoint(-34.0493, 18.3292);
        var locations = from location in lmd.Location
                        where GeoFunctions.GetDistance(geography , location.GeoLocation ) > 0
                              && location.TotalAccommodation > 0
                              && location.LocationTypeId == 3
                        orderby GeoFunctions.GetDistance(geography, location.GeoLocation)
                        select
                            new
                            {
                                location.LocationId,
                                location.LocationName,
                                distance = GeoFunctions.GetDistance(geography, location.GeoLocation)
                            };

I get the following error:

Exception information: Exception type: ORMQueryExecutionException Exception message: An exception was caught during the execution of a retrieval query: Cannot call methods on nvarchar..

I have to use this:

Add(new FunctionMapping(typeof(GeoFunctions), "GetDistance", 2,
                            "GetDistanceSpatial({0}, {1})", "---", "dbo"));

which simply invokes the following user defined function

---calculates the distance between two places, typically a place and a seller product
ALTER FUNCTION [dbo].[GetDistanceSpatial]
(   
    @GeoLocation1 geography,
    @GeoLocation2  geography

)
RETURNS float
AS

BEGIN

    return @GeoLocation1.STDistance(@GeoLocation2);

    
END

Am I missing something , or are there other library changes I might need?

Thanks

Remember, the SQLGeometry/geography objects created in memory are transferred as nvarchar to the DB. This means that if you do: {0}.SomeFunction({1}), and the first parameter is an in-memory object, you will effectively get: nvarchar_value.SomeFunction(...)

So to use the pattern {0}.SomeFunction({1}), you have to make sure the first parameter (which will be placed at the {0} position) of the function has to be a field, not an in-memory value.

So my code: where GeoLocationFunctions.STEquals(g.Location, location)==1

works, but: where GeoLocationFunctions.STEquals(location, g.Location)==1

won't work, as that would set 'location' (in-memory object) as the {0} parameter in the pattern.

Right you are.

All working now..