.NET - using Entity Framework with Visual FoxPro - error "Compiled code for this line is too long."

Question

I am trying to use Entity Framework 6 (EF) with a Visual FoxPro database (using package VFPEntityFrameworkProvider2 from NuGet) I am able to make the connection just fine. I am able to load data from most tables.

The problem occurs when I try to retrieve a DbSet of a certain type which has about 240 fields. The exception that is thrown has the message:

Compiled code for this line is too long.

Best I understand, EF composes the SQL select statement to retrieve the data, and this statement is too long. As I use the debugger to examine the local variables, I see an SQL statement, whose length is close to 16000 chars, whereas maximum allowed command length for VFP is 8,192 bytes

I am not quite sure how to solve this, as I am fairly new to EF. I imagine I might be able to either edit some generated code, or perhaps write some custom handler. Perhaps I could retrieve the fields in batches, and then compose the object.

Also, I believe it's possible to break down my entity into a type and sub-type; perhaps if I do so, only a portion of the fields will be retrieved at once.

Can anyone suggest in what manner I could resolve this limitation?

Added notes:

It is not an option to use a different database. I'm stuck with FoxPro, and this specific schema. However, I am free to change my approach to the problem. Of course, I can write the DB access code myself, but it would be very nice to be able to use EF.

The query sent to VFP actually looks something like this:

SELECT 
E1.Wn_Ref, 
E1.Wn_Surname, 
E1.Wn_Forenam, 
E1.Wn_Dirctr, 
...
E1.Wn_Chqno, 
 CAST( E1.Wn_Lelval AS n(20,2)) AS Wn_Lelval, 
E1.Wn_Dirstpd, 
...
E1.Wn_Leavdt, 
 CAST( E1.Wn_Grsprv AS n(20,2)) AS Wn_Grsprv, 
 CAST( E1.Wn_Taxprv AS n(20,2)) AS Wn_Taxprv, 
E1.Wn_Ovride, 
E1.Wn_Nichgpr, 
...
E1.Wn_Totabs, 
 CAST( E1.Wn_Tgrspay AS n(20,2)) AS Wn_Tgrspay, 
 CAST( E1.Wn_Tottax AS n(20,2)) AS Wn_Tottax, 
 CAST( E1.Wn_Totpens AS n(20,2)) AS Wn_Totpens, 
 CAST( E1.Wn_Totsspr AS n(20,2)) AS Wn_Totsspr, 
 CAST( E1.Wn_Totsmp AS n(20,2)) AS Wn_Totsmp, 
 CAST( E1.Wn_Totchrt AS n(20,2)) AS Wn_Totchrt, 
 CAST( E1.Wn_Totcmee AS n(20,2)) AS Wn_Totcmee, 
 CAST( E1.Wn_Totcmer AS n(20,2)) AS Wn_Totcmer, 
 CAST( E1.Wn_Tcmeeyr AS n(20,2)) AS Wn_Tcmeeyr, 
 CAST( E1.Wn_Tcmeryr AS n(20,2)) AS Wn_Tcmeryr, 
 CAST( E1.Wn_Totpenl AS n(20,2)) AS Wn_Totpenl, 
 CAST( E1.Wn_Totpay AS n(20,2)) AS Wn_Totpay, 
 CAST( E1.Wn_Tothol AS n(20,1)) AS Wn_Tothol, 
 CAST( E1.Wn_Roundbf AS n(20,2)) AS Wn_Roundbf, 
