Random Dev Notes

February 5, 2012

Using VFP Entity Framework Provider with Free Tables

Filed under: Development — Tags: — Tom Brothers @ 10:42 pm

Introduction

To my surprise it seems that the majority of people interested in using the VFP Entity Framework Provider are targeting Free Tables. Fortunately, I took special care during development of the provider to ensure that it would work as best as it could with Free Tables. In this blog entry I’ll walk through an example using the provider with Free Tables.

For this example I will “borrow” the blog Free Table DBFs from the Web Connection shareware download.

(This post assumes you’ve already installed the VFP Entity Framework Provider)

Getting Started

Create a simple console application.

image

Create the Entity Framework Data Model.

  1. Add a new item.
    image
  2. Select “ADO.NET Entity Data Model” and enter BlogModel as the name.
    image
  3. Select “Generate from database.”
    image
  4. Create a New Connection.
    image
  5. Enter the Data Source and Provider. (Notice that the Data Source is the directory that contains the DBFs)
    image
  6. Don’t need to change anything on this screen. Just click next.
    image
  7. Select all tables.
    image

Here is the Entity Framework Model that was created.

image

Now look at the Error List panel. Wonder what those Warnings and Messages mean or how to resolve them? This issue will be discussed later

image

Query Some Data

The Model has been created and we are ready to query the data. For this example, I want to query the Blog_Lookups table.

Here is the Model. Notice the key icon on all the properties? Add this to the list of things that will be discussed later.

image

Here is an example of querying the data sending it to the console.

image

Console Output

image

 

Lets take a second to recap what we’ve done. We create a project that included an Entity Framework Model that was created from Free Tables and we were able to query these tables. All this basically happened in the matter of minutes.

I know, I know… you could’ve done this quicker in VFP but still getting this done in minutes in .Net is pretty cool… huh?

(now that I’m done patting my self on the back… lets get back to business :))

Alter the Model

Did you notice that when I wrote to the console I conceptualized some of the Model property names? I wrote out a label of “Abbreviation” instead of “Cdata”, “Name” instead of “Cdata1”, and “Description” instead of “Cdata2.” Well the cool thing with ORMs, such as Entity Framework, is that you can make such changes to the Model. More specifically what I’m trying to point out is that the Model property names do not need to match the table column names. I personally find the ability to change the property names useful when having to deal with the Free Table’s 10 character column name limitation. Additionally, you can change change the Entity name without needing to change the table name.

Here is an example of modifying the Model (just the Model was changed… not the underlying table).

image
image

Update (failure)

Ok, we have a Model and we even tweaked it so that our code is a little more readable (by changing the Entity name and property names)… so what is next? Well, we’d like to modify the data right?

Here is code to find an Entity, change the Name, and then save the change.

image

It didn’t work!!!

image

Looking at the exception message, what does it mean that the “Name” is part of the object’s key information mean…?

One Step Forward Two Steps Back…

Remember the issues that I pushed off for a “discussion later?” Well now is the time. The update didn’t work due to these two issues (which is really one issue).

The first issue was the Warnings and Messages in the Error List. Here is the Error List that relates to the Blog_Lookups.

image

The second issue is the key indicator on all the properties. Entity Framework, by default, uses all the properties as a composite primary key when it cannot identify an actual primary key for the table. When it does this it actually places the Model in a read-only state which is why the update fails.

image

So how do we fix these issues (errr… this issue)? Well the Error List clearly states that we are missing a primary key on the table doesn’t it?

Candidate Key=Primary Key

Free Tables don’t have Primary Keys so does that mean we can’t update data using the VFP Entity Framework Provider? Of course not. The provider is aware of the fact that Free Tables can’t have primary keys so it treats Candidate Keys as Primary Keys. So in this example, if we add a Candidate Key to the Blog_Lookups table prior to creating the Model…

image

… the Model will identify the Candidate Key as a Primary Key as seen in the following Model. (Notice that only the Pk property has a key icon)

image

 

* There is another way to modify the model without changing the table structure. If you are interested in this option I’d suggest reading Julia Lerman’s Programming Entity Framework, Second Edition book to learn how to modify the xml that defines the Models.

8 Comments »

  1. Hello, nice job with VFP EF Provider! However, I have experienced an issue trying to modify some dBase III .DBF files. When I would call ctx.SaveChanges(), it would display an error saying “Too many arguments.”. Is VFP EF Provider able to modify dBase III .DBF files?

    Comment by dejancg — May 5, 2012 @ 8:51 am

    • That error message makes me think of a scenario that I had to deal with when writing LINQ to VFP. The issue was due to a user whose computer settings used a comma for the decimal separator. In this case the decimal value was written directly out in the insert statement (instead of using a “?” parameter). Consequently the insert statement interpreted the commas as it always does by considering them as another column value. I was able to fix this issue by ensuring that decimal values were always inserted as parameters. This “fix” has yet to make it into the VFP EF Provider as I’m still working refining the Command Tree (which is what is used to generate the VFP code).

      If you are using commas as a decimal separator then you will need to wait for the next build which should be in a couple weeks. Otherwise, could I get a copy of your project so I can debug the issue (trbrothers@hotmail.com)? Feel free to zap the tables if you have concerns with handing me your data.

      Comment by Tom Brothers — May 5, 2012 @ 11:27 am

      • Thanks for the quick response! It makes sense. The computer I do development on indeed uses comma for the decimal separator. I am actually working on a big project which is slowly coming to a conclusion. This partucular component I need your provider for should be communicating with a Datecs fiscal printer via some 3rd party application. That application expects that I follow the specified DBF file layout, for which I have empty files. I fill an empty file with the data I want to send, copy the file to a certain folder, make sure the fiscal printer has accepted it and then delete all records from the file using foreach (var obj in ctx.Table) ctx.Table.DeleteObject(obj); followed by ctx.SaveChanges(); I am sending these empty files to your e-mail address now.

        Comment by dejancg — May 5, 2012 @ 12:24 pm

      • Here is a copy of the project that I used to test the VFP EF Provider with your tables. Included is an example using LINQ to VFP. Since you have a comma for a decimal, I’d expect the EF project to fail for you but the LINQ to VFP project should work fine.

        Comment by Tom Brothers — May 6, 2012 @ 3:51 pm

      • Thanks for the test project. However, it seems the EF project works fine on my PC (!). Could it be because it’s code-first this time, and not DB first as in my project? Also thanks for the linqtovfp suggestion, I will take a look at it.

        Comment by dejancg — May 7, 2012 @ 2:31 am

      • My mistake… the project used whole numbers as a test. Change one of the whole numbers to a decimal and then you should see the expected results.

        Comment by Tom Brothers — May 7, 2012 @ 6:56 am

      • Actually I have noticed that and tried with a decimal number before I posted the new comment. I don’t get the difference. The code-first approach using the EntityFramework.dll you supplied with the sample project results in the same “Too many arguments” message when used in my project – however it worked fine when used in the test project. Anyway, I gave LINQ to VPF a shot and it seems to work just fine.

        Comment by dejancg — May 7, 2012 @ 8:14 am

  2. Thanks for sending me your data. I was able to verify that everything worked as expected with the dBase III files. So that means that the problem is related to having the decimal separator.

    Given the fact that you said that you are close to wrapping up the project I’d suggest that you look at LINQ to VFP (http://linqtovfp.codeplex.com/) since a stable build is currently available… opposed to the VFP EF Provider which is currently an alpha build.

    Comment by Tom Brothers — May 6, 2012 @ 12:55 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Customized Shocking Blue Green Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: