Dear LLBLGen Team,

I have been working with Postgresql with Postgis. Recent alpha releases of Npgsql start to support Postgis geometry datatypes. Using ADO.net interface, Npgsql will read Postgis geometry as PostgisGeometry type.

However, using latest alpha Npgsql, LLBLGen Designer 4.2 to recognize Postgis Geometry as String type, which is not correct and will cause runtime casting exception.

I had worked with TypeConverter, and I think create some dummy TypeConverter may work, but I don't really need conversion.

I tried to force the .net entity field type to System.Object in the designer, but the designer thinks there is an error and asks for a TypeConverter and prevent me from generating code.

So, I am wondering, is there a way to force field type to System.Object and ignore errors in the designer when generating code because I know the type will be right at runtime.

Sincerely,

Hi Matthew,

I dont' think LLBLGen will support this until a stable and official version of Ngpsql would be out there (not alpha). I think a TypeConverter is suitable here.

What is the problem with the TypeConverter approach?

to elaborate on the string type: this isn't as hopeless as it looks: the postgis type is only known as an npgsql type, so to work with that type we need to have a reference to npgsql, which isn't really what should be done. A string representation is therefore not that bad, it can be used to convert back/forth to the npgsql native type if you need that. This is the same system as we're using for Microsoft's geospatial types which are defined in different assemblies outside sqlclient and sqlserver.

TypeConverter may works fine. It is just the situation doesn't need converting. Frankly speaking, I found out I could modify the .llblgenproj, change database field type to some DbType="15" in order to force LLBLGen recognize that field as Object. So that I can generate code and use it.

With Npgsql 2.2.1.0 the string type works. But since Npgsql 3, treating it as string time will throw exception when reading. This is also mentioned here. https://github.com/npgsql/npgsql/issues/807

I am wondering does it make sense to let user force the .Net type in to System.Object, to let the user deal with it at runtime. (Frankly speaking, I thought I did such thing before , just choose System.Object in the drop down. But now, if I do so, code generating is prevented.)

the table you define in the github issue indeed results in the 'text' type for geom. Looking into it.

(edit). The type is reported as 'USER-DEFINED', not as 'geometry'. I'll see if this is done by the v3 npgsql but I doubt it. very strange.

(edit). This looks like to be the case since the beginning... I think in older postgresql versions it was reported as datatable='point', in v9 it's reported as 'user-defined' and thus falls back onto text. Can this be the case in your situation?

I'll adjust the driver so it will check the udt_data_type info as well, to see whether it can find a type there.

I've attached a debug driver build for you to test. It should properly convert your geometry types to 'object' in which case you can do 2 things: 1) create a type converter to convert this back/forth to the proper npgsql type automatically, which will make the field of the actual Npgsql type

or

2) set the value in your code to the proper npgsql type.

It will make the code generate the type of the field in the db as 'geometry' so it will use NpgsqlTypes.Geometry, if available, otherwise it will not set the type on the parameter.

I've also defined the sort order for 'object' to use Geometry first, so you can use object as .net type in your entity to use forward mapping (model first) to get geometry types, as all object defined types in postgres are postgis types anyway.

Copy the attached dll as administrator into the drivers\postgresql folder.

Please let me know if this fixes/doesn't fix your problem

Thank you very much for the updates. Quick question, Do I need to provide a "driver Id" when I overwrite the existing one? It seems the Designer is not picking up the driver when I use the new assembly you attached. I tried to use the .dll GUID, but it didn't work.

matthewma wrote:

Thank you very much for the updates. Quick question, Do I need to provide a "driver Id" when I overwrite the existing one? It seems the Designer is not picking up the driver when I use the new assembly you attached. I tried to use the .dll GUID, but it didn't work.

Reproduced. It's not the GUID. The application output says:

DatabaseDriverStore::Error loading driver 'PostgreSql 7.4+ Driver (Npgsql)' from file 'C:\Program Files (x86)\Solutions Design\LLBLGen Pro v4.2\Drivers\PostgreSql\SD.LLBLGen.Pro.DBDrivers.PostgreSqlDBDriver.dll': Could not load file or assembly 'file:///C:\Program Files (x86)\Solutions Design\LLBLGen Pro v4.2\Drivers\PostgreSql\SD.LLBLGen.Pro.DBDrivers.PostgreSqlDBDriver.dll' or one of its dependencies. Operation is not supported. (Exception from HRESULT: 0x80131515). The driver will be unavailable.