E1.Wl_Totsspd, 
E1.Wl_Totabs, 
 CAST( E1.Wl_Tgrspay AS n(20,2)) AS Wl_Tgrspay, 
 CAST( E1.Wl_Tottax AS n(20,2)) AS Wl_Tottax, 
 CAST( E1.Wl_Totpens AS n(20,2)) AS Wl_Totpens, 
 CAST( E1.Wl_Totsspr AS n(20,2)) AS Wl_Totsspr, 
 CAST( E1.Wl_Totsmp AS n(20,2)) AS Wl_Totsmp, 
 CAST( E1.Wl_Totchrt AS n(20,2)) AS Wl_Totchrt, 
 CAST( E1.Wl_Totcmee AS n(20,2)) AS Wl_Totcmee, 
 CAST( E1.Wl_Totcmer AS n(20,2)) AS Wl_Totcmer, 
 CAST( E1.Wl_Tcmeeyr AS n(20,2)) AS Wl_Tcmeeyr, 
 CAST( E1.Wl_Tcmeryr AS n(20,2)) AS Wl_Tcmeryr, 
 CAST( E1.Wl_Totpenl AS n(20,2)) AS Wl_Totpenl, 
 CAST( E1.Wl_Totpay AS n(20,2)) AS Wl_Totpay, 
 CAST( E1.Wl_Tothol AS n(20,1)) AS Wl_Tothol, 
 CAST( E1.Wl_Txb AS n(20,2)) AS Wl_Txb, 
 CAST( E1.Wl_Tax AS n(20,2)) AS Wl_Tax, 
 CAST( E1.Wl_Net AS n(20,2)) AS Wl_Net, 
 CAST( E1.Wl_Erni AS n(20,2)) AS Wl_Erni, 
 CAST( E1.Wl_Eeni AS n(20,2)) AS Wl_Eeni, 
 CAST( E1.Wl_Cnoni AS n(20,2)) AS Wl_Cnoni, 
 CAST( E1.Wl_Nien AS n(20,2)) AS Wl_Nien, 
 CAST( E1.Wl_Nieco AS n(20,2)) AS Wl_Nieco, 
 CAST( E1.Wl_Compee AS n(20,2)) AS Wl_Compee, 
 CAST( E1.Wl_Comper AS n(20,2)) AS Wl_Comper, 
 CAST( E1.Wl_Pen AS n(20,2)) AS Wl_Pen, 
 CAST( E1.Wl_Penbl AS n(20,2)) AS Wl_Penbl, 
 CAST( E1.Wl_Roundcf AS n(20,2)) AS Wl_Roundcf, 
 CAST( E1.Wn_Ssp1 AS n(20,2)) AS Wn_Ssp1, 
 CAST( E1.Wn_Ssp2 AS n(20,2)) AS Wn_Ssp2, 
 CAST( E1.Wn_Ssp3 AS n(20,2)) AS Wn_Ssp3, 
 CAST( E1.Wn_Ssp4 AS n(20,2)) AS Wn_Ssp4, 
 CAST( E1.Wn_Ssp5 AS n(20,2)) AS Wn_Ssp5, 
 CAST( E1.Wn_Ssp6 AS n(20,2)) AS Wn_Ssp6, 
 CAST( E1.Wn_Ssp7 AS n(20,2)) AS Wn_Ssp7, 
 CAST( E1.Wn_Ssp8 AS n(20,2)) AS Wn_Ssp8, 
