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.

Blog at WordPress.com.