Can´t create edmx.File because of "The Source has no Data Rows"

Nov 16, 2012 at 9:40 PM
Edited Nov 16, 2012 at 9:40 PM

Hi.

Im trying do use your Entity Provider on my FoxPro Database.

But while creating the edmx-File i´m getting the following exception:

System.Data.EntityCommandExecutionException
inner Exception:

System.InvalidOperationException

with the Error Message

The Source has no Data Rows.

System is VS2010, Win 7 64 SP1, VFP 9

 

Any Information on that problem ?

Coordinator
Nov 16, 2012 at 10:00 PM

I believe that error indicates that I’m doing a linq statement on a DataTable that doesn’t have any rows. Could you provide me with a copy of your data structure so that I could debug this issue? You can email me at tombrothers@outlook.com.

Nov 19, 2012 at 9:42 AM
Edited Nov 19, 2012 at 9:42 AM

Feedback: Working in new Version released yesterday, edxm is created.

While testing, i´ve got another "problem" If i don´t set an order in a Linq-Statement, i´ll get an exception:
Don´t know if its a bug or misstake in my code. The original Entity Generator seems to take Objects by the first field of a table if i use .FirstorDefault().

 

        public MainWindow()
        {
            InitializeComponent();

            Entities1 x = new Entities1();
            var test = from i in x.Person select i;
            var testwithOrder = from i in x.Person orderby i.Per_Cname select i;

            //Working
            Person anotherPerson = testwithOrder.FirstOrDefault();
            
            //Not Working
            Person onePerson=test.FirstOrDefault();
            //Inner Exception:{"SQL: TOP requires an ORDER BY."}
        }

Thx for your support and the very interesting projekt!
Seems to be a very good solution to slowly migrate our FoxPro-Projekts to .net Code.
Nov 19, 2012 at 10:10 AM

Nearly same Code, another Problem:

Our goal is to use the FoxPro Data over a WCF Data Webservice. For testing, i implented a Webservice with "your" Entities as Source:

Creating Webservice

 

        public static void InitializeService(DataServiceConfiguration config)
        {
            config.SetEntitySetAccessRule("*", EntitySetRights.All);
            config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
        }

Using the Webservice:
private Uri svcUri = new Uri("http://localhost:12345/WcfDataService1.svc");
        public MainWindow()
        {
            InitializeComponent();
            //Bug:
            //Using as Webservice
            ServiceReference2.Entities x = new ServiceReference2.Entities(svcUri);
            var test = from i in x.Person select i;
            var testwithOrder = from i in x.Person orderby i.Per_Cname select i;
            //Not Working
            ServiceReference2.Person anotherPerson = testwithOrder.FirstOrDefault();
            //XAML-Parse Exception
            /*
            System.Windows.Markup.XamlParseException ist aufgetreten.
  HResult=-2146233087
  Message=Zeilennummer "3" und Zeilenposition "9" von "Durch den Aufruf des Konstruktors für Typ "WpfApplication12.MainWindow", der den angegebenen Bindungseinschränkungen entspricht, wurde eine Ausnahme ausgelöst.".
  Source=PresentationFramework
  LineNumber=3
  LinePosition=9
  StackTrace:
       bei System.Windows.Markup.WpfXamlLoader.Load(XamlReader xamlReader, IXamlObjectWriterFactory writerFactory, Boolean skipJournaledProperties, Object rootObject, XamlObjectWriterSettings settings, Uri baseUri)
  InnerException: System.Data.Services.Client.DataServiceQueryException
       HResult=-2146233079
       Message=Fehler beim Verarbeiten dieser Anforderung.
       Source=System.Data.Services.Client
       StackTrace:
            bei System.Data.Services.Client.DataServiceRequest.Execute[TElement](DataServiceContext context, QueryComponents queryComponents)
            bei System.Data.Services.Client.DataServiceQuery`1.Execute()
            bei System.Data.Services.Client.DataServiceQuery`1.GetEnumerator()
            bei System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
            bei System.Data.Services.Client.DataServiceQueryProvider.ReturnSingleton[TElement](Expression expression)
            bei System.Data.Services.Client.DataServiceQueryProvider.Execute[TResult](Expression expression)
            bei System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)
            bei WpfApplication12.MainWindow..ctor() in C:\Lokale Projekte\WpfApplication12\WpfApplication12\MainWindow.xaml.cs:Zeile 37.
       InnerException: System.Data.Services.Client.DataServiceClientException
            HResult=-2146233079
            Message=<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
  
  <message xml:lang="de-DE">Fehler beim Verarbeiten dieser Anforderung.</message>
</error>
            Source=System.Data.Services.Client
            StatusCode=500
            StackTrace:
                 bei System.Data.Services.Client.QueryResult.Execute()
                 bei System.Data.Services.Client.DataServiceRequest.Execute[TElement](DataServiceContext context, QueryComponents queryComponents)
            InnerException: 
 
             
             
             */
            //
        }
