LLBLGen.com Forums Database drivers feed

SQL Server 2008 by Otis

Otis — Thu, 09 Sep 2010 15:21:39 GMT

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)

SQL Server 2008 by MattE

MattE — Thu, 09 Sep 2010 15:07:56 GMT

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.

SQL Server 2008 by MattE

MattE — Tue, 07 Sep 2010 13:24:06 GMT

Wow!

That was quick . Thanks.

SQL Server 2008 by Otis

Otis — Tue, 07 Sep 2010 13:20:19 GMT

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

SQL Server 2008 by Otis

Otis — Tue, 07 Sep 2010 11:26:55 GMT

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

SQL Server 2008 by MattE

MattE — Tue, 07 Sep 2010 10:46:25 GMT

SQL Server 2008 by Otis

Otis — Mon, 06 Sep 2010 15:16:29 GMT

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.

SQL Server 2008 by MattE

MattE — Mon, 06 Sep 2010 14:09:20 GMT

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

SQL Server 2008 by Walaa

Walaa — Mon, 06 Sep 2010 14:06:14 GMT

The sql server driver should have picked them up.

SQL Server 2008 by MattE

MattE — Mon, 06 Sep 2010 11:33:40 GMT

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