Random Dev Notes

May 6, 2012

Using VFP Entity Framework Provider with Free Tables using Code First

Filed under: .Net Development — Tags: VFP EF Provider — Tom Brothers @ 3:37 pm

Introduction

In my previous post, titled “Using VFP Entity Framework Provider with Free Tables”, I outlined a way of connecting the VFP Entity Framework Provider with Free Tables. The post was really geared towards an “out of the box” experience – said another way it was more specifically an Entity Framework 4.0 way. Although I feel the post is relevant, it really didn’t sit well with me because it is not the way I would have done things. This post will outline another way of connecting Free Table DBFs with the VFP Entity Framework Provider using Code First (or Entity Framework 4.1+).

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

Getting Started

Create a console application.

Create the Entities.

Install the Entity Framework Power Tools
Use the Power Tools to generate the code first entities.
You’ll be prompted for the connection information.
A new Models folder has been added to the project.

Alter the Model

I’ll alter the Blog_Lookups table just list in the last blog post.

Here is the code that was generated:

Using Visual Studio’s refactoring I renamed the class name and some of the properties.

One other thing that needs to be done which I couldn’t do in the last example is set the primary key to the correct column.

* One benefit of the using the Code First approach makes it easier to set the primary key when your table doesn’t include a candidate key.

Comments (5)

February 5, 2012

Using VFP Entity Framework Provider with Free Tables

Filed under: Development — Tags: VFP EF Provider — 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.

Create the Entity Framework Data Model.

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

Here is the Entity Framework Model that was created.

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…

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.

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

Console Output

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).

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.

It didn’t work!!!

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.

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.

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…

… 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)

* 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.

Comments (15)

November 14, 2011

VFP Entity Framework Provider (v0.6.1)

Filed under: Development — Tags: VFP EF Provider — Tom Brothers @ 8:51 pm

I posted an update to the VFP EF Provider.

Changes:

Bug: Fixed an issue with getting the schema information when the connecting to free tables.

Installer: Modified the installer so that it will update the machine.config… so there is no need to manually change that machine.config anymore.

Installer: Added a registry setting so that the provider is available in the Visual Studio “Add Reference” dialog box.

Comments (1)

November 7, 2011

VFP Entity Framework Provider (v0.6)

Filed under: Development — Tags: VFP EF Provider — Tom Brothers @ 1:06 am

Introduction

Another VFP EF Provider build is ready for testing. The focus of this build was to get the provider to work with “Code First” scenarios.

The remainder of this blog entry will walk you though a simple example of using Code First. This example was derived from the ADO.NET team blog – EF 4.2 Code First Walkthrough.

Create the Application

Open Visual Studio 2010
File –> New –> Project..
Select “Windows” from the left menu and “Console Application”
Enter “CodeFirstSample” as the name
Select “OK”

Add References

Add EntityFramework.dll
Add VfpEntityFrameworkProvider.dll

Add Code

Replace the Project.cs code with the following:

using System;

using System.Collections.Generic;

using System.ComponentModel.DataAnnotations;

using System.Data.Entity;

using System.Linq;

namespace CodeFirstSample {

    class Program {