E1.Wn_Ssprate, 
...
E1.Wn_Pwxretn
FROM (SELECT 
Wname.Wn_Ref, 
...
Wname.Wn_Chqno, 
 CAST( Wname.Wn_Lelval AS n(20,2)) AS Wn_Lelval, 
Wname.Wn_Dirstpd, 
Wname.Wn_Payfrq, 
Wname.Wn_Birth, 
Wname.Wn_Startdt, 
Wname.Wn_Leavdt, 
 CAST( Wname.Wn_Grsprv AS n(20,2)) AS Wn_Grsprv, 
 CAST( Wname.Wn_Taxprv AS n(20,2)) AS Wn_Taxprv, 
Wname.Wn_Ovride, 
...
Wname.Wn_Totabs, 
 CAST( Wname.Wn_Tgrspay AS n(20,2)) AS Wn_Tgrspay, 
 CAST( Wname.Wn_Tottax AS n(20,2)) AS Wn_Tottax, 
 CAST( Wname.Wn_Totpens AS n(20,2)) AS Wn_Totpens, 
 CAST( Wname.Wn_Totsspr AS n(20,2)) AS Wn_Totsspr, 
 CAST( Wname.Wn_Totsmp AS n(20,2)) AS Wn_Totsmp, 
 CAST( Wname.Wn_Totchrt AS n(20,2)) AS Wn_Totchrt, 
 CAST( Wname.Wn_Totcmee AS n(20,2)) AS Wn_Totcmee, 
 CAST( Wname.Wn_Totcmer AS n(20,2)) AS Wn_Totcmer, 
 CAST( Wname.Wn_Tcmeeyr AS n(20,2)) AS Wn_Tcmeeyr, 
 CAST( Wname.Wn_Tcmeryr AS n(20,2)) AS Wn_Tcmeryr, 
 CAST( Wname.Wn_Totpenl AS n(20,2)) AS Wn_Totpenl, 
 CAST( Wname.Wn_Totpay AS n(20,2)) AS Wn_Totpay, 
 CAST( Wname.Wn_Tothol AS n(20,1)) AS Wn_Tothol, 
 CAST( Wname.Wn_Roundbf AS n(20,2)) AS Wn_Roundbf, 
Wname.Wl_Totsspd, 
Wname.Wl_Totabs, 
 CAST( Wname.Wl_Tgrspay AS n(20,2)) AS Wl_Tgrspay, 
 CAST( Wname.Wl_Tottax AS n(20,2)) AS Wl_Tottax, 
 CAST( Wname.Wl_Totpens AS n(20,2)) AS Wl_Totpens, 
 CAST( Wname.Wl_Totsspr AS n(20,2)) AS Wl_Totsspr, 
 CAST( Wname.Wl_Totsmp AS n(20,2)) AS Wl_Totsmp, 
 CAST( Wname.Wl_Totchrt AS n(20,2)) AS Wl_Totchrt, 
 CAST( Wname.Wl_Totcmee AS n(20,2)) AS Wl_Totcmee, 
 CAST( Wname.Wl_Totcmer AS n(20,2)) AS Wl_Totcmer, 
 CAST( Wname.Wl_Tcmeeyr AS n(20,2)) AS Wl_Tcmeeyr, 
 CAST( Wname.Wl_Tcmeryr AS n(20,2)) AS Wl_Tcmeryr, 
 CAST( Wname.Wl_Totpenl AS n(20,2)) AS Wl_Totpenl, 
 CAST( Wname.Wl_Totpay AS n(20,2)) AS Wl_Totpay, 
 CAST( Wname.Wl_Tothol AS n(20,1)) AS Wl_Tothol, 
 CAST( Wname.Wl_Txb AS n(20,2)) AS Wl_Txb, 
 CAST( Wname.Wl_Tax AS n(20,2)) AS Wl_Tax, 
 CAST( Wname.Wl_Net AS n(20,2)) AS Wl_Net, 
 CAST( Wname.Wl_Erni AS n(20,2)) AS Wl_Erni, 
 CAST( Wname.Wl_Eeni AS n(20,2)) AS Wl_Eeni, 
 CAST( Wname.Wl_Cnoni AS n(20,2)) AS Wl_Cnoni, 
 CAST( Wname.Wl_Nien AS n(20,2)) AS Wl_Nien, 
 CAST( Wname.Wl_Nieco AS n(20,2)) AS Wl_Nieco, 
 CAST( Wname.Wl_Compee AS n(20,2)) AS Wl_Compee, 
 CAST( Wname.Wl_Comper AS n(20,2)) AS Wl_Comper, 
 CAST( Wname.Wl_Pen AS n(20,2)) AS Wl_Pen, 
 CAST( Wname.Wl_Penbl AS n(20,2)) AS Wl_Penbl, 
 CAST( Wname.Wl_Roundcf AS n(20,2)) AS Wl_Roundcf, 
 CAST( Wname.Wn_Ssp1 AS n(20,2)) AS Wn_Ssp1, 
 CAST( Wname.Wn_Ssp2 AS n(20,2)) AS Wn_Ssp2, 
 CAST( Wname.Wn_Ssp3 AS n(20,2)) AS Wn_Ssp3, 
 CAST( Wname.Wn_Ssp4 AS n(20,2)) AS Wn_Ssp4, 
 CAST( Wname.Wn_Ssp5 AS n(20,2)) AS Wn_Ssp5, 
 CAST( Wname.Wn_Ssp6 AS n(20,2)) AS Wn_Ssp6, 
 CAST( Wname.Wn_Ssp7 AS n(20,2)) AS Wn_Ssp7, 
 CAST( Wname.Wn_Ssp8 AS n(20,2)) AS Wn_Ssp8, 
Wname.Wn_Ssprate, 
...
FROM Wname Wname) E1

Answer 1

My own answer: After working with this for a while, I found that tables were added to EDMX model as read-only. This means that it was selecting from a select statement, which is redundant, and bloated the size of the select statement. When I manually edited the EDMX file and forced it to treat the table as read-write, the problem disappeared.

Moreover, upon Tom's advice, I grew to appreciate the code-first approach better. I did away with the EDMX model completely. I find this much easier to manage since I control how each field is treated via attributes on model's class properties. Using code-first, there is no chance that my customizations to the context classes might get overwritten; this is perfect for me since I need to customize constructors and override SaveChanges and other methods.

Answer 2

There are many reasons why the sql statement could exceed the 8000+ limit. I’ve tried, as the VFP EF Provider author, to reduce the sql statements as much as possible given the various constraints between VFP and Entity Framework. But there isn’t much wiggle room with 240 fields. Personally, I’d go with Cetin Basoz’s suggestion of treating it as multiple tables by creating multiple classes that represent a portion of the table to trim down the sql statement. Alternatively, you could just provide a subset of the fields using the LINQ Select operation. That should reduce the statement for you so that it doesn’t exceed the 8000+ limit.

(going off assumptions of your linq statement since it isn’t shown...) If you really need all columns then you might want to try to break up the query between a query that just gets the primary key and a query that uses the primary key to return all fields. This might work for you.

Answer 3

While you may have no choice as to the database, do you have a choice with Entity Framework? I find that using Dapper is a far better fit in this scenario and would give you low-level control over the query.