Coordinator
Nov 19, 2012 at 1:28 PM

Glad to hear the update fixed your EDMX issue.  I found a couple areas in the code that I suspected to be the problem.

FirstOrDefault Issue:
FirstOrDefault works a little bit different with the VFP provider than the default provider because VFP requires that a SELECT statement with a TOP clause must include an ORDER BY clause (unlike SQL Server).  However, if you have a Primary Key and don't provide an ORDER BY clause the VFP provider will inject an ORDER BY clause using the Primary Key.  I figured that this would be safe assumption for the desired behavior and wouldn't cause any performance issues.  I considered using the first field in the projection if a there wasn't a Primary Key but decided it would probably be best to let the default VFP behavior take over and force the developer to decide the ORDER BY criteria.




For your WCF Data Service I would suggest adding the following to the InitializeService method so that you can get meaningful error messages while debugging.

#if DEBUG
            config.UseVerboseErrors = true;
#endif

Nov 19, 2012 at 3:26 PM

@FirstOrtDefault Issue: Thx for explanation

@WCF Problem:

Hm, so u think there´s a bug in my WCF Data Service? It´s a little bit confusing.

            ServiceReference1.Entities1 asd = new ServiceReference1.Entities1(svcUri);
            var persons = from i in asd.Person orderby i.Per_Cnachname select i;
            //working (e.g. 63 at my Test-Database)
            int count = persons.Count();
            //not working, XAML-Parse-Exception (although there are 63 and they are sorted)
            var person = persons.FirstOrDefault();
On the one hand, it´s working, if i use the Entity-Set locally in my WPF-Test-Applikation (like the first code i posted above).
So i would say: Year, my Webservice is wrong!

On the other hand, my Webservice gives Data to so Local System (e.g. Count, or in a foreach-loop), but i got an exception when i call .FirstOrDefault().
Confusing... but i´ll try a little bit more.

Coordinator
Nov 19, 2012 at 4:02 PM

I don’t see anything wrong with your code and I know that FirstOrDefault does work with WCF Data Service so I’m not sure what’s going on here. Could you add the UseVerboseErrors setting and re-post the stack trace?

Nov 20, 2012 at 7:40 AM
Edited Nov 20, 2012 at 9:08 AM

The Stack Trace, seems to be a problem with ordering:

 

