Hi,

May I know if SQL Sever 2008's sparse columns and the xml datatype are supported in 2.6?

If not, will there be support be in 3.0?

Thanks, Cheng

The geographical spatial types are supported as long as you have the .NET assembly which defines the types in your GAC, as they're seen as normal UDT's. Xml type is also supported, as string. You can't do XQuery queries on xml typed columns as that would open the route to sql injection and we want to avoid that at all costs.

Hi Frans

Can you clarify what that assembly is ?

At the moment LLBLGen seems to have mapped the geographic data type as a string.

IN addition, is syntax like the following possible (using e.g. Linq to LLBLGEN)?

SELECT name FROM Roads WHERE location.STIntersects(@microsoft) = 1

Given a similar Parks table containing all US parks, we can ask our parks question from above:

SELECT SUM(location.STArea()) FROM Parks WHERE location.STDistance(@microsoft) < 1.0

thanks,

I think it's the Microsoft.SqlServer.Types.dll. This can be found in [SQL Server Installation Folder]\100\SDK\Assemblies\

The geographical spatial types are supported as long as you have the .NET assembly which defines the types in your GAC

Ok, I've gac'ced that assembly, but the field is still mapped as a string, by default

What should it be? (do I need to map it manually, in the editor?)

Thanks,

M

The sql server driver should have picked them up.

Yes, but apparently not?

I get the feeling I might need to be manually setting up a UDT in the fields editor or something..

I will keep digging around

To our knowledge the spatial types are UDTs, so when a table has a spatial type, it's seen as a UDT, and the assembly of the type is 'loaded' (i.e. if the type is reachable through the GAC etc.) and used as such. If that type lookup fails, the type is skipped and seen as 'string'.

We'll look into whether the types are indeed reported as 'UDT' or otherwise. If otherwise, it won't work of course, so we'll then make a change to the driver.

For doing model first, you have to add a type converter which returns the spatial type. It is then picked up as a type you can work with for mapping to tables. The reason it is cumbersome for model-first is that sqlserver requires you to register the UDT assemblies with the catalog, which is a pretty strict (read: cumbersome) process, so using UDT's is actually a database-first 'preferred' mechanism. Not sure if you're using model first or database first.

Ok, I'll wait to hear from you then.

To answer your question, we are doing database first (by which I assume you mean the DB drives the Model, and not the other way around. Didn't realize it was possible to be honest.....)

Thanks, Matthew

MattE wrote:

Ok, I'll wait to hear from you then.

To answer your question, we are doing database first (by which I assume you mean the DB drives the Model, and not the other way around. Didn't realize it was possible to be honest.....)

Thanks, Matthew

yes v3 does model first as well, you create an entity, create some fields, say 'automap' and voila, you have a table with fields Export it to DDL SQL -> run it on the DB, done.

Looking into the problem now.

(edit) main problem seems to be that the type is reported as 'geometry' or 'geography' but no UDT type is specified. The driver doesn't pick it up. We'll add the UDT type if the type is loadable, and will see if it is picked up then.

(edit) driver picks up type now, doesn't create a typeshortcut automatically while it does that automatically for a user UDT, so a little bug left in that area. Likely because it's not truly part of the catalog. Will make it part of the catalog (as the assembly seems to be registered in each catalog separately).

Ok!

It's fixed. The fix I attach here is not the 100% fix, as a small part is in the applicationcore, but you can work around that for now.

3 assemblies were affected: the sql server driver (attached), BCL extensions (attached) and applicationcore (not attached). The part in application core is for the automatic typeshortcut addition for a udt. You can work around this by adding the type shortcuts manually by right-clicking the project node -> Edit type shortcuts, and then add two type shortcuts, one for geometry and one for geography.

Do that before you reverse engineer the entities, as they'll be used automatically then.

About using the functions in queries: they're not available to you automatically, so you if you want to use it in Linq, you have to map a custom function (See 'Linq to LLBLGen Pro' documentation for details how to do that, it's a couple of lines of code). After that, you can use the method in a linq query which is then converted to the sql function.

Unpack the attached zip in the llblgen pro installation folder, it will overwrite the bcl extensions dll and the sqlserver driver. Please note the workaround I described above about the type shortcuts.

Load your project, refresh the catalog and the types should be picked up. You can see that in the catalog explorer -> show details and in the type shortcuts dialog.

Full fix is available in the next build

Wow!

That was quick . Thanks.

Ok, back to this, after a day of upgrading my project to version 3.

For some reason, the spatial data types aren't in the list of types to create a shortcut for.

Its definitely in the GAC. And the version in the GAC includes the spatial types. I've reflected it

Anything else I need to do?

thanks again.

Are the tables in the catalog explorer reflecting the types? (so if you right-click on 'fields' of such a table, and select 'show details' do you see the fields as typed geometry etc?

(I presume you do use the sql server driver I attached above)

Nope - It's typed varchar

I believe I am using the driver you sent.

I overwrite the existing one in C:\Program Files (x86)\Solutions Design\LLBLGen Pro v3.0\Drivers\SqlServer

with this file:

• SD.LLBLGen.Pro.DBDrivers.SqlServerDBDriver.dll

Version 3.0.0.0

07 ‎September ‎2010, ‏‎02:53:06 PM

thanks

Sorry - I've just refreshed, and the field is correctly mapped.

Not sure what happened, might not have refreshed the catalog after overwriting the driver previously

I'll see how I get on..

Yes a refresh was necessary because the UDTs weren't read into your project before.

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..

Is this working correctly using ADO.NET? Will using a SqlGeography in a SQLParamater will wrap the value in single comma?

Hi

I haven't tried ADO.Net. I guess I could get that to work, but our app uses the LINQ to LLBLGEN wrapper, and I am trying to get the spatial functionality working with mapped functions - as outlined by Frans earlier in the thread..

Thanks,

I know what you are trying to do, I was trying to hunt the problem down.

If this can be reproduced with ADO.NET or with Linq2QSql then most probably it's not an issue in LLBLGen.

Ok - got you - I'll write a quick test case passing a SqlGeography as a SQLParameter

Ok - here's the test case:

        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);

        cmd.Parameters.Add(new SqlParameter("@a", a) { UdtTypeName = "Geography" });
        cmd.Parameters.Add(new SqlParameter("@b", b) { UdtTypeName = "Geography" });

        cmd.ExecuteScalar();

This works fine.

In Sql Profiler i see this generated code:

declare @p3 sys.geography
set @p3=convert(sys.geography,0xE6100000010C00000000000000000000000000000000)
declare @p4 sys.geography
set @p4=convert(sys.geography,0xE6100000010C000000000000F03F000000000000F03F)
exec sp_executesql N'select dbo.getdistancespatial(@a,@b) ',N'@a Geography,@b Geography',@a=@p3,@b=@p4

Any pointers from here would be appreciated...

thanks,

Our framework doesn't have support for setting the UdtTypeName property of a parameter, as the info isn't available in the meta-data.

The thing is though that it shouldn't be necessary to set that property, as every UDT type has to be convertible to string. That's also how we save UDT typed objects, they convert themselves to string, that's send to the db, all is well.

If you don't set UdtTypename, but set the SqlDbType to UDT, it should work properly, could you test that for me please?

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

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 ..

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!