Random Dev Notes

October 23, 2011

VFP Entity Framework Provider (v0.5)

Filed under: Development — Tags: — Tom Brothers @ 7:46 pm

Introduction

I’m back it again… working on another LINQ provider for Visual FoxPro data.

Introduced back in 2009, LINQ to VFP was my first attempt at writing a LINQ provider that accessed Visual FoxPro data.  That provider worked well but had a few drawbacks that I was willing to live with at the time due to the lack of options.     The most notable drawbacks include not having a designer/code generator and requiring special code to get it to work with other Microsoft offerings such as WCF Data Services.  Additionally, there were some usability limitations due the framework that LINQ to VFP was build upon (IQToolkit).  I was able to code my way around most of these issues but I still felt that the whole process wasn’t all that developer friendly.  What I really wanted was a more integrated development process that could easily hook into new Microsoft offerings (such as LightSwitch).  So I started to look into what it would take to create an Entity Framework 4 Provider.  Fortunately I found that there was a starter project available.  I modified this project to work with Visual FoxPro data and now have what I’d like to consider a proof-of-concept build ready for testing.

* Note:  This build only works for “Database first” scenarios.

See In Action

A sample application (VfpEFQuerySamples) is available to see the provider in action.  This application is a copy of the ADO.NET Entity Framework Query Samples configured to work with Visual FoxPro data.

image2
* Notes:
  • Installing the Visual FoxPro Entity Framework Provider is not required to run this application
  • The VfpOleDb driver must be install otherwise you will see the following when you try to run the samples
    image5

Install Steps

  • Download and run the VfpEntityFrameworkProvider.msi from the codeplex site.

* UPDATE:  Starting with version 0.6.1, the installer will update the machine.config so no need to manually make this change.

  • Add an entry for the provider in C:WindowsMicrosoft.NETFrameworkv4.0.30319configmachine.config. 
    image8
    <add name="Vfp Entity Framework Provider" 
         invariant="VfpEntityFrameworkProvider" 
         description="Vfp Entity Framework Provider" 
         type="VfpEntityFrameworkProvider.VfpProviderFactory, VfpEntityFrameworkProvider, Version=0.5.0.0, Culture=neutral, PublicKeyToken=feace53afe38fe48"/>


How to add to a Project

  • Add a new ADO.NET Entity Data Model
    image11
  • Select “Generate from database”
    image14
  • Click on the “New Connection…” button and select the VFP Provider
    image17
  • Enter the connection information
    image20
  • Continue with the following wizard screens

    imageimage image image

  • After completing all the wizard screens you will see the EF designer and depending on your data you may see messages similar to the following.  This is expected behavior from the Entity Framework and is not specific to the Visual FoxPro Entity Framework Provider.
    image38

Deployment

There are two options for deploying an application using this provider. Both options required the provider information to be added to the machine.config or the project’s app.config/web.config.

image47
  1. Install the VfpEntityFrameworkProvider.msi. This installer includes the VfpOleDb driver.
  2. Include the VfpEntityFrameworkProvider.dll in the project. You will also need to ensure that the VfpOleDb driver is installed.

    image44


Known Issues

  • The generated sql statements could be improved to use more concise syntax
  • I haven’t figured out how to hook into “Generate Database” screen so it currently shows commands for Sql Server instead of Vfp.

    image50
  • The provider only woks in the non-Express versions of Visual Studio.  This is not because I’m intentionally restricting the Express Products but instead because I haven’t figured out the registry settings yet.

