Non AutoInc primary keys

Jan 10, 2013 at 3:57 PM

We have primary keys that are integer but are not AutoInc. Their value is created using a Table rule that looks up the next number in another table.

This table rule works but the problem is when a header table is created with child entries in another table. The child table does not have the parent's ID.

This does work if you add new child entries to an existing header table.

and it also works when AutIoinc integers are used.

 

 

Coordinator
Jan 10, 2013 at 5:06 PM

I can’t think of a way the provider could be changed to accommodate that type of schema. The reason why is because I wouldn’t have a reliable way of retrieving the primary key after the insert. When using an AutoInc I’m able to call GetAutoIncValue() after the insert to retrieve the primary key. The problem in your case is that the header entity’s primary key property doesn’t get updated after the insert.

 

If you can’t change the primary keys to AutoInc then you probably should retrieve and set the entity’s primary key prior to calling SaveChanges.

Coordinator
Jan 11, 2013 at 4:16 PM

I thought about this some more and realized that my original response suffered from tunnel vision.  I put together  a simple ADO.Net example proving that I can get the primary key value from the last insert.

 

//CREATE DATABASE c:\_Data\AutoGenIdDb
//CREATE TABLE c:\_data\AutoGenIdTest(PK v(250) DEFAULT SYS(2015))

var connectionString = @"provider=vfpoledb;data source=c:\_Data\AutoGenIdDb.dbc";

using(var connection = new OleDbConnection(connectionString)) {
    using(var command = connection.CreateCommand()) {
       
        command.CommandText = "insert into AutoGenIdTest(cValue) values('" + Guid.NewGuid().ToString() + "')";

        connection.Open();
       
        command.ExecuteNonQuery();
        command.CommandText = "=AutoGenIdTest.pk";
       
        command.ExecuteScalar().Dump();
        connection.Close();
    }
}

 

 

Now I just need to see if I can modify the provider to work like this example. 

I'll let you know when I figure something out.

Jan 11, 2013 at 4:47 PM

 

Thanks.

Coordinator
Jan 18, 2013 at 12:06 AM

I posted an update that should work for you.

Jan 18, 2013 at 2:20 PM

 

Thanks, but I am having problems.

I can no longer Add a new ADO.NET Entity model or Update model from database.

Everything looks OK the tables are shown but they do not get pulled over to the Model.

Jan 18, 2013 at 2:53 PM

 

More info

Unable to generate the model because of the following exception: 'Object reference not set to an instance of an object.'.

I tried it on a Windows 7 64bit Machine on Visual Studio 10 and 12

and on a Windows Vista 32bit using Visual studio 10.

Coordinator
Jan 18, 2013 at 5:04 PM

I was able to reproduce that error when testing with data that didn't have views.  Does your data have views?  If not, the latest build should work for you.

Feb 13, 2013 at 2:27 PM
Just come back to this and I started by downloading your latest version (7.9)

I can add a new entity data model but when ever I try to acces the data I get.

"Unable to find the requested .Net Framework Data Provider. It may not be installed"

I using VS2012 on Windows 7 64bit.

My Machine.Config has the following
<DbProviderFactories>
   <add name="Vfp Entity Framework Provider" invariant="VfpEntityFrameworkProvider" description="Vfp Entity Framework Provider" type="VfpEntityFrameworkProvider.VfpProviderFactory, VfpEntityFrameworkProvider, Version=0.7.9.9923, Culture=neutral, PublicKeyToken=feace53afe38fe48" />
</DbProviderFactories>
Coordinator
Feb 13, 2013 at 2:40 PM
Make sure that your project is targeting x86.

Project Properties -> Build -> Platform Target -> x86
Feb 13, 2013 at 4:21 PM
Thanks again for your fast response.

Back to my original problem.
Here is a link to the foxpro database I am using for testing.

ftp://support.anagram-sys.co.uk/SimpleData.zip

I would appreciate if you could have a look at it.

Basically there is three types of tables.
There is a pair of tables for each type. Header table and lines (child)
  1. AutoInc Integer Primary keys (Works fine)
  2. Integer primary keys that get their value for a stored procedure (table valid). Stores records but the childs parentid is blank
  3. Character primary keys that get their value for a stored procedure (table valid). Does not work at all.
My C# code looks like this
        using (var simplecontext = new simpleEntities { })
        {

            // Test tables that use Integer keys that get their value from stored procedure
            Hilohead head = new Hilohead
            {
                Text = "HiLoHead:" + System.DateTime.Now.ToString()
            };

            Hiloline line = new Hiloline
            {
                Text = "Hiloline: " + System.DateTime.Now.ToString()
            };
            head.Hilolines.Add(line);
            simplecontext.Hiloheads.Add(head);
            simplecontext.SaveChanges();
        }
    }


Coordinator
Feb 14, 2013 at 2:55 PM
I successfully tested all three of your use cases. There were two things that needed to be done to get them to work.
  1. In the Entity Model you need to make sure that the primary keys have StoreGeneratedPattern=Identity. This is done automatically for the AutoInc primary keys.
  2. Change the non-AutoInc primary key fields in the table to allow nulls. I know that this isn't needed when running within VFP code but it is required when inserting from OleDb. Below is an example that you can use to see the error.

var connectionString = @"provider=vfpoledb;data source=C:\SimpleData\Simple.dbc";

using(var connection = new OleDbConnection(connectionString)) {
    using(var command = connection.CreateCommand()) {
        command.CommandText = "insert into Hilohead(Text) values('HiLoHead:" + System.DateTime.Now.ToString() + "')";
 
        connection.Open();
       
        command.ExecuteNonQuery();
        command.CommandText = "=HiloHead.HiloHeadId";
       
        command.ExecuteScalar().Dump();
        connection.Close();
    }
}
Feb 14, 2013 at 5:00 PM
Thanks one again.