Hi Tom,

I'm trying to use your post and I have a System.Data.EntityCommandExecutionException error on the ToList() line of your sample.

The inner exception is {System.Data.OleDb.OleDbErrorCollection} : {"Syntax error."}

Here is the StackTrace of the main exception

at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)

at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)

at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()

at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)

at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

at ConsoleApplication2.Program.Main(String[] args)

in C:\Projects\Sources\ConsoleApplication2\ConsoleApplication2\Program.cs:line 14

at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)

at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)

at  Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()

at System.Threading.ThreadHelper.ThreadStart_Context(Object state)

at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)

at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

at System.Threading.ThreadHelper.ThreadStart()

And here the inner exception StackTrace

at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)

at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)

at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)

at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)

at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)

at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)

at VfpEntityFrameworkProvider.VfpOleDb.VfpCommand.ExecuteDbDataReader(CommandBehavior behavior)

at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)

at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

I used the same code as you with my zapped free tables I sent you last time.

I found the issue and posted a new build.

The problem was related to how the provider was aliasing columns.  More specifically, how it was aliasing a column name that was a reserved word.

Here is an example of the problem query:

SELECT Dossiers.Group Group FROM Dossiers

There are a couple issues with this query. The first issue is that when the query was built it did not include the “AS” keyword between the fully qualified column name and the alias name. This was intentionally omitted to reduce the query size to help avoid the infamous “string too long” error that VFP throws when the query text is too long.  Taking your data structure into account I now see that this omission was clearly a mistake  So I changed the provider to include the “AS” keyword… resulting in the following query.

SELECT Dossiers.Group AS Group FROM Dossiers

But there is still a problem with this query. There really isn’t a need for the column alias at all. So another change that I made in this build was to include a column alias only if it is needed.

Example of the generated query with the latest build (v0.6.4).

SELECT Dossiers.Group FROM Dossiers

Thanks to you for bringing this issue to my attention.

You're welcome.

I tried the 0.6.4 version.

I still have syntax error.

I downloaded the sources 88207 with subversion.

In debug mode I have the syntax error in

VfpCommand.cs

protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior)

Here is the query :

SELECT 

Extent1.Reserve, 

[...]

Extent1.Ententep

FROM (

SELECT

Dossiers.Reserve Reserve,

[...]

Dossiers.Ententep Ententep

FROM Dossiers Dossiers) Extent1

As you see it seems to be the same problem.

Sorry, I failed to mention that you will need to recreate your model with the new build.  That inner query that still has the problem is coming from the edmx file which was generated with the previous build.  If you view the edmx file as xml you will see what I mean.

Ok that's it thanks