5 Comments »

  1. This looks excellent! I’m going to try it out… I was just creating a model right now (first time, used to using ado.net). The only problem I see is working with flat files as opposed to a dbc. Each company in my world is a folder full of tables. An external folder called global has shared tables like the list of companies etc… The syntax sucks to type all the time though, but can be done like so: select global.name, orders.no from `\\server1\share1\global` global, orders where orders.Globalid = global.id
    if you could figure out a neat way of doing that you’d be my hero lol… I’ve completely dumped datasets for loading data at this point. too slow to design, not very foxpro friendly syntax especially when using functions like IIF() or = .F. . I’ve been reduced to dynamically creating queries and auto-retrieving schemas to automatically take as little work as possible.

    Comment by kanaida — June 15, 2012 @ 7:58 pm

    • The VFP EF Provider works with free tables but I’ve never tried connecting to tables in different folders. I know it currently doesn’t work in this scenario but I’d like to see what could be done to make it work. Can I get a copy of your data structure to experiment with? If so, you can email me at trbrothers@hotmail.com.

      Comment by Tom Brothers — June 15, 2012 @ 9:00 pm

      • Hi Tom,

        I just try to use VFP Entity Provider with lightswitch, everything looks ok but when i try to get data from my foxpro database I receive the RED X, when I debug the application I get:

        [Microsoft.LightSwitch.DataService][Application:Error][LightSwitchApplication.DataSource1:Beipjobs_All] An exception has occurred: Microsoft.LightSwitch.DataServiceOperationException: The underlying provider failed on EnlistTransaction. —> System.Data.EntityException: The underlying provider failed on EnlistTransaction. —> System.InvalidOperationException: The ITransactionLocal interface is not supported by the ‘VFPOLEDB’ provider. Local transactions are unavailable with the current provider.
        at System.Data.OleDb.OleDbConnectionInternal.EnlistTransactionInternal(Transaction transaction)
        at System.Data.OleDb.OleDbConnectionInternal.EnlistTransaction(Transaction transaction)
        at System.Data.OleDb.OleDbConnection.EnlistTransaction(Transaction transaction)
        at VfpEntityFrameworkProvider.VfpOleDb.VfpConnection.EnlistTransaction(Transaction transaction)
        at System.Data.EntityClient.EntityConnection.EnlistTransaction(Transaction transaction)
        — End of inner exception stack trace —
        at System.Data.EntityClient.EntityConnection.EnlistTransaction(Transaction transaction)
        at Microsoft.LightSwitch.ServerGenerated.Implementation.DataProvider.LinqToEntitiesDataProvider`1.EnlistTransactionCore(Transaction transaction)
        at Microsoft.LightSwitch.ServerGenerated.Implementation.DataProvider.DataProvider.EnlistTransaction(Transaction transaction)
        at Microsoft.LightSwitch.ServerGenerated.Implementation.DataServiceImplementation`1.Microsoft.LightSwitch.ServerGenerated.Implementation.IServerDataServiceImplementationCore.set_Transaction(CommittableTransaction value)
        at Microsoft.LightSwitch.ServerGenerated.Implementation.DomainService`1.EnsureTransaction(Boolean isEditable)
        at Microsoft.LightSwitch.ServerGenerated.Implementation.DomainService`1.c__DisplayClasse.c__DisplayClass10.b__d()
        at Microsoft.LightSwitch.ServerGenerated.Implementation.DataServiceImplementation`1.Microsoft.LightSwitch.ServerGenerated.Implementation.IServerDataServiceImplementationCore.InvokeOperation[T](String operationName, Object[] args, Boolean invokedFromClient, Func`1 invokeOperation, Action catchCallback)
        — End of inner exception stack trace —

        I tried include in the connection string the paramenter: “OLE DB Services=-4” but it did not work. Can you please advice me.

        Thanks.

        Raul

        Comment by Raul — June 20, 2012 @ 10:54 am

      • I created a unit test for this scenario by wrapping a TransactionScope around a query and I got the same exception. But the exception went away when I added “OLE DB Services=-4” to my connection string. I’m not sure why it isn’t working in your project. Could you email me a copy of the project so that I could help you figure this out?

        Comment by Tom Brothers — June 20, 2012 @ 12:06 pm

  2. I sent the project to your email : trbrothers@hotmail.com
    Thank you,

    Comment by Raul — June 20, 2012 @ 12:49 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

Blog at WordPress.com.

%d bloggers like this: