For postgresql 5.10.126 EF wrong sql, double quotes used

[atamgp]

Hi,

I had working code using EF CTP4. Now i updraged tot latest EF version and 5.10.126 of Devart dotConnect for postgreSQL and get some errors.

For a single table query, the following is generated:

Code: Select all

{SELECT 
"Extent1"."ID" AS "ID", 
"Extent1"."name" AS "name", 
"Extent1".code AS code, 
"Extent1"."countryID" AS "countryID", 
"Extent1"."coordinatenID" AS "coordinatenID", 
"Extent1"."creationDate" AS "creationDate", 
"Extent1"."creationUserID" AS "creationUserID", 
"Extent1"."lastChangedDate" AS "lastChangedDate", 
"Extent1"."lastChangedUserID" AS "lastChangedUserID"
FROM dbo.city AS "Extent1"}

which causes a Devart.Data.PostgreSql.PgSqlException exception with:
column Extent1.ID does not exist"

This is understanable because i get the same when i try to execute this query directly in the database.

For every columns, this part is the problem: ."ID"
So every

Code: Select all

"Extent1"."XY" AS "XY"

should be =>

Code: Select all

"Extent1".XY AS XY

Then it runs. So in my belief, wrong code is being generated.
Can someone take a look at this?

Thanks,
Atam

[AndreyR]

The build with full Entity Framework 4.1 RTM support will be available in a week or so.
Update. However, this problem may be associated not with EF 4.1. Could you please send us a small test project? If you are not using either Code Only or the Model First approach, then please include the DDL script of the DB objects you are using in the test project.

[atamgp]

Hi, thanks for reply.
The code for my city entity (left other entities out for now):

Code: Select all

public class DbModel : DbContext
    {
        public DbModel(DbConnection conn)
            : base(conn,false)
        {
            this.Configuration.LazyLoadingEnabled = true;
            this.Configuration.ProxyCreationEnabled = false;
            this.Configuration.ValidateOnSaveEnabled = false; //based on data annotations on domain entities
            this.Configuration.AutoDetectChangesEnabled = true; // ??? TODO
        }

        public MetadataWorkspace MetadataWorkspace
        {
            get
            {
                return (this as IObjectContextAdapter).ObjectContext.MetadataWorkspace;
            }

        }

        public ObjectStateManager ObjectStateManager
        {
            get
            {
                return this.ObjectStateManager;
            }
        }

        public DbSet City { get; set; }


        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Configurations.Add(new CityMap());       

            modelBuilder.Conventions.Remove();
            
            base.OnModelCreating(modelBuilder); 
        }

Code: Select all

public abstract class BaseEntity : ICloneable
    {
        public virtual Guid ID { get; set; }
        public virtual DateTime? creationDate { get; set; }
        public virtual Guid? creationUserID { get; set; }
        public virtual DateTime? lastChangedDate { get; set; }
        public virtual Guid? lastChangedUserID { get; set; }
 }
public class City : BaseEntity
    {

        public virtual String name { get; set; }
        public virtual String code { get; set; }

        public virtual Country country { get; set; }
        public virtual Guid countryID { get; set; }

        public virtual Coordinates coordinaten { get; set; }
        public virtual Guid coordinatenID { get; set; }
    }

Code: Select all

 public class CityMap : EntityTypeConfiguration
    {
        public CityMap()
        {
            HasKey(x => x.ID);
            Property(x => x.ID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity); // .IsIdentity().StoreGeneratedPattern = StoreGeneratedPattern.None;
            Property(x => x.creationDate);
            Property(x => x.creationUserID);
            Property(x => x.lastChangedDate).IsConcurrencyToken().HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed);  // .StoreGeneratedPattern = StoreGeneratedPattern.None;
            Property(x => x.lastChangedUserID);

            Property(x => x.name).HasMaxLength(25).IsRequired();
            Property(x => x.code).HasMaxLength(5).IsFixedLength().IsRequired();

            HasRequired(x => x.country)
                .WithMany().HasForeignKey(x => x.countryID);

            //When an City is deleted, we delete its coordinates also.
            HasRequired(x => x.coordinaten).WithMany().HasForeignKey(x => x.coordinatenID)
                .WillCascadeOnDelete(true);

            Map(k => k.Properties(t => new
            {
                t.ID,
                t.creationDate,
                t.creationUserID,
                t.lastChangedDate,
                t.lastChangedUserID,

                t.name,
                t.code,

                t.countryID,
                t.coordinatenID
            })).ToTable("city", "dbo");
        }
    }

I updated this code from working CTP4 code.

[AndreyR]

The quoting is designed in case you are using non-lowercase names.
However, the upcoming Beta build contains the PostgreSqlEntityProviderConfig.Workarounds class. The DisableQuoting property turns the quoting off for all queries and commands. Here is the code sample:

Code: Select all

PgSqlEntityProviderConfig config = PgSqlEntityProviderConfig.Instance;
config.Workarounds.DisableQuoting = true; 

[atamgp]

Hi AndreyR,

Have you looked at the code?
I just tried upgrading to 5.10.141. No difference.

I execute a query like this:

var query= new DbModel(...).Set().AsQueryable();
items = query.ToList();

Here it goes wrong....
I can see the generated sql in the query object, which I posted previously.
The sql is just not compatible with postgre (9).

[atamgp]

oeps... i didn't see your post.

"the upcoming Beta build"
when will this be available?

thanks

[AndreyR]

We plan to release the Beta build this month.
You can try to set the Database Initialization Strategy to DropCreateDatabaseAlways, for example, the tables with correct names will be created, if you are still in the process of development (and data is not critical for production).

[atamgp]

I can't, because we use a db tool to create and update/migrate our database.
And I saw that this tool generates tables and columns without quotes.

In postgre this is translated into lowercase string then which casues problem in sql generation compatibility.

e.g. SELECT "Extent1"."ID" AS "ID" ... is generated stating that ID is a highercase string.
SELECT "Extent1".ID AS "ID" ... would work

So i have to wait for the new release with the DisableQuoting option

Greetz

[AndreyR]

The Beta build is released today. Here is the code sample:

Code: Select all

PgSqlEntityProviderConfig config = PgSqlEntityProviderConfig.Instance;
config.Workarounds.DisableQuoting = true;

Don't forget to add the %Program Files%\Devart\dotConnect\PostgreSQL\Entity\EF4\Devart.Data.PostgreSql.Entity.dll assembly as Reference to your project.

[atamgp]

HI, just installed it, can't find PgSqlEntityProviderConfig in any assembly....

[AndreyR]

Please download the build again. We have resolved the issue.
Sorry for the inconvenience.

[atamgp]

Perfect! it work! Thanks