Updating Free Tables via EF

Apr 30, 2013 at 10:03 PM
Is there a way in EF to manually define the primary key to a Fox25 dbf/cdx?

Up until now, I have been using ADO\ADO.NET and hardcoding all my calls and it's getting a bit tiresome.

My free tables have index tags that are "Unique" but without a DBC I realize VFPOLEDB has no way of determining and I'm relying on the underlying provider and rushmore.

So is there still a way of telling the EDMX how to do it's SQL Update\Inserts and Deletes? I've marked my field as an "Entity Key" - but still have warning 6002 in EDMX and not sure what am I missing?

Thanks again,
Mike
May 1, 2013 at 6:22 AM
I'm new to this but will try to answer:
First: the unique key is realy bad because it only stops dupplicate keys in the index-table. If you have 2 records with the key 1 you will end with 2 records in your dbf but only one entry in your index. I think you know this?!
Second: The EDMX-Model for your table is "defined" as readonly because it does not have a "primary key". This is done by adding the "DefiningQuery" part to the edmx file. To make your table writeable delete "DefiningQuery" entry and modify the "Key"-section.
Coordinator
May 1, 2013 at 2:12 PM
A Candidate index is used as the primary key for Free Tables when generating the Entity Framework model. If Fox25 supports this type of index and you don't mind changing your table structure then this is an option for you. Another option is to manually configuring the EDMX as mlandwehr suggested. I personally wouldn’t go this route because you end up having to spend time learning the EDMX xml so that you can re-do some of the work that the EF design wizard was supposed to do for you – which in my mind makes using the wizard, in this case, a waste of time.

If changing your tables to use a Candidate index isn’t an option for you then what I would suggest is using the Entity Framework Power Tools Visual Studio Extension for your model generation. This tool will create your model as “code first” and will not include an EDMX. By default the generated Entities will have a primary key that includes all fields in the table. You can change the primary key by changing one line of code (per Entity) which I believe would be a lot less work than fixing up the EDMX after it has identified your Tables/Entities as “read only.” Here is an example (slightly outdated) showing how to use the Entity Framework Power Tools with Free Tables.
May 1, 2013 at 4:59 PM
Thanks Tom and mlandwehr,

I hate to admit it, but we still have fox25 code running in a dos box and I hate to touch the CDX's again. (I Just finished the first wave of mods to get rid of the NDX's) The VFP Code is all backend server printing\emailing... and some ADO on the web side, but those don't doesn't worry me.

I'll take a look at the Power Tools and Code first, if that gets too ugly then I guess I'll Add candidates

Thanks both again for the help and Tom, Thanks again for your entity classes really appreciate your work on keeping fox alive.

Regards.
Mike
May 2, 2013 at 6:04 PM
To further my response:

I have played around with the "Power tools toolkit" and it does a nice job of generating the necessary POCO objects from the table fields for use with EF as code first. Unfortunately it will not work with the RIA Domain Services so I would need to hand code that layer or wait for an update to RIA which is already questionable.

I'm not 100% sure I'm sticking with RIA, Just trying to wrap my data in a clear layer to use for other future Form and web applications.
So.... I'm adding candidate indexes and hoping that the DOS and VFP worlds play nice, Unfortunalely I cannot add a DBC for that will make the older DOS code fail (ie Not a database File)

Besides not supporting transactions and relations, what else am i giving up without the DBC in a ADO\EF world?

Also

I had to add the ;OLE DB Services=-4 to my connection string or it would throw an error. I also added my Mode=ReadWrite;ENGINEBEHAVIOR=80;CODEPAGE=1252; which is my typical to ADO statement for the unicode index "Feature" (err Bug) that they introduced in VFP9. Is this typically what you\others are doing?


Regards,
Mike
Coordinator
May 3, 2013 at 2:54 PM
The only difference that I'm aware of between Free Tables and DBC, in regards to the EF provider, is that you don't get Transactions with the Free Tables. Other than that I think everything else works the same... including being able to define relations in the model.


Typically my connection string just contains the data source. The only time I had to add anything was when I was experimenting with LightSwitch using RIA Services. In this case I had to add the OLE DB Services setting for the same reason you pointed out. For my production code I decided not to use RIA Services in favor of WCF Data Services. I did this so that I could write a simple repository layer that could easily switch between the DbContext and DataServiceContext.
May 3, 2013 at 3:45 PM
Edited May 3, 2013 at 3:49 PM
I'm in the same debate now between RIA and WCF, I was under the assumption that there was better control in RIA but I have yet to understand the benefit (my next learning curve). If the rumors are true about RIA being "end of life" then I guess there isn't much point in investing the time (unless it becomes open sourced)

If your connection string is standard, then how is your performance on larger tables? From what i've found in my current ADO applications, unless I use cpzero.prg on all my DBF's to set the codepage byte manually to the same codepage in the connection string, Rushmore wouldn't enable and seeks were slow. Setting the Engine Behavior to 80 would make rushmore indexes work again by disabling the newer unicode indexes in 90. Considering all this is still sitting above VFPOLEDB's VFP9sp2 driver it must still be the case regardless of EF?

(For anybody reading this who is unfamiliar with Rushmore - it's Foxpro's ability to use the various CDX index tags dynamically to find the data faster without having to do a "Set order to" in code. Considering there is no way easy way to set underlying orders in a SQL (not result set sorting) rushmore is more critical for performance for OleDb) - At least from what I've learned.... Tom might disprove me....

Regards,
Mike Pisano
Coordinator
May 5, 2013 at 2:19 PM
In general, my query performance seem so be the same when running a command in VFP compared to using the VfpOleDb provider. I test this by turning on command tracing which will log the command text and the duration and then I copy that command into VFP to see how it performs. The only performance issues that I’ve noticed is when someone would write a query that didn’t utilize the table indexes. This would typically happen when the index contained expressions such as an index like “Upper(allt(FieldName)).” However, it is certainly worth me experimenting with the connection string settings that you’ve identified to see if I can get better performance on my queries.

It would have been nice if the VfpOleDb provider supported the Sys(3054) command so that I could include that information along with the existing information that I log when executing a command.



Add the following source to turn on tracing for VFP EF Provider, LINQ to VFP, and VfpClient:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <system.diagnostics>
    <sources>
      <source name="VfpClient" switchValue="Information" >
        <!--<listeners>
        <add name="VfpClientTextWriterTraceListener"
             type="System.Diagnostics.TextWriterTraceListener"
             initializeData="c:\VfpClient.txt" />
      </listeners>-->
      </source>
    </sources>
  </system.diagnostics>
</configuration>
May 6, 2013 at 4:21 PM
Yep - We are kind of blind to Rushmore behind VfpOleDb.

I remember upgrading from VFP 8 to 9 on our web server and the entire site came to a screeching halt. I had to put the old DLL's back in place until I figured out about ENGINEBEHAVIOR and Codepages.

I'll add the listener in the XML - nice to be able to watch the queries - Thanks

Regards,
Mike