We will look into it...

Perhaps it's blocked by windows? Could you right-click the dll in windows explorer-> properties -> unblock (if that's available) ?

The dll is like the one in the installed package so no external dependencies.

Yes. Thank you, it was blocked by windows.

But after I unlock it, I notice an issue.

I replace the old DLL with the new one in LLBLGen Pro v4.2\Drivers\PostgreSql. No matter what I do to the DriverID in the driver.config, when I create new project and try to "Add relational model data from database", no database structure will be generated in the Catalog Explorer.

If I save the project, and open it. Designer will say "The XML contains a reference to a driver with ID: '88EBFD8C-CBDD-4452-88AF-1C99E41A123F' which isn't loaded, likely due to a missing ADO.NET provider."

Then, I manually replace DriverID in driver.config and all occurrence of the 88...23F guid with '02639D71-0935-35E8-9D1B-9DD1A2A34627', which is the guid I think it is of the new DLL.

Now I can open the project and will see database structure in the Catalog Explorer.

But if I try to reverse engineer to entity, I got null exception at SD.LLBLGen.Pro.ApplicationCore.ProjectClasses.Project.ReverseEngineerNormalRelationships(IEnumerable1 targetedCatalogs, HashSet1 entitiesToProcess, MultiValueDictionary2 entitiesToProcessPerTarget, String driverID, MultiValueDictionary2 fksToIgnorePerGroup, HashSet1& ucsToIgnore, MultiValueDictionary2& newNavigatorsPerEntityDefinition) at SD.LLBLGen.Pro.ApplicationCore.ProjectClasses.Project.CreateNewEntityDefinitionsFromRawDefinitions(List`1 rawEntityDefinitions) at SD.LLBLGen.Pro.Gui.Classes.GuiController.<>c__DisplayClass76.<PerformReverseEngineerEntitiesFromTargetsAction>b__75() at SD.LLBLGen.Pro.Gui.Classes.GuiController.<>c__DisplayClass84.<WrapAndExecuteCommandForSlowProjectManipulation>b__83() at SD.Tools.Algorithmia.Commands.CommandQueueManager.PerformUndoablePeriod(UndoablePeriodCommand cmd, Action codeToExecuteInPeriodFunc) in c:\Myprojects\VS.NET Projects\Algorithmia_hg\SD.Tools.Algorithmia\Commands\CommandQueueManager.cs:line 299 at SD.LLBLGen.Pro.Gui.Controls.CatalogExplorer._commandManager_CommandClick(Object sender, CommandEventArgs e) upper stack omitted

No guid has been changed, so you shouldn't change anything in the driver.config file (or anywhere else). It's important that the guid in the driver.config file is kept as-is, which is for postgresql driver 88EBFD8C-CBDD-4452-88AF-1C99E41A123F

The guid you specify is not a known driver ID (the only driver id starting with 2 is sqlserver's but its different from the one you specify). Please use 88EBFD8C-CBDD-4452-88AF-1C99E41A123F and don't change it, as you shouldn't have to. The DLL I posted is simply build with our build system as it's build for the installer as well, so use that one as-is We never change guids / driverid's ever, as that will break everyone's project using that driver.

Thank you, it worked like a charm Sorry that I was messing with the driverID.

Did you fix the TimeSpan issue of this ticket http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=23643 in the DLL as well? The TimeSpan issue seems fixed now.

Also, is this related to https://github.com/npgsql/npgsql/issues/529#issuecomment-173316927 as well? Saving PostgisGeometry also works now.

matthewma wrote:

Thank you, it worked like a charm Sorry that I was messing with the driverID.

Did you fix the TimeSpan issue of this ticket http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=23643 in the DLL as well? The TimeSpan issue seems fixed now.

Yes all fixes are always included

Also, is this related to https://github.com/npgsql/npgsql/issues/529#issuecomment-173316927 as well? Saving PostgisGeometry also works now.

Yes. What we did was basically make sure that npgsql knows it's a geometry type and the value in the parameter is then up to npgsql what to do with it.

Glad it's working, I'll release a new full build of the installer

Thank you for all your help.