        static void Main(string[] args) {

            using (var db = new ProductContext()) {

                // Use Find to locate the Food category

                var food = db.Categories.Find("FOOD");

                if (food == null) {

                    food = new Category { CategoryId = "FOOD", Name = "Foods" };

                    db.Categories.Add(food);

                // Create a new Food product

                Console.Write("Please enter a name for a new food: ");

                var productName = Console.ReadLine();

                var product = new Product { Name = productName, Category = food };

                db.Products.Add(product);

                int recordsAffected = db.SaveChanges();

                Console.WriteLine("Saved {0} entities to the database.", recordsAffected);

                // Query for all Food products using LINQ

                var allFoods = from p in db.Products

                               where p.CategoryId == "FOOD"

                               orderby p.Name

                               select p;

                Console.WriteLine("All foods in database:");

                foreach (var item in allFoods) {

                    Console.WriteLine(" - {0}", item.Name);

                Console.WriteLine("Press any key to exit.");

                Console.ReadKey();

    public class ProductContext : DbContext {

        public DbSet<Category> Categories { get; set; }

        public DbSet<Product> Products { get; set; }

    public class Category {

        [MaxLength(10)]

        public string CategoryId { get; set; }

        [MaxLength(50)]

        public string Name { get; set; }

        public virtual ICollection<Product> Products { get; set; }

    public class Product {

        public int ProductId { get; set; }

        [MaxLength(50)]

        public string Name { get; set; }

        [MaxLength(10)]

        public string CategoryId { get; set; }

        public virtual Category Category { get; set; }

This example deviates from the ADO.NET team blog – EF 4.2 Code First Walkthrough when defining the model classes (Category and Product). The MaxLength attribute was added to let the provider know that the table field should be varchar instead of a memo.

Change App.Config

Add the provider information and connection string to app.config.

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

  <system.data>

    <DbProviderFactories>

      <clear />

      <add name="Vfp Entity Framework Provider"

                 invariant="VfpEntityFrameworkProvider"

                 description="Vfp Entity Framework Provider"

                 type="VfpEntityFrameworkProvider.VfpProviderFactory, VfpEntityFrameworkProvider, Version=0.6.0.0, Culture=neutral, PublicKeyToken=feace53afe38fe48" />

    </DbProviderFactories>

  </system.data>

  <connectionStrings>

    <add name="ProductContext"

             connectionString="provider=vfpoledb;data source=DataCodeFirst.dbc"

             providerName="VfpEntityFrameworkProvider" />

  </connectionStrings>

</configuration>

Run & Review

Run the application and then check the database container directory to see that the data files have been created.

October 23, 2011

VFP Entity Framework Provider (v0.5)

Filed under: Development — Tags: VFP EF Provider — 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.

* 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

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.~~

<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
Select “Generate from database”
Click on the “New Connection…” button and select the VFP Provider
Enter the connection information
Continue with the following wizard screens
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.

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.

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

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.
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.

Comments (5)

June 7, 2011

Retrieving Auto Generated Primary Key Values on Insert

Filed under: Development — Tags: IQToolkit Contrib — Tom Brothers @ 12:17 am

The default IQToolkit Insert method always seemed a little odd to me in that it didn’t populate an auto generated primary key property after executing the Insert statement. The following image demonstrates this issue. Notice how the Categoryid is 0 after the insert.

The toolkit does provide a way to get the primary key value but the process isn’t very intuitive (especially after working with other ORMs like LINQ to SQL). Here is how the toolkit provides a way to get the primary key value.

One problem with this approach is that it isn’t an option when using the Unit of Work (EntitySession) approach.

Personally, I ‘d rather have a simpler approach such as the following.

To achieve this simpler approach I needed to create a class that could handle the inserting of an Entity. This class needed to read the mapping information to see if the Entity had an auto-generated primary key. If the Entity did have an auto-generated primary key then the class needed to create an Insert function that could retrieve the new value and set it on the Entity instance. However, if the Entity didn’t have an auto-generated primary key the class would return a function that called the standard IQToolkit Insert method.

Here is the class that I came up with:

using System;

using System.Linq;

using System.Linq.Expressions;

using System.Reflection;

using IQToolkit;

using IQToolkit.Data;

using IQToolkit.Data.Common;

using IQToolkit.Data.Mapping;

namespace IQToolkitContrib {

    /// <summary>

    /// This class handles processing the Insert statement with special consideration given to AutoGenerated Primary Keys to ensure that

    /// the Entity's Primary Key property is updated after the Insert.

    /// </summary>

    /// <typeparam name="T">Entity Type.</typeparam>

    internal class DbEntityInserter<T> {

        private readonly IEntityTable<T> entityTable;

        private readonly Type entityType = typeof(T);

        private readonly MappingEntity mappingEntity;

        private readonly PropertyInfo primaryKey;

        private readonly Func<T, int> insert;

        public DbEntityInserter(IEntityTable<T> entityTable, DbEntityProvider provider) {

            if (entityTable == null) {

                throw new ArgumentNullException("entityTable");

            this.entityTable = entityTable;

            if (provider != null) {

                this.mappingEntity = provider.Mapping.GetEntity(this.entityType);

                this.primaryKey = this.GetPrimaryKey(provider);

            this.insert = this.GetInsertFunction();

        /// <summary>

        /// Executes the Insert for the Entity.

        /// </summary>

        /// <param name="instance">Entity instance.</param>

        /// <returns>Returns the Insert row count.</returns>

        public int Insert(T instance) {

            return this.insert(instance);

        /// <summary>

        /// Inspects the Entity mapping information to determine if the standard Insert statement should be used or if the insert statement should include

        /// the extra step of retrieving the AutoGenerated primary key.

        /// </summary>

        /// <returns>Returns the Insert function.</returns>

        private Func<T, int> GetInsertFunction() {

            if (this.primaryKey != null && this.IsGeneratedPrimaryKey()) {

                // Create a type array for the generic types for the Insert<T, S> method.

                Type[] genericTypes = new Type[] { this.entityType, this.primaryKey.PropertyType };

                LambdaExpression insertExpression = this.GetInsertExpression(genericTypes);

                MethodInfo insertMethod = this.GetInsertMethod(genericTypes);

                return instance => {

                    object id = insertMethod.Invoke(null, new object[] { this.entityTable, instance, insertExpression });

                    // Set the primary key property to the new Id value.

                    this.entityType.GetProperty(this.primaryKey.Name).SetValue(instance, id);

                    // Assuming if we got this far without an exception then the insert worked.

                    return 1;

};

            // Do the insert statement as normal if the Entity doesn't have an AutoGenerated primary key.

            return instance => this.entityTable.Insert(instance);

        /// <summary>

        /// Gets a reference to the IQToolkit.Updatable.Insert method.

        /// </summary>

        /// <param name="genericTypes">Generic Types Array for the Expression.</param>

        /// <returns>Returns IQToolkit.Updatable.Insert MethodInfo.</returns>

        private MethodInfo GetInsertMethod(Type[] genericTypes) {

            // MethodInfo for: (IQToolkit.Updatable) public static S Insert<T, S>(this IUpdatable<T> collection, T instance, Expression<Func<T, S>> resultSelector)

            MethodInfo mi = typeof(IQToolkit.Updatable).GetMethods()

                                                       .Where(d => d.Name == "Insert" && d.IsGenericMethod && d.ReturnType.FullName == null)

                                                       .First();

            return mi.MakeGenericMethod(genericTypes);

        /// <summary>

        /// Creates an Expression that will be passed to the IQToolkit.Updatable.Insert method

        /// </summary>

        /// <param name="genericTypes">Generic Types Array for the Expression.</param>

        /// <returns>Returns an Expression.</returns>

        private LambdaExpression GetInsertExpression(Type[] genericTypes) {

            // Create type for Expression<Func<T, S>>.

            Type functionExpressionType = Expression.GetFuncType(genericTypes);

            // Create an Expression Parameter.

            var parameter = Expression.Parameter(this.entityType, "instance");

            // Create a Lambda Expression for the Func<T, S> call.

            // Expression example:  x => x.Id

            return Expression.Lambda(functionExpressionType, Expression.Property(parameter, this.primaryKey.Name), parameter);

        /// <summary>

        /// Use reflection to determine if the primary key column was mapped as a generated column.

        /// </summary>

        /// <returns>Returns true if the primary key is generated by the database.</returns>

        private bool IsGeneratedPrimaryKey() {

            // Get a reference to the internal method GetMappingMember.

            MethodInfo getMappingMember = this.mappingEntity.GetType()

                                                             .GetMethod("GetMappingMember", BindingFlags.NonPublic | BindingFlags.Instance);

            // Invoke GetMappingMember.

            object mappingMember = getMappingMember.Invoke(this.mappingEntity, new object[] { this.primaryKey.Name });

            // Get the Column property from the mappingMember.

            ColumnAttribute columnAttribute = (ColumnAttribute)mappingMember.GetType()

                                                                            .GetProperty("Column", BindingFlags.NonPublic | BindingFlags.Instance)

                                                                            .GetValue(mappingMember);

            return columnAttribute.IsGenerated;

        /// <summary>

        /// Gets the primary key if the Entity has only one column for the primary key.  The assumption is that composite

        /// primary keys don't need to be retrieved after executing the Insert statement.

        /// </summary>

        /// <param name="provider">DbEntityProvider instance.</param>

        /// <returns>Returns the PrimaryKey PropertyInfo.</returns>

        private PropertyInfo GetPrimaryKey(DbEntityProvider provider) {

            MemberInfo primaryKeyMemberInfo = provider.Mapping

                                                      .GetPrimaryKeyMembers(this.mappingEntity)

                                                      .SingleOrDefault();

            return primaryKeyMemberInfo as PropertyInfo;

The next step was to hook the DbEntityInserter into the IQToolkit insert process. This was handled by creating a decorator class that implemented IEntityTable.

Here is the class that I came up with:

using System;

using System.Collections.Generic;

using System.Linq;

using IQToolkit;

using IQToolkit.Data;

namespace IQToolkitContrib {

    /// <summary>

    /// This class wraps the actions to the EntityTable to provide a hook into the Insert method.

    /// </summary>

    /// <typeparam name="T">Entity Type.</typeparam>

    internal class DbEntityTable<T> : IEntityTable<T> where T : class {

        private readonly IEntityTable<T> entityTable;

        private DbEntityInserter<T> inserter;

        public DbEntityTable(IEntityTable<T> entityTable) {

            if (entityTable == null) {

                throw new ArgumentNullException("entityTable");

            this.entityTable = entityTable;

        #region IEntityTable<T> Members

        public int Delete(T instance) {

            return this.entityTable.Delete(instance);

        public T GetById(object id) {

            return this.entityTable.GetById(id);

        public int Insert(T instance) {

            if (this.inserter == null) {

                this.inserter = new DbEntityInserter<T>(this.entityTable, this.Provider as DbEntityProvider);

            // Using the DbEntityInserter to handle the insert.

            return this.inserter.Insert(instance);

        public int InsertOrUpdate(T instance) {

            return this.InsertOrUpdate(instance);

        public int Update(T instance) {

            return this.entityTable.Update(instance);

        #endregion

        #region IEntityTable Members

        public int Delete(object instance) {

            return this.entityTable.Delete(instance);

        object IEntityTable.GetById(object id) {

            return this.entityTable.GetById(id);

        public int Insert(object instance) {

            return this.Insert(instance as T);

        public int InsertOrUpdate(object instance) {

            return this.entityTable.InsertOrUpdate(instance);

        public IEntityProvider Provider {

            get { return this.entityTable.Provider; }

        public string TableId {

            get { return this.entityTable.TableId; }

        public int Update(object instance) {

            return this.entityTable.Update(instance);

        #endregion

        #region IQueryable Members

        public Type ElementType {

            get { return this.entityTable.ElementType; }

        public System.Linq.Expressions.Expression Expression {

            get { return this.entityTable.Expression; }

        IQueryProvider IQueryable.Provider {

            get { return ((IQueryable)this.entityTable).Provider; }

        #endregion

        #region IEnumerable Members

        public System.Collections.IEnumerator GetEnumerator() {

            return this.entityTable.GetEnumerator();

        #endregion

        #region IEnumerable<T> Members

        IEnumerator<T> IEnumerable<T>.GetEnumerator() {

            return ((IEnumerable<T>)this.entityTable).GetEnumerator();

        #endregion

The final step was to simply hook into the Provider’s GetTable method and return an instance of the DbEntityTable.

Here is an example of a base class that I wrote to just that:

using System;

using IQToolkit;

using IQToolkit.Data;

namespace IQToolkitContrib {

    public abstract class DbEntityContextBase {

        private readonly DbEntityProvider provider;

        public DbEntityProvider Provider {

            get {

                return this.provider;

        public DbEntityContextBase(DbEntityProvider provider) {

            if (provider == null) {

                throw new ArgumentNullException("provider");

            this.provider = provider;

        protected IEntityTable<T> GetTable<T>() where T : class {

            string tableId = this.provider.Mapping.GetTableId(typeof(T));

            IEntityTable<T> table = this.provider.GetTable<T>(tableId);

            return new DbEntityTable<T>(table);

* This base class and a unit of work version can be found in the IQToolkitContrib download.

April 23, 2011

Accessing VFP data using LINQPad

Filed under: Development — Tags: LINQ to VFP, LINQPad — Tom Brothers @ 10:52 am

A LINQ to VFP user recently suggested that I write a LINQPad provider that could be used to access VFP data. This sounded like an interesting idea so I gave it a shot…

This blog entry provides a simple overview on how to use the driver. The first thing you need to do is get the driver – which can be found in the LINQ to VFP download or as part of the NuGet download.

Install the driver (must be x86 LINQPad build):

Click “Add connection” link
Click the “View more drivers…” button
Click the “Browse…” button
Open the LinqToVfpLinqPadDriver.lpx file

Connect to your database container (or free tables directory).

If you just added the LinqToVfpLinqPadDriver you will continue to the “Choose Data Context” screen (otherwise you need to hit the “Add connection” link)
Select the LINQ to VFP driver and then click the “Next >” button
Enter a full connection string or click the “Locate Database Container…” link which will build a connection string for you.

After installing the driver and setting up your connection you can start querying and manipulating your data.

Here is an example of a query.

You can also see the generated sql command.

Here is an example of manipulating the data.

* Cetin Basoz posted a video showing how to install the LINQPad driver.

Comments (31)

February 20, 2011

LINQ to VFP’s usage of XmlToCursor

Filed under: Development — Tags: LINQ to VFP — Tom Brothers @ 8:55 pm

About a year ago I wrote a blog entry explaining a change that was made to LINQ to VFP to help prevent the VFP error “1812 – Statement too long.” To be honest, I was never really satisfied with the solution… but at the time it seemed to be the only way I could deal with this annoying VFP error. Fortunately though, a much more knowledgeable VFP developer, Cetin Basoz, stumbled upon my blog entry and offered a more elegant solution to this problem. He pointed me to a link, that pre-dated my blog entry by a couple years, that suggested using the VFP’s XmlToCursor function instead of creating temp tables. Needless to say, I took his advice and modified LINQ to VFP accordingly.

In this blog entry, I will provide more details on a scenario that resulted in the VFP error, how it can be dealt with using ADO.Net, and the changes made to LINQ to VFP to avoid this error.

First off, let me say that what you are about to see here is a dramatization of actual events. The database names and table names have been changed to protect the innocent. 🙂

As a VFP developer, I’ve picked up a project that has been in production for many years. Prior to my involvement, a senior developer convinced the project owner that the company needed to move away from DBFs and start using SQL Server. The convincing argument had something to do with preventing corrupted memo files and index files. The product owner was tired of hearing from the support team that the customer had to lose some data due to these issues. In an effort to move forward, it was decided that new tables could only be created in SQL Server. Existing table would be move to SQL Server at a later time to be determined when it is more convenient. Unfortunately, the project owners never found a convenient time for converting the core tables. This ultimately resulted in a fractured data system which consequently produced less than optimal query performance.

This chain of events provides a very general idea of what led to the VFP error. Now lets dig into this idea a little more with a concrete example using the Northwind database.

Setting up the scenario…

Assume that at one time an Order could be for only one Product and all the information was stored in the Orders table. This data model seemed to satisfy the business needs for awhile but as time went on various pain points were attributed to this design. Consequently, it was decided that it was time to create an Orders Details table so that multiple Products could be part of a single Order. The new Order Details table would be created in SQL Server while the Orders and Products tables remained DBFs.

… with a basic scenario explained… time to move onto a work item

The product owner decided that what the application could really use is a report that provides a list of Customers that have received discounts on any Products. This would have been a trivial work item had the data been in one data system but now that it spread across two data systems this will require several steps.

The basic steps are:

Query SQL data for a list of Order Details records that have a discount
Get a distinct list of Order Ids from the discounted Order Details
Query VFP data for the Customer Information

Here is a code example of the steps listed above:

SqlData sqlData = new SqlData();

VfpData vfpData = new VfpData();

// Step 1:  get the OrderDetails that have a discount

DataTable orderDetails = sqlData.CreateDataTable("SELECT * FROM [Order Details] WHERE discount > 0");

// Step 2:  get a distinct list of order ids

string[] orderIds = orderDetails.AsEnumerable()

                             .Select(row => row.Field<int>("OrderId").ToString())

                             .Distinct()

                             .ToArray();

// Step 3:

//          build select statement that looks for all orders specified in the orderIds array

string sql = string.Format(@"

                select o.OrderId, c.CustomerId, c.CompanyName

                    from Orders o

                    inner join Customers c on upper(allt(o.CustomerId)) == upper(allt(c.CustomerId))

                    where o.OrderId in ({0})", string.Join(",", orderIds));

//          execute select statement... results in vfp error

DataTable orderCustomerInfo = vfpData.CreateDataTable(sql);

Here is the select statement VFP couldn’t handle:

select o.OrderId, c.CustomerId, c.CompanyName

    from Orders o

    inner join Customers c on upper(allt(o.CustomerId)) == upper(allt(c.CustomerId))

    where o.OrderId in (10250,10251,10252,10254,10258,10260,10262,10263,10264,10266,10267,10269,10273,10275,10279,10284,10285,10287,10288,10291,10298,10303,10305,10314,10324,10327,10329,10330,10332,10333,10335,10336,10339,10340,10341,10342,10343,10344,10346,10347,10348,10350,10351,10352,10353,10357,10358,10359,10361,10368,10369,10370,10371,10372,10373,10376,10377,10379,10380,10385,10388,10390,10393,10395,10397,10398,10403,10404,10406,10411,10412,10414,10417,10419,10420,10421,10424,10425,10429,10430,10431,10434,10436,10438,10440,10443,10446,10450,10451,10452,10453,10454,10456,10459,10460,10461,10464,10465,10469,10472,10475,10476,10477,10478,10483,10485,10487,10488,10489,10491,10492,10493,10496,10500,10506,10507,10510,10511,10512,10513,10515,10516,10519,10522,10523,10525,10526,10527,10528,10533,10534,10535,10536,10541,10542,10543,10547,10548,10549,10550,10551,10554,10555,10559,10560,10562,10564,10565,10566,10567,10569,10570,10571,10572,10580,10581,10583,10584,10586,10588,10590,10592,10593,10595,10596,10597,10602,10603,10604,10605,10606,10610,10613,10616,10617,10622,10623,10627,10630,10631,10632,10633,10635,10637,10640,10642,10643,10644,10646,10648,10650,10651,10652,10653,10654,10655,10656,10658,10659,10661,10663,10664,10667,10668,10672,10677,10680,10681,10686,10687,10688,10689,10690,10693,10697,10698,10700,10701,10707,10712,10714,10717,10719,10721,10727,10730,10731,10735,10740,10741,10743,10744,10750,10751,10755,10756,10760,10761,10764,10765,10769,10770,10773,10774,10776,10777,10781,10784,10786,10787,10788,10790,10791,10794,10795,10796,10799,10800,10801,10802,10803,10804,10806,10808,10812,10813,10814,10816,10817,10823,10832,10833,10834,10835,10837,10838,10839,10840,10843,10845,10847,10849,10850,10851,10854,10855,10857,10859,10863,10865,10866,10868,10871,10872,10875,10877,10878,10880,10882,10884,10891,10892,10894,10899,10900,10902,10905,10908,10912,10913,10918,10923,10924,10925,10930,10931,10932,10935,10936,10938,10939,10941,10944,10951,10952,10953,10954,10955,10959,10960,10961,10963,10966,10970,10978,10980,10983,10985,10988,10990,10991,10993,10994,10997,10999,11000,11002,11006,11008,11009,11011,11012,11014,11020,11021,11025,11027,11030,11033,11034,11038,11041,11046,11047,11049,11050,11051,11052,11053,11062,11063,11064,11065,11068,11070,11071,11074,11075,11076,11077)

Finally, after a long winded setup of the scenario I’ve gotten back to the decision point in my last post where I decided I needed to use temp tables to get passed this issue.

Here is the code example for the same 3 basic steps only this time using XmlToCursor:

SqlData sqlData = new SqlData();

VfpData vfpData = new VfpData();

// Step 1:  get the OrderDetails that have a discount

DataTable orderDetails = sqlData.CreateDataTable("SELECT * FROM [Order Details] WHERE discount > 0");

// Step 2:  get a distinct list of order ids

int[] orderIds = orderDetails.AsEnumerable()

                             .Select(row => row.Field<int>("OrderId"))

                             .Distinct()

                             .ToArray();

// Step 3:

//          build xml

StringBuilder sb = new StringBuilder();

sb.Append("<VFPData>");

foreach (int orderId in orderIds) {

    sb.Append("<curTempIdList Id='");

    sb.Append(orderId);

    sb.Append("' />");

sb.Append("</VFPData>");

string xml = sb.ToString();

DataTable orderCustomerInfo;

using (OleDbCommand command = vfpData.CreateCommand()) {

    //      build select statement that looks for all orders specified in the orderIds array

    //      using XmlToCursor

    command.CommandText = @"

        select o.OrderId, c.CustomerId, c.CompanyName

            from Orders o

            inner join Customers c on upper(allt(o.CustomerId)) == upper(allt(c.CustomerId))

            where o.OrderId in (select Id from (iif(XmlToCursor(?, 'curTempIdList') > 0, 'curTempIdList', '')))";

    command.Parameters.Add("Xml", xml);

    //          execute select statement... executes properly without a VFP error

    orderCustomerInfo = vfpData.CreateDataTable(command);

Here is the selected statement:

select o.OrderId, c.CustomerId, c.CompanyName

    from Orders o

    inner join Customers c on upper(allt(o.CustomerId)) == upper(allt(c.CustomerId))

    where o.OrderId in (select Id from (iif(XmlToCursor(?, 'curTempIdList') > 0, 'curTempIdList', '')))

There is one main consideration that you would want to keep in mind when using XmlToCursor as I have in this example. The generated xml didn’t include any schema information which resulted in VFP making an assumption as to the expected data type. VFP’s assumption worked out for this example. But if the OrderId column had been a string this query would have failed due to a data type mismatch. To correct this issue, the xml would have to include a schema that explicitly identified the data type.

Here is an example querying the Customers table which has the CustomerId column define as a string. In this example the XmlToCursor function creates a cursor with the Id column being an int instead of a string which results in an “Operator/operant type mismatch” exception:

VfpData vfpData = new VfpData();

// notice that this string array contains only numbers

string[] customerIds = { "1", "2" };

StringBuilder sb = new StringBuilder();

sb.Append("<VFPData>");

foreach (string customerId in customerIds) {

    sb.Append("<curTempIdList Id='");

    sb.Append(customerId);

    sb.Append("' />");

sb.Append("</VFPData>");

string xml = sb.ToString();

DataTable customers;

using (OleDbCommand command = vfpData.CreateCommand()) {

    command.CommandText = @"

        select *

            from Customers

            where CustomerId in (select Id from (iif(XmlToCursor(?, 'curTempIdList') > 0, 'curTempIdList', '')))";

    command.Parameters.Add("Xml", xml);

    // causes "Operator/operand type mismatch." exception

    customers = vfpData.CreateDataTable(command);

To help deal with this issue, I wrote a class that would convert an array to XmlToCursor formatted xml which would include the schema when necessary.

class XmlToCursor {

    private readonly XNamespace xsd = "http://www.w3.org/2001/XMLSchema";

    private readonly XNamespace msdata = "urn:schemas-microsoft-com:xml-msdata";

    private const string ROOTNAME = "VFPData";

    public readonly string CursorName = "curTempIdList";

    public readonly string ColumnName = "Id";

    private Type arrayType;

    public string GetXml(Array array) {

        this.arrayType = this.GetArrayType(array);

        XDocument xdoc = new XDocument();

        XElement root = new XElement(ROOTNAME);

        xdoc.Add(root);

        XElement schema = this.GetSchemaElement();

        if (schema != null) {

            root.Add(schema);

        foreach (var item in array) {

            if (item == null) {

                root.Add(new XElement(CursorName));

            else {

                root.Add(new XElement(CursorName,

                            new XAttribute(ColumnName, item)));

        return xdoc.ToString();

    private Type GetArrayType(Array array) {

        Type type = array.GetType();

        // Try to get the actual array type incase it requires schema information in the xml.

        if (type.FullName == "System.Object[]" && array.Length > 0) {

            type = Type.GetType(array.GetValue(0).GetType().FullName + "[]");

        return type;

    private XElement GetSchemaElement() {

        XElement attribute = this.GetAttributeElement();

        if (attribute == null) {

            return null;

        return new XElement(xsd + "schema",

                new XAttribute("id", ROOTNAME),

                new XAttribute(XNamespace.Xmlns + "xsd", xsd.NamespaceName),

                new XAttribute(XNamespace.Xmlns + "msdata", msdata.NamespaceName),

                new XElement(xsd + "element",

                    new XAttribute("name", ROOTNAME),

                    new XAttribute(msdata + "IsDataSet", true),

                    new XElement(xsd + "complexType",

                        new XElement(xsd + "choice",

                            new XAttribute("maxOccurs", "unbounded"),

                            new XElement(xsd + "element",

                                new XAttribute("name", CursorName),

                                new XAttribute("minOccurs", 0),

                                new XAttribute("maxOccurs", "unbounded"),

                                new XElement(xsd + "complexType",

                                    attribute,

                                    new XElement(xsd + "anyAttribute",

                                        new XAttribute("namespace", "http://www.w3.org/XML/1998/namespace"),

                                        new XAttribute("processContext", "lax"))))))));

    private XElement GetAttributeElement() {

        XElement complexType = null;

        switch (this.arrayType.FullName) {

            case "System.String[]":

                complexType = this.GetVarCharAttributeElement();

                break;

            default:

                return null;

        return complexType;

    private XElement GetVarCharAttributeElement() {

        const int MAX_VARCHAR_LENGTH = 254;

        return new XElement(xsd + "attribute",

                new XAttribute("name", this.ColumnName),

                new XAttribute("use", "required"),

                new XElement(xsd + "simpleType",

                    new XElement(xsd + "restriction",

                    new XAttribute("base", "xsd:string"),

                    new XElement(xsd + "maxLength",

                        new XAttribute("value", MAX_VARCHAR_LENGTH)))));

Here is an example of the Customer query rewritten to use the XmlToCursor class:

VfpData vfpData = new VfpData();

// notice that this string array contains only numbers

string[] customerIds = { "1", "2" };

XmlToCursor xmlToCursor = new XmlToCursor();

string xml = xmlToCursor.GetXml(customerIds);

DataTable customers;

using (OleDbCommand command = vfpData.CreateCommand()) {

    command.CommandText = @"

        select *

            from Customers

            where CustomerId in (select Id from (iif(XmlToCursor(?, 'curTempIdList') > 0, 'curTempIdList', '')))";

    command.Parameters.Add("Xml", xml);

    // the query succeeds

    customers = vfpData.CreateDataTable(command);

At this point, I’ve explained a basic scenario that has required the XmlToCursor function. I’ve also shown how the function can be used in ADO.net. So how does this XmlToCursor stuff affect LINQ to VFP…?

Before becoming aware of the potential usage of XmlToCursor I had included some temp table helper classes in LINQ to VFP. These classes got the job done but resulted in some very messy code because it required branching the code to handle queries that contained too many array items (see code below). In addition to less than desirable code, the LINQ to VFP developer had to evaluate each query to determine if it had potential of causing the “statement too long” error.

NorthwindDataContext context = new NorthwindDataContext(connectionString);

int[] ids = context.List<Order>().OrderBy(o => o.OrderID).Take(160).Select(o => o.OrderID).ToArray();

List<Order> list;

if (ids.Length > 100) {

    using (TempTable<TempTableIntId> tempTable = context.Provider.CreateTempTable(ids)) {

        list = (from o in context.List<Order>()

                join t in tempTable.EntityTable on o.OrderID equals t.Id

                select o).ToList();

else {

    list = (from o in context.List<Order>()

            where ids.Contains(o.OrderID)

            select o).ToList();

return list;

What I think would have been much better is if LINQ to VFP evaluated the query to determine the array length so that it could modify the select statement as needed. In retrospect, I probably could have made the select statement rewrite happen with the temp tables but it just didn’t seem like the right thing to do at the time. Nevertheless, this essentially describes the process that was included when LINQ to VFP was changed to use XmlToCursor. In addition to this new process I have added a new setting that is used to opt-in to the whole XmlToCursor rewrite process.

Here is the previous code example rewritten so that the sql select statement includes the XmlToCursor function.

// can be set once in the application startup

VfpQueryProvider.XmlToCursorMinimumItemCount = 100;

NorthwindDataContext context = new NorthwindDataContext(connectionString);

int[] ids = context.List<Order>().OrderBy(o => o.OrderID).Take(160).Select(o => o.OrderID).ToArray();

List<Order> list = (from o in context.List<Order>()

            where ids.Contains(o.OrderID)

            select o).ToList();

return list;

Comments (1)

February 25, 2010

WCF Data Service mangled my select statement…

Filed under: Development — Tags: LINQ to VFP, WCF Data Service — Tom Brothers @ 1:49 am

I’ve been working on a project that uses WCF Data Services for data access on a DMZ server. The application was designed to work directly with LINQ to VFP on the intranet but use WCF Data Services on the extranet/DMZ server. During development I noticed that in some cases the queries over WCF Data Services didn’t exactly match the direct LINQ to VFP queries. What I found was the Expression Tree for the queries that required an upper(alltrim()) (to utilize indexes) had included some extra null checking Expressions.

Here is an example of the issue.

Example Query:

var list = (from d in context.List<Customer>()

            where d.CustomerId.Trim().ToUpper() == "ALFKI"

            select d).ToList();

When executing this query directly with LINQ to VFP you would get the following Expression:

This would result in the following query:

__Param__0__ = [ALFKI]

SELECT t0.City, t0.CompanyName, t0.ContactName, t0.Country, t0.CustomerId, t0.Phone ;

FROM Customers AS t0 ;

WHERE (UPPER(ALLTRIM(t0.CustomerId)) = __Param__0__)

The same query would result in the following when executed through WCF Data Services:

__Param__0__ = [ALFKI]

SELECT t0.City, t0.CompanyName, t0.ContactName, t0.Country, t0.CustomerId, t0.Phone ;

FROM Customers AS t0 ;

WHERE (ICASE((ICASE((t0.CustomerId IS NULL), NULL, ALLTRIM(t0.CustomerId)) IS NULL), NULL, UPPER(ICASE((t0.CustomerId IS NULL), NULL, ALLTRIM(t0.CustomerId)))) = __Param__0__)

The extra null checking makes sense given the fact that WCF Data Services can be used with Data Sources other than ORMs. However, this really made a mess of the Select Statement created by LINQ to VFP. More importantly the query was unable to utilize the index.

Correcting this issue wasn’t that difficult once it had been identified. I added the following Visitor class to remove the unnecessary null checking Expressions.

using System.Linq.Expressions;

using IQToolkit.Data.Common;

namespace LinqToVfp {

    public class ConditionalImmediateIfNullRemover : DbExpressionVisitor {

        public static Expression Remove(Expression expression) {

            return new ConditionalImmediateIfNullRemover().Visit(expression);

        protected override Expression VisitConditional(ConditionalExpression c) {

            if (c.Test.NodeType == ExpressionType.Equal && c.IfTrue.NodeType == ExpressionType.Constant && ((ConstantExpression)c.IfTrue).Value == null) {

                MethodCallExpression methodCallExpression = c.IfFalse as MethodCallExpression;

                if (methodCallExpression != null) {

                    if (methodCallExpression.Object.NodeType == ExpressionType.Conditional) {

                        return this.Visit(base.VisitConditional(c));

                    BinaryExpression be = (BinaryExpression)c.Test;

                    ColumnExpression columnExpression = be.Left as ColumnExpression;

                    if (columnExpression == null) {

                        return c.IfFalse;

                    else {

                        return this.Visit(c.IfFalse);

            return base.VisitConditional(c);

* This change was made in v1.0.17.1

January 23, 2010

VFP Error 1812 – Statement too long (.Net/LINQ to VFP perspective)

Filed under: Development — Tags: LINQ to VFP, VFP — Tom Brothers @ 1:50 am

In the last 10 years or so that I’ve been working with VFP I’ve only encountered the “Statement too long” error a handful of times. Within VFP, I found that this error could be handled in two ways. One way to handle this error is to simply rewrite the SQL Statement. Another way, which I only recently discovered, was to use a sys() command.

Here is a quick VFP example using the Northwind database. I have a function that accepts a parameter which is a comma delimited list of Order Ids. The parameter is passed into a simple SQL Select statement.

FUNCTION CreateOrdersCursorByOrderIds(tcOrderIds)

LOCAL lcSql

lcSql = " SELECT * " + ;

            " FROM Orders " + ;

            " WHERE OrderId in (" + tcOrderIds + ")" + ;

            " into cursor curOrders "

&lcSql

This method works fine when there are a limited amount of Order Ids but when there are a couple hundred Order Ids the statement fails. This statement can easily be rewritten using a join statement to prevent the “Statement too long” error.

FUNCTION CreateOrdersCursorByOrderIds(tcOrderIds)

LOCAL lnIndex, laOrders[1]

CREATE CURSOR curOrderIds(OrderId i)

FOR lnIndex = 1 TO ALINES(laOrders, tcOrderIds, 1, ",")

    INSERT INTO curOrderIds value(VAL(laOrders[lnIndex]))

NEXT

SELECT o.* ;

    FROM Orders o ;

    INNER JOIN curOrderIds i ON o.OrderId = i.OrderId ;

    into cursor curOrders

USE IN curOrderIds

An alternative option to re-writing the statement is to use the SYS(3055) command.

FUNCTION CreateOrdersCursorByOrderIds(tcOrderIds)

LOCAL lcSql

lcSql = " SELECT * " + ;

            " FROM Orders " + ;

            " WHERE OrderId in (" + tcOrderIds + ")" + ;

            " into cursor curOrders "

SYS(3055, 2040)

&lcSql

Ok so I have a simple work around for this error in VFP. Now what about .Net…?

Here is the same example re-written in C#.

using (OleDbConnection conn = new OleDbConnection(connectionString)) {

    conn.Open();

    string[] orderIds = new string[] {

        "10248", "10249", "10250", "10251", "10252", "10253", "10254", "10255", "10256", "10257", "10258", "10259", "10260", "10261", "10262",

            "10263", "10264", "10265", "10266", "10267", "10268", "10269", "10270", "10271", "10272", "10273", "10274", "10275", "10276", "10277",

            "10278", "10279", "10280", "10281", "10282", "10283", "10284", "10285", "10286", "10287", "10288", "10289", "10290", "10291", "10292",

            "10293", "10294", "10295", "10296", "10297", "10298", "10299", "10300", "10301", "10302", "10303", "10304", "10305", "10306", "10307",

            "10308", "10309", "10310", "10311", "10312", "10313", "10314", "10315", "10316", "10317", "10318", "10319", "10320", "10321", "10322",

            "10323", "10324", "10325", "10326", "10327", "10328", "10329", "10330", "10331", "10332", "10333", "10334", "10335", "10336", "10337",

            "10338", "10339", "10340", "10341", "10342", "10343", "10344", "10345", "10346", "10347", "10348", "10349", "10350", "10351", "10352",

            "10353", "10354", "10355", "10356", "10357", "10358", "10359", "10360", "10361", "10362", "10363", "10364", "10365", "10366", "10367",

            "10368", "10369", "10370", "10371", "10372", "10373", "10374", "10375", "10376", "10377", "10378", "10379", "10380", "10381", "10382",

            "10383", "10384", "10385", "10386", "10387", "10388", "10389", "10390", "10391", "10392", "10393", "10394", "10395", "10396", "10397",

            "10398", "10399", "10400", "10401", "10402", "10403", "10404", "10405", "10406", "10407"

};

    DataTable dt = new DataTable();

    string sql = @"SELECT * FROM Orders WHERE OrderId in (" + string.Join(",", orderIds) + ")";

    OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);

    da.Fill(dt);

    da.Dispose();

When I run this code I get the same “Statement too long” error.

Out of the two ways that I know of to correct this issue… Using the SYS(3055) command would be the easiest to implement. So I modified the code to include the SYS(3055) command.

using (OleDbConnection conn = new OleDbConnection(connectionString)) {

    conn.Open();

    string[] orderIds = new string[] {

        "10248", "10249", "10250", "10251", "10252", "10253", "10254", "10255", "10256", "10257", "10258", "10259", "10260", "10261", "10262",

            "10263", "10264", "10265", "10266", "10267", "10268", "10269", "10270", "10271", "10272", "10273", "10274", "10275", "10276", "10277",

            "10278", "10279", "10280", "10281", "10282", "10283", "10284", "10285", "10286", "10287", "10288", "10289", "10290", "10291", "10292",

            "10293", "10294", "10295", "10296", "10297", "10298", "10299", "10300", "10301", "10302", "10303", "10304", "10305", "10306", "10307",

            "10308", "10309", "10310", "10311", "10312", "10313", "10314", "10315", "10316", "10317", "10318", "10319", "10320", "10321", "10322",

            "10323", "10324", "10325", "10326", "10327", "10328", "10329", "10330", "10331", "10332", "10333", "10334", "10335", "10336", "10337",

            "10338", "10339", "10340", "10341", "10342", "10343", "10344", "10345", "10346", "10347", "10348", "10349", "10350", "10351", "10352",

            "10353", "10354", "10355", "10356", "10357", "10358", "10359", "10360", "10361", "10362", "10363", "10364", "10365", "10366", "10367",

            "10368", "10369", "10370", "10371", "10372", "10373", "10374", "10375", "10376", "10377", "10378", "10379", "10380", "10381", "10382",

            "10383", "10384", "10385", "10386", "10387", "10388", "10389", "10390", "10391", "10392", "10393", "10394", "10395", "10396", "10397",

            "10398", "10399", "10400", "10401", "10402", "10403", "10404", "10405", "10406", "10407"

};

    DataTable dt = new DataTable();

    string sql = @"SELECT * FROM Orders WHERE OrderId in (" + string.Join(",", orderIds) + ")";

    using (OleDbCommand cmd = conn.CreateCommand()) {

        cmd.CommandText = "SYS(3055, 2040)";

        cmd.ExecuteNonQuery();

        cmd.CommandText = "SYS(3055)";

        var sys3055 = cmd.ExecuteScalar();

        System.Diagnostics.Debug.WriteLine("SYS(3055) = " + sys3055.ToString());

    OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);

    da.Fill(dt);

    da.Dispose();

No luck… the simple solution didn’t work.

Adding the SYS(3055) command just changed the error message to “Insufficient stack space.” Researching this error led me back to SYS(3055) which clearly isn’t a fix for .Net.

I still have two options at this point. I can pull all the Orders into a DataTable and then filter the results in .Net. Using LINQ to DataSets with this option would have a nice feel to it… it would seem similar to processing the data in VFP but it would come at a heavy performance cost (assuming the data was a larger data set than the Northwind.dbc). Needless to say I’d like to avoid this option if possible so I’ll move on to my last idea. I need to rewrite the query to use an inner join as I did in the VFP code. To pull this off I will need to create a temporary table to hold the Order Ids.

using (OleDbConnection conn = new OleDbConnection(connectionString)) {

    conn.Open();

    string[] orderIds = new string[] {

        "10248", "10249", "10250", "10251", "10252", "10253", "10254", "10255", "10256", "10257", "10258", "10259", "10260", "10261", "10262",

        "10263", "10264", "10265", "10266", "10267", "10268", "10269", "10270", "10271", "10272", "10273", "10274", "10275", "10276", "10277",

        "10278", "10279", "10280", "10281", "10282", "10283", "10284", "10285", "10286", "10287", "10288", "10289", "10290", "10291", "10292",

        "10293", "10294", "10295", "10296", "10297", "10298", "10299", "10300", "10301", "10302", "10303", "10304", "10305", "10306", "10307",

        "10308", "10309", "10310", "10311", "10312", "10313", "10314", "10315", "10316", "10317", "10318", "10319", "10320", "10321", "10322",

        "10323", "10324", "10325", "10326", "10327", "10328", "10329", "10330", "10331", "10332", "10333", "10334", "10335", "10336", "10337",

        "10338", "10339", "10340", "10341", "10342", "10343", "10344", "10345", "10346", "10347", "10348", "10349", "10350", "10351", "10352",

        "10353", "10354", "10355", "10356", "10357", "10358", "10359", "10360", "10361", "10362", "10363", "10364", "10365", "10366", "10367",

        "10368", "10369", "10370", "10371", "10372", "10373", "10374", "10375", "10376", "10377", "10378", "10379", "10380", "10381", "10382",

        "10383", "10384", "10385", "10386", "10387", "10388", "10389", "10390", "10391", "10392", "10393", "10394", "10395", "10396", "10397",

        "10398", "10399", "10400", "10401", "10402", "10403", "10404", "10405", "10406", "10407"

};

    DataTable dt = new DataTable();

    string sql = @"

select *

    from Orders o

    inner join {0} t on o.OrderId = t.OrderId

";

    string tempTableFullPath = Path.ChangeExtension(Path.GetTempFileName(), "dbf");

    using (OleDbCommand cmd = conn.CreateCommand()) {

        cmd.CommandText = string.Format("create table "{0}" free (OrderId I)", tempTableFullPath);

        cmd.ExecuteNonQuery();

        for (int index = 0, total = orderIds.Length; index < total; index++) {

            cmd.CommandText = string.Format("insert into "{0}" values({1})", tempTableFullPath, orderIds[index]);

            cmd.ExecuteNonQuery();

    OleDbDataAdapter da = new OleDbDataAdapter(string.Format(sql, tempTableFullPath), conn);

    da.Fill(dt);

    da.Dispose();

This worked perfectly.

Great, I got a proof of concept to work using standard ADO.Net. Now I’d like to get this to work with LINQ to VFP.

Here is the example rewritten with LINQ to VFP.

NorthwindDataContext context = new NorthwindDataContext(connectionString);

int[] ids = context.List<Order>().OrderBy(o => o.OrderID).Take(160).Select(o => o.OrderID).ToArray();

var list = context.List<Order>().Where(o => ids.Contains(o.OrderID)).ToList();

Rewriting the LINQ to VFP statement isn’t as simple as it was in VFP or using standard ADO.Net. The temporary table needs an Entity class and this Entity class will need to be mapped to the temporary table. Plus I will need to be able to pass the full temporary file path as the Table Id. After a few attempts I found that I could not pull this off without modifying the LINQ to VFP code. So I modified the VfpQueryProvider to include a new method that would create and map the temporary table. Here is an example of the rewritten LINQ to VFP statement using the temporary table.

NorthwindDataContext context = new NorthwindDataContext(connectionString);

int[] ids = context.List<Order>().OrderBy(o => o.OrderID).Take(160).Select(o => o.OrderID).ToArray();

using (TempTable<TempTableIntId> tempTable = context.Provider.CreateTempTable(ids)) {

    var list = (from o in context.List<Order>()

                join t in tempTable.EntityTable on o.OrderID equals t.Id

                select o).ToList();

* One thing to note about the TempTable class is that it implements IDisposable. The dispose method will delete the temporary file so keep this in mind if you are using the TempTable when passing around IQueryable. In this case you need to make sure the Dispose method isn’t called before executing the query.

Creating a temporary table is going to add a little extra processing time so this should only be used if needed. Here is an example that of how I’m using this in my production code.

NorthwindDataContext context = new NorthwindDataContext(connectionString);

int[] ids = context.List<Order>().OrderBy(o => o.OrderID).Take(160).Select(o => o.OrderID).ToArray();

List<Order> list;

if (ids.Length > 100) {

    using (TempTable<TempTableIntId> tempTable = context.Provider.CreateTempTable(ids)) {

        list = (from o in context.List<Order>()

                join t in tempTable.EntityTable on o.OrderID equals t.Id

                select o).ToList();

else {

    list = (from o in context.List<Order>()

            where ids.Contains(o.OrderID)

            select o).ToList();

return list;

Comments (1)

« Newer Posts — Older Posts »

May 6, 2012

Introduction

Getting Started

Alter the Model

February 5, 2012

Introduction

Getting Started

Query Some Data

Alter the Model

Update (failure)

One Step Forward Two Steps Back…

Candidate Key=Primary Key

November 14, 2011

Changes:

November 7, 2011

Introduction

Create the Application

Add References

Add Code

Change App.Config

Run & Review

October 23, 2011

Introduction

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

See In Action

* Notes:

Install Steps

How to add to a Project

Deployment

Known Issues

June 7, 2011

April 23, 2011

Install the driver (must be x86 LINQPad build):

Connect to your database container (or free tables directory).

February 20, 2011

February 25, 2010

January 23, 2010

Open Source Projects

Recent Posts

Archives

About