Hi Frans,
Finally got back to this:
I've implemented the function mapping as follows:
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), "GetDistance", 2,
"GetDistanceSpatial({0}, {1})", "SafariNow", "dbo"));
}
}
public class GeoLocationFunctions
{
//stub method
public static float GetDistance(SqlGeography pointA, SqlGeography pointB)
{
// empty body, as it's just here to make the query compile.
//mk:@MSITStore:C:\Program%20Files%20(x86)\Solutions%20Design\ LLBLGen%20Pro%20v3.0\Documentation\LLBLGen%20Pro%20Runtime%20Framework%20v3.0.chm ::/Using%20the%20generated%20code /Linq/gencode_linq_functionmappings.htm#FunctionMappingStore
return 0.0F;
}
}
This maps the to the following db function:
ALTER FUNCTION [dbo].[GetDistanceSpatial]
(
@GeoLocation1 geography,
@GeoLocation2 geography
)
RETURNS float
AS
BEGIN
return @GeoLocation1.STDistance(@GeoLocation2);
END
And the linq integration e.g.
SqlGeography geography = SpatialHelper.CreateGeogPoint(-34.0493, 18.3292);
var locations = from location in lmd.Location
where GeoLocationFunctions.GetDistance(geography, location.GeoLocation) > 0
&& location.TotalAccommodation > 0
&& location.LocationTypeId == 3
orderby GeoLocationFunctions.GetDistance(geography, location.GeoLocation)
select
new
{
location.LocationId,
location.LocationName,
distance = GeoLocationFunctions.GetDistance(geography, location.GeoLocation)
};
It almost works...
I get an exception message: "Failed to convert parameter value from a SqlGeography to a String."
with this stack trace
at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)
at System.Data.SqlClient.SqlParameter.GetCoercedValue()
at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
at System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters)
at System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
The QueryExecuted property looks like this:
Query: SELECT TOP(@p4) [LPLA_1].[LocationID] AS [LocationId], [LPLA_1].[LocationName], [SafariNow].[dbo].GetDistanceSpatial(@p2, [LPLA_1].[GeoLocation]) AS [distance] FROM [SafariNow].[dbo].[Location] [LPLA_1] (nolock) WHERE ( ( ( ( ( ( ( ( [SafariNow].[dbo].GetDistanceSpatial(@p8, [LPLA_1].[GeoLocation]) > @p6) AND ( [LPLA_1].[TotalAccommodation] > @p9)) AND ( [LPLA_1].[LocationTypeID] = @p10))))))) ORDER BY [SafariNow].[dbo].GetDistanceSpatial(@p12, [LPLA_1].[GeoLocation]) ASC
Parameter: @p2 : AnsiString. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: "POINT (18.3292 -34.0493)".
Parameter: @p4 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2.
Parameter: @p8 : AnsiString. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: "POINT (18.3292 -34.0493)".
Parameter: @p6 : Single. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0.
Parameter: @p9 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 0.
Parameter: @p10 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 3.
Parameter: @p12 : AnsiString. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: "POINT (18.3292 -34.0493)".
If I wrap each of the point properties in single inverted commas .. it works, but not sure how to implement this in the function mapping?
thanks in advance..