StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POLinq/Entity Framework gives no data error when using Oracle Linked-Server
text
Body
copied!<p>I am using entity framework (model first) with an entity that is mapped to a view. The view is in SQL server, but joins with SQL Server and Oracle tables using a linked server. This has been in place for several months with no issues.</p> <p><strong>I have updated this question.</strong> The issue is caused by the Oracle linked server. As mentioned above, my view joins SQL Server tables and Oracle table using the linked server (and OpenQuery). As a test, I moved all the data to a local table then changed my view to select from this table which takes the linked server out of the process. Now everything works - I am not getting a 'No Data Found' error.</p> <p>First, I use a Linq query to get distinct rows (parents). Then, while iterating, I use a second link query (on the original object) to get the children of each row. It is this second Linq query that fails - and it only fails when I access the second row. When there is a only one row returned it works. When multiple rows are returned I can access the first row but no more.</p> <p>Is there a problem using linked servers (specifically Oracle) and Entity Framework?</p> <p>Here are the details:</p> <p>The view represents data related to a collection of files that reside in a batch. The batch names may be duplicated because we can have more than one file per batch. Here is an example:<br/> File1 Batch1<br/> File1 Bacth2<br/> File3 Batch2<br/></p> <p>I have a model-first entity named LatestFileResult that maps to the Oracle/SqlServer view. <br/><br/><br/></p> <p>In my view model I have a method that gets the distinct batches (Batch1, Batch2) for batches that have errors. It’s called BatchErrors in the code sample below. I start with the LatestFileResult entity with no restrictions.</p> <p>In my view I successfully iterate though the batches and to show the data. This works as expected.</p> <p>On each iteration, I need to get the files that make up this batch and iterate through them. In the viewModel I have a method called FilesInBatch which takes a batchID and returns the rows with that batchID. It also starts with the LatestFileResult entity with no restrictions. <br/><br/><br/><br/> In my view, on the first outer iteration everything works as expected. It calls FilesInBatch (which has one row) and access the values returned. On the second outer iteration I get an error calling FilesInBatch on the forEach line itself (on the assignment - after exiting FilesInBatch). There should be two items returned but the error occurs getting the file – on the foreach statement. This only fails when there is more than 1 row. I can access the first row, but never the second. The foreeach pulls them all (including the second) and throws the exception. The error is:<br/> Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "OM_ORACLE". OLE DB provider "OraOLEDB.Oracle" for linked server "OM_ORACLE" returned message "ORA-01403: no data found".</p> <p><strong>I know what this means – that is not the question.</strong> However, there is data for this query. This is not an accurate error. In FilesInBatch I have two variables that get a count and they both show a 2 on the call that gives the ‘No data Found’ error.</p> <p>Both methods in the ViewModel use the entity framework LatestFileResult object with no restrictions. </p> <ul> <li>There is no way for the batchID I get in the first method not to exist in the second method. </li> <li>The fileInBatch method shows 2 files inside the method – then crashes with ‘No Data Returned’ in the view. If there is no data how do I get a count of 2.</li> </ul> <p>I am new to MVC and to Linq. I suspect there is something I do not understand about Linq and delayed binding. <br/><br/><br/> Now for the code.</p> <p>First, the ViewModel.</p> <p>Here is the method that gets the distinct batches:</p> <pre><code> public IQueryable<LatestFileResult> BatchErrors { get { IQueryable<LatestFileResult> results = new Domain.Concrete.ResultsRepository().LatestFileResult; //Get one batchFileID from each batch var x = from row in results where row.batchErrorCode > 0 group row by new { row.batchID } into single let maxChild = single.Max(row => row.batchFileID) select new { maxChild }; //Get one batch for each batchFileID IQueryable<LatestFileResult> batches = from row in results where x.Any(fileID => fileID.maxChild == row.batchFileID) orderby row.officeName, row.orgName, row.lastName, row.firstName, row.fileNumber, row.batchErrorCode, row.StartDate select row; return batches; } } </code></pre> <p>Here is the method that gets the files based on the bacthID.</p> <pre><code> public IQueryable<LatestFileResult> FilesInBatch(Int32 batchID) { IQueryable<LatestFileResult> results = new Domain.Concrete.ResultsRepository().LatestFileResult; var theCountBefore = results.Where(r => r.batchID == batchID); IQueryable<LatestFileResult> theFiles = from row in results where row.batchID == batchID select row; int theCountAfter = theFiles.Count(); return theFiles; } </code></pre> <p><em>Notice those variables theCountBefore and theCountAfter. They show that I have data on the call that crashes. Also, notice that both methods start with the same LatestFileResult entitiy object.</em></p> <p>And here is the code for the View.</p> <pre><code> @foreach (Domain.Entities.LatestFileResult row in Model.BatchErrors) { <tr> <td>@Html.DisplayFor(modelItem => row.batchName)</td> </tr> <tr> <td > @foreach (var file in Model.FilesInBatch(row.batchID)) { @Html.DisplayFor(modelItem => file.fileName) <br /> } </td> </tr> } </code></pre> <p>There error happens on this line: @foreach (var file in Model.FilesInBatch(row.batchID)). Using the debugger, I return from the Model.FilesInBbatch call (showing the correct count inside the method). Then the debugger highlights 'in' and when I step-over I get the error.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload