This project has moved. For the latest updates, please go here.

Using GUID's as ID fields

Nov 5, 2015 at 4:23 PM
Edited Nov 5, 2015 at 4:50 PM
First off, kudos on the Provider, nice work!

I have been busy plumbing this provider into a project which had the requirement to be able to use MS Access, SQL Server, MySQL and Oracle as customer selectable databases so your provider is perfect for what I need.

One issue I am facing which I could do with some pointers about is as follows:


I am Using code first and a migration to create the tables etc.

I need to use a GUID as the datatype of my ID columns, so in MS Access the column would be:

for auto assigned ID's:
Data Type = AutoNumber
Field Size = Replication ID
Indexed = Yes (No Duplicates)

for manually assigned ID's:
Data Type = Number
Field Size = Replication ID
Indexed = Yes (No Duplicates)

And my C# declarations simply look like:
 public Guid Id { get; set; }
or for manually set ID's:
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.None)]
    public Guid Id { get; set; }
and in the case of foreign keys they are occasionally nullable.

When I try and create the initial DB migration script (add-migration "initial" I get the following error:
_
(7,12) : error 2019: Member Mapping specified is not valid. The type 'Edm.Guid[Nullable=False,DefaultValue=]' of member 'Id' in type 'TestApp.DataAccess.Api.DataAccessLayer.Document' is not compatible with 'Jet.int[Nullable=False,DefaultValue=]' of member 'Id' in type 'CodeFirstDatabaseSchema.Document'._

Any pointers would be appreciated,

Regards

JT

edit

I realised that this may be due to a different between Jet and ACE. I am using 'Microsoft.ACE.OLEDB.12.0' as my underlying provider. From comments in your code I think that maybe Jet had no GUID support at all in older versions of Access but that ACE would have support in it for new versions of Access?

I have been using the empty.mdb file which came with the test app but have been using it via MS Access 2013.
Coordinator
Nov 6, 2015 at 5:26 AM
Edited Nov 6, 2015 at 6:56 AM
Hi,
actually the provider does not support Guid at all.
I admit that I never used them in Access so I don't know how to use them and I also don't know how to implement DDL, DML and SQL to implement and use them.
Anyway, I try to have a look to understand if is possible and how support Guid.


EDIT
Some consideration (source https://support.microsoft.com/en-us/kb/170117):
CREATE TABLE MyGUIDTable (MyGUIDField GUID)

Returned value in select statements (is it a string or a varbinary???)
{11223344-1122-1122-1122-AABBCCDDEEFF}

Syntax in INSERT, SELECT and UPDATE
{guid {11223344-1122-1122-1122-AABBCCDDEEFF<AngularNoBind>}}</AngularNoBind>

INSERT INTO MyGUIDTable (MyGuidField) VALUES
({guid {11223344-1122-1122-1122-AABBCCDDEEFF<AngularNoBind>}}</AngularNoBind>)


SET MyGuidField = {guid {11223344-1122-1122-1122-AABBCCDDEEFF<AngularNoBind>}}</AngularNoBind>


Athor suggestion to generate and use guid.
In this case is just prefixed with a P and used like a string
    private void InsertGUID(){
        String strGuid = "P" + System.Guid.NewGuid().ToString();
        AccessDataSource ds = new AccessDataSource();
        ds.DataFile = "C:\\App_Data\\db1.mdb";
        ds.InsertCommand = "INSERT INTO xyz (ID) VALUES('" + strGuid + "')";
        ds.Insert();
    }
EDIT2
It seems that identity(1,1) does not generate new guids (http://stackoverflow.com/questions/3573138/how-to-create-guid-autonumber-programaticaly-in-ms-access)
The right statement could be
CREATE TABLE MyTable (Id Guid DEFAULT GenGUID())
Nov 6, 2015 at 8:32 AM
Edited Nov 6, 2015 at 8:34 AM
Hey thanks for the swift reply! i'll take a look through the links you suggested.

I already spent a fair amount of time last night seeing if I could get Guid's working and so far have got to the following functionality (I've never bothered with DB first so all my focus is on code first):

done:
  1. The provider now accepts that GUIDs are a valid data type
  2. When specifying that a C# entities property is of type GUID the corresponding field in the created table is of type Number \ Replication ID
  3. When reading in an existing record GUID properties are correctly populated
to do:
  1. If the field is an identity column (which is to be auto-generated) then I need the corresponding field to instead be of type AutoNumber \ Replication ID <-- this is what I am working on fixing next.
  2. When updating a record a GUID field must be correctly set <-- this is not working at present, I think because at present the generated SQL is of this format:
SET MyGuidField = 11223344-1122-1122-1122-AABBCCDDEEFF
but from your reply perhaps should instead be:
SET MyGuidField = {guid {11223344-1122-1122-1122-AABBCCDDEEFF<AngularNoBind>}}</AngularNoBind>
  1. When inserting a new record where the GUID field is not AutoNumber then the field should be set <- not working at present, presumably the same reason as 2.
When (if!) I get the GUIDs working fully I'll happily create a new fork with the necessary updates if its of any help to anyone.


As a side note to the GUID issue, I've always had the following issue with the provider which I've not spent any time looking into as I want to be confident I could get GUIDs working first:

The first time my app runs, it executes the migration code, generates the tables etc and then displays a test record.

Subsequent runs always throw an error in the JetCommand.ExecuteDbDataReader method when reading from the MigrationHistory table:
CommandText = "SELECT TOP 1 \r\n[c].[CreatedOn] AS [CreatedOn]\r\nFROM [__MigrationHistory] AS [c]"
The error reported is 'No value given for one or more required parameters.',

The error can be ignored and the can just continue the code and it always then resumes ok. I just wandered if you may have an idea about the issue of the top of your head else i'll dig into it deeper later.


Once again, thanks for your time,

Regards
Coordinator
Nov 6, 2015 at 9:28 AM
About the error you can ignore it, EF is just testing if the table exists.

About the GUID
I solved several issues and actually I can work on created tables but there is a huge problem...
If you use an autogenerated guid the EF needs to know it but it seems that there is no way to retrieve it using OleDb.
Look at this question for more details:
http://stackoverflow.com/questions/33562581/ms-access-c-sharp-retrieve-the-latest-inserted-guid
Nov 6, 2015 at 2:01 PM
Ah I see, ok thanks I will just ignore the error.

Yeah that's a big shame about not being able to retrieve the auto-generated GUID ID. It's not necessarily a show stopper for me as since new GUIDs are 'guaranteed' unique I can generate them in C# on object instantiation and then drop the auto-number in favour of a normal number DB type.

It won't be as clean as being able to support auto number GUIDs but from what you've found and what I've been reading as well its unlikely to be possible.
Nov 7, 2015 at 8:01 PM
Regards the issue with Auto generating GUID Identity columns, I've come up with a solution which works for me. It allows me to have GUID Identity columns in my C# entities which automatically generate and store a GUID value. This was an important thing for me as i need the system to be able to swap between MSAccess, SQL Server Express, MySQL etc. etc. so it means I can keep the data tier consistent :D

I have added a new method to JetDmlBuilder, this searches through the return arguments of a new insert request. It is looking for a store generated Identity (of which there must only be one per table ofc) of type "guid":
        internal static string GetGuidArgs(DbNewInstanceExpression returning)
        {
            return ( from arg in returning.Arguments
                         where ((DbPropertyExpression)arg).Property.IsStoreGeneratedIdentity
                            && ((DbPropertyExpression)arg).Property.TypeUsage.EdmType.Name == "guid"
                        select ((DbPropertyExpression)arg).Property.Name
                    ).FirstOrDefault() ?? string.Empty;
        }
I've then updated the GenerateInsertSql method to make a call to this new method if the insert tree has a returning object e.g.
var guidIdentifierColumn = string.Empty;
var guidIdentifierValue = string.Empty;

if (tree.Returning != null)
{
    guidIdentifierColumn = GetGuidArgs(tree.Returning as DbNewInstanceExpression);

    if (guidIdentifierColumn != string.Empty)
    {
         guidIdentifierValue = "{" + Guid.NewGuid().ToString() + "}";
    }
}
This gives me the name any GUID identifier column in the insert and generates a GUID to use.

I have updated the block of code in this method which generates the insert SQL to add the identity column and its value if present.

Then I have updated the GenerateReturningSql method such that it takes the GUID value (which is passed into it now) into account e.g.
if (guidIdentifierValue != string.Empty)
{
   commandText.Append(guidIdentifierValue);
}
else
{
   commandText.Append("@@Identity");
}
This results in a SQL insert statement for a row which has a GUID Identity column as follows:
{insert into [Document]([Id], [Name], [UnitId], [CreatedOn], [LastAmendedOn], [CreatedBy_Id], [DocumentMetadata_Id], [LastAmendedBy_Id], [Organization_Id], [Standard_Id])
values ({b60ac7ca-d309-43ce-8363-1e1f700ecd43}, @p0, @p1, @p2, @p3, @p4, null, @p5, @p6, @p7);
select [Id]
from [Document]
where [Id] = {b60ac7ca-d309-43ce-8363-1e1f700ecd43}}
Prior to my changes the same request would have returned:
{insert into [Document]([Name], [UnitId], [CreatedOn], [LastAmendedOn], [CreatedBy_Id], [DocumentMetadata_Id], [LastAmendedBy_Id], [Organization_Id], [Standard_Id])
values (@p0, @p1, @p2, @p3, @p4, null, @p5, @p6, @p7);
select [Id]
from [Document]
where [Id] = @@Identity}
Important Note that with the changes I have made, the Identity column in the Access DB must be of type Number / Replication ID and NOT AutoNumber / Replication ID as I am essentially faking the DB generating the GUID and instead I am having the EF provider do it. This is only possible of course because generated GUIDs are unique.

You are undoubtedly much more familiar with the code than I so if you spot any better ways to do this then I would love to hear!

Thanks again,

JT
Coordinator
Nov 9, 2015 at 5:21 AM
I'm trying to figure out how to insert it on a connection but I think there's not a good way to do it.
So, I wait for the end of the stack overflow bounty then I will copy your code.
Nov 12, 2015 at 11:12 AM
Edited Nov 12, 2015 at 11:18 AM
Hi!

Whilst testing the changes i have made to the system to accomodate I have found an underlying issue with this provider, i've reverted all of my GUID based changed and am able to reproduce the issue whilst running against the current mainline version of the code.

The issue is that when you have a database structure such as this:
Document
    --> Metadata
    --> --> Variables
    --> --> Fields
Then when retieving the objects via a standard call such as:
    var documents = this.db.Documents
        .Include(db => db.DocumentMetadata)
        .Include(db => db.DocumentMetadata.VariablesMetadata)
        .Include(db => db.DocumentMetadata.DocumentMetadataExpressions);
Then we get an exception of type 'Specified cast is not valid' in the JetDataReader classes GetInt32 method.

If the call is simplified by removing either of the child collections then the exception does not occur e.g.
    var documents = this.db.Documents
        .Include(db => db.DocumentMetadata)
        .Include(db => db.DocumentMetadata.DocumentMetadataExpressions);
        
and

    var documents = this.db.Documents
        .Include(db => db.DocumentMetadata)
        .Include(db => db.DocumentMetadata.VariablesMetadata);  
both run to completion with no issues.

I have included some of the generated SQL below and the call which causes the exception is against column 'C15' and the value of the field is "\u0001\0" which the function is trying to cast to an int.


Have you come accross this issue before and any ideas about the cause?

Regards

JT


Class structure:
    public class Document
    {
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public string Name { get; set; }
        public DocumentMetadata DocumentMetadata { get; set; }
    }

    public class DocumentMetadata
    {
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public string Name { get; set; }
        public ICollection<VariableMetadata> VariablesMetadata { get; set; }
        public ICollection<DocumentMetadataExp> DocumentMetadataExpressions { get; set; }
    }
    
    public class VariableMetadata
    {
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; } 
        public string Name { get; set; }
        public string Type { get; set; }
        public string DefaultValue { get; set; }
    }   
    
    public class DocumentMetadataExp
    {
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public Expression Expression { get; set; }
    }   
    
    public class Expression
    {
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public string Name { get; set; }
        public string Value { get; set; }
    }   
Exception details:
System.InvalidCastException was unhandled by user code
  HResult=-2147467262
  Message=Specified cast is not valid.
  Source=System.Data
  StackTrace:
       at System.Data.OleDb.ColumnBinding.ValueInt32()
       at System.Data.OleDb.OleDbDataReader.GetInt32(Int32 ordinal)
       at JetEntityFrameworkProvider.JetDataReader.GetInt32(Int32 ordinal) in C:\Development\C-Spec-development\Workspace\WeldOffice Suite\WeldOffice.Suite\JetEntityFrameworkProvider\JetDataReader.cs:line 135
       at lambda_method(Closure , Shaper )
       at System.Data.Entity.Core.Common.Internal.Materialization.Coordinator`1.ReadNextElement(Shaper shaper)
  InnerException: 

SQL which was generated and being run when the exception occured:
SELECT 
[UnionAll1].[Id] AS [C1], 
[UnionAll1].[Id1] AS [C2], 
[UnionAll1].[Name] AS [C3], 
[UnionAll1].[Id2] AS [C4], 
[UnionAll1].[Id3] AS [C5], 
[UnionAll1].[Name1] AS [C6], 
[UnionAll1].[C1] AS [C7], 
[UnionAll1].[Id4] AS [C8], 
[UnionAll1].[Id5] AS [C9], 
[UnionAll1].[Name2] AS [C10], 
[UnionAll1].[Type] AS [C11], 
[UnionAll1].[DefaultValue] AS [C12], 
[UnionAll1].[DocumentMetadata_Id] AS [C13], 
[UnionAll1].[C2] AS [C14], 
[UnionAll1].[C3] AS [C15], 
[UnionAll1].[C4] AS [C16], 
[UnionAll1].[C5] AS [C17]
FROM  (SELECT 
    IIf([Extent3].[Id] IS NULL, (null), 1) AS [C1], 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Id] AS [Id1], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[Id] AS [Id2], 
    [Extent2].[Id] AS [Id3], 
    [Extent2].[Name] AS [Name1], 
    [Extent3].[Id] AS [Id4], 
    [Extent3].[Id] AS [Id5], 
    [Extent3].[Name] AS [Name2], 
    [Extent3].[Type] AS [Type], 
    [Extent3].[DefaultValue] AS [DefaultValue], 
    [Extent3].[DocumentMetadata_Id] AS [DocumentMetadata_Id], 
    (null) AS [C2], 
    (null) AS [C3], 
    (null) AS [C4], 
    (null) AS [C5]
    FROM ( ( [Document] AS [Extent1]
    LEFT OUTER JOIN [DocumentMetadata] AS [Extent2] ON [Extent1].[DocumentMetadata_Id] = [Extent2].[Id])
    LEFT OUTER JOIN [VariableMetadata] AS [Extent3] ON [Extent3].[DocumentMetadata_Id] = [Extent1].[DocumentMetadata_Id])
UNION ALL
    SELECT 
    2 AS [C1], 
    [Extent4].[Id] AS [Id], 
    [Extent4].[Id] AS [Id1], 
    [Extent4].[Name] AS [Name], 
    [Extent4].[Id] AS [Id2], 
    [Extent5].[Id] AS [Id3], 
    [Extent5].[Name] AS [Name1], 
    (null) AS [C2], 
    (null) AS [C3], 
    (null) AS [C4], 
    (null) AS [C5], 
    (null) AS [C6], 
    (null) AS [C7], 
    [Extent6].[Id] AS [Id4], 
    [Extent6].[Id] AS [Id5], 
    [Extent6].[Expression_Id] AS [Expression_Id], 
    [Extent6].[DocumentMetadata_Id] AS [DocumentMetadata_Id]
    FROM ( ( [Document] AS [Extent4]
    LEFT OUTER JOIN [DocumentMetadata] AS [Extent5] ON [Extent4].[DocumentMetadata_Id] = [Extent5].[Id])
    INNER JOIN [DocumentMetadataExp] AS [Extent6] ON [Extent4].[DocumentMetadata_Id] = [Extent6].[DocumentMetadata_Id])
    WHERE [Extent6].[DocumentMetadata_Id] IS NOT NULL) AS [UnionAll1]
ORDER BY [UnionAll1].[Id1] ASC, [UnionAll1].[Id3] ASC, [UnionAll1].[C1] ASC
Coordinator
Nov 13, 2015 at 8:02 AM
I had some cast problem at the beginning (thats why I wrote JetDataReader) but in my opinion this issue is not related to the same problems.
For sure there is an issue that I can't solve that sometimes EF generates commands trees (and the EF Jet provider queries) too difficult to solve for Jet (nested queries) but in this issue the query can be run.

Could you also send the context, the mapping and some line of code to insert some sample data?
Coordinator
Nov 13, 2015 at 3:52 PM
About guid I made an update to the provider with the logic you suggested (actually there's no way to retrieve the guid).
I also updated the Ddl to create tables with autogenerated guid. In this case the behaviour of default is the same if autogenerated integers. If you insert a guid, Access will retain that guid. If the user insert a record with another method (i.e. Microsoft Access) the guid is generated by Access.
Nov 13, 2015 at 7:00 PM
Edited Nov 13, 2015 at 7:06 PM
Excellent, sounds great :)

Regards the issue I found with the casting, I realised that if I run the generated SQL direct in access then I can get a better look at what's going on. I've noticed that non-ID fields return correctly all the time, ID columns tho (either primary or foreign) always exhibit the casting issue when they are in a nested join.

I have been playing around with the SQL and I have found a fix for the issue. So I know how to fix it from a SQL perspective but need to figure out how/if the fix can be implemented into the SQL generator.

The fix is to wrap the effected fields in the select section of the join in a CStr thus casting it to a string - this seems to force the engine to use a consistent data type for the field - of course ideally we could casting it the desired format of the column but for simplicity I have just used CStr with the assumption that in the c# code I can then cast from a string to the relevant data type as each data type has its own Get function.

Some sample SQL to show what I mean - which relates to the SQL I posted previously:

Before:
    [Extent6].[Id] AS [Id4], 
    [Extent6].[Id] AS [Id5], 
    [Extent6].[Expression_Id] AS [Expression_Id], 
    [Extent6].[DocumentMetadata_Id] AS [DocumentMetadata_Id]
After:
    cstr([Extent6].[Id]) AS [Id4], 
    cstr([Extent6].[Id]) AS [Id5], 
    cstr([Extent6].[Expression_Id]) AS [Expression_Id], 
    cstr([Extent6].[DocumentMetadata_Id]) AS [DocumentMetadata_Id]
NOTE that the casting is only required in the Select section, there is no need to cast them in the Where clauses or joins etc. etc. I am proposing that we need to cast all the ID fields to a known datatype to better direct the engine and then cast them back to the correct data type in the C# layer.

I can send you a sample mdb file with some test data and queries showing the issue and resolution - but I don't know how I can send files on this forum.

I'll post up the code for a sample context etc when I get home later,

Regards

JT
Coordinator
Nov 14, 2015 at 6:33 PM
I'm quite sure that is a Jet/ACE bug... The problem is related to
    null AS [C2],
    null AS [C3],
    null AS [C4],
    null AS [C5]
of the first query.
Probably Jet assign char/varchar to the type of [C2]-[C5] and when the results of the first query are unioned to the results of the second query the fields are of the wrong type (string).
The behaviour is that the number 1 (0x00000001) is casted (C cast not a C# nice conversion) to a string (big endian so 0x01000000).
In my opinion the best behaviour is to trust the question of EF (that is asking for an int) and give it what it want.

I fixed JetDataReader.GetInt32 with this
    public override int GetInt32(int ordinal)
    {
        // Fix for discussion https://jetentityframeworkprovider.codeplex.com/discussions/647028
        object value = _wrappedDataReader.GetValue(ordinal);
        if (value is string)
        {
            byte[] buffer = Encoding.Unicode.GetBytes((string)value);
            int intValue = BitConverter.ToInt32(buffer, 0);
            return intValue;
        }
        else
            return _wrappedDataReader.GetInt32(ordinal);
    }


Nov 14, 2015 at 9:05 PM
Cool, nice approach, based on your suggestion I also changed the GetGuid function to be:
        public override Guid GetGuid(int ordinal)
        {
            // Fix for discussion https://jetentityframeworkprovider.codeplex.com/discussions/647028
            object value = _wrappedDataReader.GetValue(ordinal);
            if (value is byte[])
            {
                return new Guid((byte[])value); ;
            }
            else
                return _wrappedDataReader.GetGuid(ordinal);
        }
And that's now working too, which is awesome!
Coordinator
Nov 15, 2015 at 7:44 AM
Great!!!
Inserted also this fix!
Nov 16, 2015 at 6:14 AM
Great, thanks for the assistance, the provider is now passing all my tests. Will give you a shout if anything else crops up but its looking good!
Nov 24, 2015 at 8:16 PM
Edited Nov 24, 2015 at 8:33 PM
Hi Again

I found another issue in the provider - along the similar lines as those we unearthed whilst plumbing in the Guid support.

Under certain conditions the SQL generator will use a switch statement or an iif statement ( I think when there are child collections which have collections which have collections etc). In my test SQL the SQL fragment is:
    [Join2].[FieldMetadata_Id1] AS [FieldMetadata_Id], 
    Switch([Join2].[Id1] IS NULL, (null), [Join2].[Id3] IS NULL, (null),  true, 1) AS [C3], 
    IIf([Join2].[Id1] IS NULL, (null), 1) AS [C4]
The issue is that like the Guids and Id's before, when the switch (and I assume the iif) passes back 1 then the jet engine does not know it's data type so it is being passed around as a byte[4]. This means that in the GetInt32 method the cast is failing as ofc the byte array is not a valid Int32. So to fix this I have another condition in the GetInt32 to check if the value type is a byte[] or not. So my new GetInt32 function is now:
        public override int GetInt32(int ordinal)
        {
            // Fix for discussion https://jetentityframeworkprovider.codeplex.com/discussions/647028
            object value = _wrappedDataReader.GetValue(ordinal);

            if (value is byte[])
            {
                return BitConverter.ToInt32((byte[])value, 0);
            } else if (value is string)
            {
                byte[] buffer = Encoding.Unicode.GetBytes((string)value);
                int intValue = BitConverter.ToInt32(buffer, 0);

                return intValue;
            }
            else
                return _wrappedDataReader.GetInt32(ordinal);
        }
This fixes invalid cast run time error which I was getting.

Regards

JT