- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
Is not compatible with npgsql.timestamptz
Joined: 17-Apr-2009
I am using LLBLGEN 3.1 (June 17, 2011). Windows 7 and PostgresSQL 8.4.1 Npgsql 2.0.11.91 .NET 4.0 EF 4.1
I am able to generate the code from the LLBLGEN Designer. But when I try to compile the generated project I get the following error message in the EDMX file.
Error 1 Error 2019: Member Mapping specified is not valid. The type 'Edm.DateTime[Nullable=False,DefaultValue=,Precision=]' of member 'EffectiveDateTime' in type 'PriceBookModel.SetupItemPricing' is not compatible with 'Npgsql.timestamptz[Nullable=False,DefaultValue=,Precision=7]' of member 'effective_date_time' in type 'PriceBook.PostgreSql.Store.setup_item_pricing'. C:\llblgen\pb\PriceBook.edmx 93 9 SSCS.DAL.EF.POSTGRES.PriceBook
Error 2 Error 2019: Member Mapping specified is not valid. The type 'Edm.DateTime[Nullable=False,DefaultValue=,Precision=]' of member 'ExpirationDateTime' in type 'PriceBookModel.SetupItemPricing' is not compatible with 'Npgsql.timestamptz[Nullable=False,DefaultValue=,Precision=7]' of member 'expiration_date_time' in type 'PriceBook.PostgreSql.Store.setup_item_pricing'. C:\llblgen\pb\PriceBook.edmx 94 9 SSCS.DAL.EF.POSTGRES.PriceBook
Here is the EDMX File
<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="2.0" xmlns:edmx="http://schemas.microsoft.com/ado/2008/10/edmx">
<edmx:Runtime>
<edmx:StorageModels>
<Schema Namespace="PriceBook.PostgreSql.Store" Alias="Self"
Provider="Npgsql" ProviderManifestToken="8.4.1"
xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
<EntityContainer Name="PriceBookPostgreSqlContainer">
<EntitySet Name="setup_item_pricing" EntityType="Self.setup_item_pricing" Schema="cpb" />
<!-- __LLBLGENPRO_USER_CODE_REGION_START SSDLEntityContainerCustomCode -->
<!-- __LLBLGENPRO_USER_CODE_REGION_END -->
</EntityContainer>
<EntityType Name="setup_item_pricing">
<Key>
<PropertyRef Name="pack_size"/>
<PropertyRef Name="setup_id"/>
<PropertyRef Name="source_setup_id"/>
<PropertyRef Name="effective_date_time"/>
<PropertyRef Name="expiration_date_time"/>
<PropertyRef Name="price_type_name"/>
<PropertyRef Name="item_state_name"/>
<PropertyRef Name="id_item_coding"/>
</Key>
<Property Name="pack_size" Type="int2" Nullable="false" />
<Property Name="setup_id" Type="int4" Nullable="false" />
<Property Name="added_audit_id" Type="int4" />
<Property Name="last_modified_audit_id" Type="int4" />
<Property Name="source_setup_id" Type="int4" Nullable="false" />
<Property Name="is_in_master" Type="bool" />
<Property Name="effective_date_time" Type="timestamptz" Nullable="false" />
<Property Name="expiration_date_time" Type="timestamptz" Nullable="false" />
<Property Name="price_type_name" Type="varchar" Nullable="false" MaxLength="50" />
<Property Name="price_name" Type="varchar" MaxLength="50" />
<Property Name="price_description" Type="varchar" MaxLength="100" />
<Property Name="price" Type="numeric" />
<Property Name="item_state_name" Type="varchar" Nullable="false" MaxLength="50" />
<Property Name="id_item_coding" Type="varchar" Nullable="false" MaxLength="30" />
</EntityType>
<!-- __LLBLGENPRO_USER_CODE_REGION_START SSDLCustomCode -->
<!-- __LLBLGENPRO_USER_CODE_REGION_END -->
</Schema>
</edmx:StorageModels>
<edmx:ConceptualModels>
<Schema Namespace="PriceBookModel" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2008/09/edm">
<EntityContainer Name="PriceBookEntities">
<EntitySet Name="SetupItemPricing" EntityType="Self.SetupItemPricing"/>
<!-- __LLBLGENPRO_USER_CODE_REGION_START CSDLEntityContainerCustomCode -->
<!-- __LLBLGENPRO_USER_CODE_REGION_END -->
</EntityContainer>
<EntityType Name="SetupItemPricing">
<Key>
<PropertyRef Name="EffectiveDateTime"/>
<PropertyRef Name="ExpirationDateTime"/>
<PropertyRef Name="IdItemCoding"/>
<PropertyRef Name="ItemStateName"/>
<PropertyRef Name="PackSize"/>
<PropertyRef Name="PriceTypeName"/>
<PropertyRef Name="SetupId"/>
<PropertyRef Name="SourceSetupId"/>
</Key>
<Property Name="PackSize" Nullable="false" Type="Int16" />
<Property Name="SetupId" Nullable="false" Type="Int32" />
<Property Name="AddedAuditId" Type="Int32" />
<Property Name="LastModifiedAuditId" Type="Int32" />
<Property Name="SourceSetupId" Nullable="false" Type="Int32" />
<Property Name="IsInMaster" Type="Boolean" />
<Property Name="EffectiveDateTime" Nullable="false" Type="DateTime" />
<Property Name="ExpirationDateTime" Nullable="false" Type="DateTime" />
<Property Name="PriceTypeName" Nullable="false" Type="String" MaxLength="50" FixedLength="false" Unicode="true" />
<Property Name="PriceName" Type="String" MaxLength="50" FixedLength="false" Unicode="true" />
<Property Name="PriceDescription" Type="String" MaxLength="100" FixedLength="false" Unicode="true" />
<Property Name="Price" Type="Decimal" Scale="0" />
<Property Name="ItemStateName" Nullable="false" Type="String" MaxLength="50" FixedLength="false" Unicode="true" />
<Property Name="IdItemCoding" Nullable="false" Type="String" MaxLength="30" FixedLength="false" Unicode="true" />
</EntityType>
<!-- __LLBLGENPRO_USER_CODE_REGION_START CSDLCustomCode -->
<!-- __LLBLGENPRO_USER_CODE_REGION_END -->
</Schema>
</edmx:ConceptualModels>
<edmx:Mappings>
<Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2008/09/mapping/cs">
<EntityContainerMapping StorageEntityContainer="PriceBookPostgreSqlContainer"
CdmEntityContainer="PriceBookEntities">
<EntitySetMapping Name="SetupItemPricing">
<EntityTypeMapping TypeName="IsTypeOf(PriceBookModel.SetupItemPricing)">
<MappingFragment StoreEntitySet="setup_item_pricing">
<ScalarProperty Name="PackSize" ColumnName="pack_size" />
<ScalarProperty Name="SetupId" ColumnName="setup_id" />
<ScalarProperty Name="AddedAuditId" ColumnName="added_audit_id" />
<ScalarProperty Name="LastModifiedAuditId" ColumnName="last_modified_audit_id" />
<ScalarProperty Name="SourceSetupId" ColumnName="source_setup_id" />
<ScalarProperty Name="IsInMaster" ColumnName="is_in_master" />
<ScalarProperty Name="EffectiveDateTime" ColumnName="effective_date_time" />
<ScalarProperty Name="ExpirationDateTime" ColumnName="expiration_date_time" />
<ScalarProperty Name="PriceTypeName" ColumnName="price_type_name" />
<ScalarProperty Name="PriceName" ColumnName="price_name" />
<ScalarProperty Name="PriceDescription" ColumnName="price_description" />
<ScalarProperty Name="Price" ColumnName="price" />
<ScalarProperty Name="ItemStateName" ColumnName="item_state_name" />
<ScalarProperty Name="IdItemCoding" ColumnName="id_item_coding" />
</MappingFragment>
</EntityTypeMapping>
</EntitySetMapping>
<!-- __LLBLGENPRO_USER_CODE_REGION_START MSLEntityContainerMappingCustomCode -->
<!-- __LLBLGENPRO_USER_CODE_REGION_END -->
</EntityContainerMapping>
<!-- __LLBLGENPRO_USER_CODE_REGION_START MSLCustomCode -->
<!-- __LLBLGENPRO_USER_CODE_REGION_END -->
</Mapping>
</edmx:Mappings>
</edmx:Runtime>
<!-- Designer element for auto-embedding the .edmx file into the compiled assembly as separated resource files and to validate it on build. -->
<edmx:Designer xmlns="http://schemas.microsoft.com/ado/2008/10/edmx">
<edmx:Connection>
<DesignerInfoPropertySet>
<DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
</DesignerInfoPropertySet>
</edmx:Connection>
<edmx:Options>
<DesignerInfoPropertySet>
<DesignerProperty Name="ValidateOnBuild" Value="true" />
</DesignerInfoPropertySet>
</edmx:Options>
<!-- __LLBLGENPRO_USER_CODE_REGION_START DesignerCustomCode -->
<!-- __LLBLGENPRO_USER_CODE_REGION_END -->
</edmx:Designer>
</edmx:Edmx>
The problem is, the npgsql provider maps timestamptz types to System.DateTime (so a timestamptz value from the db is returned by the datareader as a DateTime). It's a bit odd that this doesn't compile... (or better: that they refuse Edm.DateTime as a valid type). If you manually change it to Edm.Time, does it accept the type in that case / does it compile in that case? If so, I'll file an issue with the npgsql people.
Joined: 17-Apr-2009
Sorry Otis. I mis-spoke. The LLBLGEN generated project does compile. It just shows those errors in the Error List for Visual Studio 2010 for the EDMX.
If I change it to EDM.Time it still compiles but give the following errors about the EDMX.
Error 1 Error 2019: Member Mapping specified is not valid. The type 'Edm.Time[Nullable=False,DefaultValue=,Precision=]' of member 'EffectiveDateTime' in type 'PriceBookModel.SetupItemPricing' is not compatible with 'Npgsql.timestamptz[Nullable=False,DefaultValue=,Precision=7]' of member 'effective_date_time' in type 'PriceBook.PostgreSql.Store.setup_item_pricing'. C:\llblgen\pb\PriceBook.edmx 93 9 SSCS.DAL.EF.POSTGRES.PriceBook
Error 2 Error 2019: Member Mapping specified is not valid. The type 'Edm.Time[Nullable=False,DefaultValue=,Precision=]' of member 'ExpirationDateTime' in type 'PriceBookModel.SetupItemPricing' is not compatible with 'Npgsql.timestamptz[Nullable=False,DefaultValue=,Precision=7]' of member 'expiration_date_time' in type 'PriceBook.PostgreSql.Store.setup_item_pricing'. C:\llblgen\pb\PriceBook.edmx 94 9 SSCS.DAL.EF.POSTGRES.PriceBook
Wonder how that Precision =7 ended up in there... Does the timestamptz field in the table have a precision of 7? As it's only emitted into the EDMX when the type is a decimal...
Then I totally don't understand why the error specifies 'precision=7' as it is very odd that it is even emitted: the EDMX property emit method doesnt emit precision for fields other than decimals.
Looking at your initial EDMX you posted, there's no 'precision' attribute in the whole file.... Are you sure this error comes from this edmx?
Joined: 17-Apr-2009
Attached is a sample to repo the issue. The generated project compiles but displays this message
Error 1 Error 2019: Member Mapping specified is not valid. The type 'Edm.DateTime[Nullable=True,DefaultValue=,Precision=]' of member 'LogDate' in type 'TestTSZoneModel.TestTable' is not compatible with 'Npgsql.timestamptz[Nullable=True,DefaultValue=,Precision=7]' of member 'log_date' in type 'TestTSZone.PostgreSql.Store.test_table'. C:\bugs\TestTSZone\TestTSZone.edmx 50 50 TestTSZone
SQL Script to Generate TestTSZone Database -- Database: "TestTSZone"
-- DROP DATABASE "TestTSZone";
CREATE DATABASE "TestTSZone" WITH OWNER = sscsdev ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1;
SQL Script to Generate test_table
- Table: test_table
-- DROP TABLE test_table;
CREATE TABLE test_table ( log_id integer NOT NULL, log_date timestamp with time zone, CONSTRAINT pk_test_table PRIMARY KEY (log_id) ) WITH ( OIDS=FALSE ); ALTER TABLE test_table OWNER TO sscsdev;
Filename | File size | Added on | Approval |
---|---|---|---|
TimestampzBug.zip | 11,407 | 08-Jul-2011 00:55.47 | Approved |
I have no idea why this is. I have posted a thread on the pgfoundry forum for npgsql: http://pgfoundry.org/forum/forum.php?thread_id=10599&forum_id=519
Question is of course... does this have any effect at runtime? (though errors after build which don't really terminate the build process might still be something you'd like to avoid)
It's a difference between the manifest they have for EF and the native provider which returns datetime. According to the developer of npgsql they didn't want to break compatibility by changing timestamptz to datetimeoffset (IMHO not an option, it's not a type present in .net 2) but they do return datetimeoffset for EF, hence the problem.
We'll add code to the EF templates to change this, although it kind of sucks that they have this difference.
It's a catch-22, and this problem is not going to be fixed in the current version on our side, as we can't fix it without breaking users of postgresql with other frameworks.