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.

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

Hey Otis,

There is not precision or scale set. It is just taking the default for timestamptz column in postgres. Which shows no precision in the column and 0 as the scale.

Thanks,

Charlie J.

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?

Hey Otis,

Yes. I am very sure this is the correct error for the embedded EDMX. I have tried several times and get the same error message whenever I use a timestamptz in my table.

Thanks,

Charlie J.

Could you please attach the project file, or better a repro.

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;

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

Thanks Otis. I saw the thread. It is very odd. If I change the log_date field from timestamptz to just timestamp there isn't any problems. They both map to EDM.DateTime.

Thanks,

Charlie J.

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)

I was thinking the same thing Otis. I changed it to Timestamp without zone for now. But I will be switching back before the software is release. We are in a Beta 1 phase at the moment.

Thanks,

Charlie J.

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.