Force a Guid to be stored as VARCHAR using EF code first

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
GerbenRampaart
Posts: 4
Joined: Wed 20 Jul 2011 09:01
Location: Rotterdam, The Netherlands

Force a Guid to be stored as VARCHAR using EF code first

Post by GerbenRampaart » Tue 02 Oct 2012 09:00

Hi Devart, my question is basically in the title. By default (using dotConnect 7.0), when I annotate a field as guid, it gets created as a 'RAW' field in Oracle. Which makes it less convenient to be altered in SQL Developer. I'd like it to be stored as VARCHAR2. This doesn't seem work:

Code: Select all

        
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
[Column("ID", TypeName = "VARCHAR2")]        
public Guid ID { get; set; }
It leads to the 'The Type VARCHAR2 is not qualified with a namespace or alias' exception. I already set the following workarounds:

Code: Select all

OracleEntityProviderServices.HandleNullStringsAsEmptyStrings = true;

OracleEntityProviderConfig config = OracleEntityProviderConfig.Instance;
                     
config.Workarounds.IgnoreSchemaName = true;
config.Workarounds.ColumnTypeCasingConventionCompatibility = true;
config.Workarounds.DisableQuoting = false;
            
config.DmlOptions.BatchUpdates.Enabled = true;
config.DmlOptions.ReuseParameters = true;
config.DmlOptions.InsertNullBehaviour = InsertNullBehaviour.Omit;
Any ideas?

Thanks in advance,
Gerben.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Force a Guid to be stored as VARCHAR using EF code first

Post by Shalex » Tue 09 Oct 2012 12:25

There is no direct way to store Guid in the VARCHAR2 column. We recommend you to use a property-wrapper (GuidId in the sample) to make conversions in your .NET code:

Code: Select all

    class Program {
        static void Main(string[] args) {
            new OracleMonitor() { IsActive = true };

            using (var db = new MyDbContext()) {
                db.MyEntities.Add(new MyEntity { GuidId = Guid.NewGuid(), Data = "asfd" });
                db.SaveChanges();

                foreach (var e in db.MyEntities) {
                    Console.WriteLine(e.GuidId);
                }
            }

        }
    }

    public class MyEntity {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        [Column("ID", TypeName = "VARCHAR2")]
        //[MaxLength(xxx)]
        //[EditorBrowsable(EditorBrowsableState.Never)]
        public string ID { get; set; }

        public string Data { get; set; }

        [NotMapped]
        public Guid GuidId {
            get {
                return new Guid(ID);
            }
            set {
                ID = value.ToString("N");
            }
        }
    }

    public class MyDbContext : DbContext {

        static MyDbContext() {

            var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
            OracleEntityProviderServices.HandleNullStringsAsEmptyStrings = true;

            config.Workarounds.IgnoreSchemaName = true;
            config.Workarounds.ColumnTypeCasingConventionCompatibility = true;
            config.Workarounds.DisableQuoting = false;

            config.DmlOptions.BatchUpdates.Enabled = true;
            config.DmlOptions.ReuseParameters = true;
            config.DmlOptions.InsertNullBehaviour = InsertNullBehaviour.Omit;
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder) {

        }
        public DbSet<MyEntity> MyEntities { get; set; }
    }

GerbenRampaart
Posts: 4
Joined: Wed 20 Jul 2011 09:01
Location: Rotterdam, The Netherlands

Re: Force a Guid to be stored as VARCHAR using EF code first

Post by GerbenRampaart » Tue 09 Oct 2012 12:36

Hi Shalex, thanks for the reply;

I've considered your solution and this certainly works when working with the EF 'layer' directly. The thing is, we use Devart in an enterprise which utilizes web services extensively. We connect allot of systems over http.

The combo database -> data layer (with devart) -> web service is a much seen solution here.

For this, we really like WCF Data Services, it allows for easy mapping and programmability. However, in a WCF Data Services scenario, the contract which is exposed over the wcf binding will be that containing the ID as string because the Guid is [NotMapped].

Now, there are ways around this, I realize that, like throwing exceptions when the provided value is not parsable as guid and such. But still, it sure would be nice to just be able to set the datatype of a guid column.

Anyway, thanks for the answer and maybe you'd consider this in the future.

Gerben.

Alladin
Posts: 149
Joined: Mon 27 Nov 2006 16:18
Contact:

Re: Force a Guid to be stored as VARCHAR using EF code first

Post by Alladin » Wed 10 Oct 2012 08:55

I'd suggest to declare Guid column as string all the way through tiers.
Guid type is well supported type in general. So use a string as a Id property type, just write there Guid.ToString() and you are good to go ;)

Post Reply