System.Windows.Markup.XamlParseException ist aufgetreten.
  HResult=-2146233087
  Message=Zeilennummer "3" und Zeilenposition "9" von "Durch den Aufruf des Konstruktors für Typ "WpfApplication12.MainWindow", der den angegebenen Bindungseinschränkungen entspricht, wurde eine Ausnahme ausgelöst.".
  Source=PresentationFramework
  LineNumber=3
  LinePosition=9
  StackTrace:
       bei System.Windows.Markup.WpfXamlLoader.Load(XamlReader xamlReader, IXamlObjectWriterFactory writerFactory, Boolean skipJournaledProperties, Object rootObject, XamlObjectWriterSettings settings, Uri baseUri)
  InnerException: System.Data.Services.Client.DataServiceQueryException
       HResult=-2146233079
       Message=Fehler beim Verarbeiten dieser Anforderung.
       Source=System.Data.Services.Client
       StackTrace:
            bei System.Data.Services.Client.DataServiceRequest.Execute[TElement](DataServiceContext context, QueryComponents queryComponents)
            bei System.Data.Services.Client.DataServiceQuery`1.Execute()
            bei System.Data.Services.Client.DataServiceQuery`1.GetEnumerator()
            bei System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
            bei System.Data.Services.Client.DataServiceQueryProvider.ReturnSingleton[TElement](Expression expression)
            bei System.Data.Services.Client.DataServiceQueryProvider.Execute[TResult](Expression expression)
            bei System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)
            bei WpfApplication12.MainWindow..ctor() in C:\Lokale Projekte\WpfApplication12\WpfApplication12\MainWindow.xaml.cs:Zeile 50.
       InnerException: System.Data.Services.Client.DataServiceClientException
            HResult=-2146233079
            Message=<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
  <code></code>
  <message xml:lang="de-DE">Fehler beim Verarbeiten dieser Anforderung.</message>
  <innererror>
    <message>Fehler beim Ausführen der Befehlsdefinition. Weitere Informationen finden Sie in der internen Ausnahme.</message>
    <type>System.Data.EntityCommandExecutionException</type>
    <stacktrace>   bei System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   bei System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
   bei System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   bei System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   bei System.Data.Objects.ObjectQuery`1.GetEnumeratorInternal()
   bei System.Data.Objects.ObjectQuery.System.Collections.IEnumerable.GetEnumerator()
   bei System.Data.Services.WebUtil.GetRequestEnumerator(IEnumerable enumerable)
   bei System.Data.Services.DataService`1.SerializeResponseBody(RequestDescription description, IDataService dataService)
   bei System.Data.Services.DataService`1.HandleNonBatchRequest(RequestDescription description)
   bei System.Data.Services.DataService`1.HandleRequest()</stacktrace>
    <internalexception>
      <message>SQL: ORDER BY clause is invalid.</message>
      <type>System.Data.OleDb.OleDbException</type>
      <stacktrace>   bei System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   bei System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   bei System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   bei System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   bei System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   bei System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
   bei VfpEntityFrameworkProvider.VfpOleDb.VfpCommand.ExecuteDbDataReader(CommandBehavior behavior)
   bei System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   bei System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)</stacktrace>
    </internalexception>
  </innererror>
</error>
            Source=System.Data.Services.Client
            StatusCode=500
            StackTrace:
                 bei System.Data.Services.Client.QueryResult.Execute()
                 bei System.Data.Services.Client.DataServiceRequest.Execute[TElement](DataServiceContext context, QueryComponents queryComponents)
            InnerException: 


Forcing the Excecution of the Linq-Statement via .ToList() is successfull:
//does well
var viele = (from i in ent.Person1Menge orderby i.Per_Cname select i).ToList();
var one=viele.FirstOrDefault();
Coordinator
Nov 20, 2012 at 1:23 PM
Edited Nov 20, 2012 at 1:24 PM

That is weird that the ToList worked and the FirstOrDefault failed.  But you certainly don't want to use the ToList because that will bring all your data to the client application.  The next step in debugging this issue is to figure out what the difference is between the two SELECT statements that are generated.  I would imagine that they are basically the same with the FirstOrDefault including a TOP clause.

Adding the following config setting will send the VFP commands to the Visual Studio Debug Output Window.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <system.diagnostics>
    <sources>
      <source name="VfpClient" switchValue="Information" />
    </sources>
  </system.diagnostics>
</configuration>




Or if you want, I can debug this issue for you.  Just send me a copy of the project to tombrothers@outlook.com.

Nov 21, 2012 at 2:01 PM

Hey.
One news on the Problem: The table i tryed was readonly because of no primary key. After providing a primary key und rebuilding the .emdx, the problem is gone. Setting a primary key to all tables is something i should do anyway if i´m working with entity frameworks i think.
The problem for me is gone, but its still crazy.
If interested, i can provide you with debug information on that.
The code above

<source name="VfpClient" switchValue="Information" />

Do i have to change something there? On this way i don´t get messages after adding this to the Web.config.
Don´t know if its important, i´m not using the VFP Client for providing the connection to the Ado.net Entity Framework-Mapper, because i can´t choose the connections created by the Visual FoxPro Data Client in the Entity Source Selection.
I only can choose connections created by:
-Add Connection
-Change Button
-Other (not FoxPro)
Selection .net Frameworkk Data Provider for Visual Foxpro

At all, your projekt ist fantastic work. I´ll continue to test und provide informations (i hope my english is readable ;-))

PS.

I´m not allowed to send projekt informations like database structure, sry.

Coordinator
Nov 23, 2012 at 1:50 AM
Edited Nov 23, 2012 at 1:50 AM

Ah, the problem was with readonly mode.  In that scenario I have less control over the generated sql.  Adding a primary key and rebuilding the edmx is the ideal fix.  Given your finding, I don't need to see the trace information.