0

First a disclaimer - I did my research on the topic and found plenty of material. Sadly, none of that was to any help, as each answer can be counted into the following categories.

  1. Permission issues for accessing database
  2. Miscellaneous missconfigurations
  3. Incorrect garbage disposal
  4. Application of MultipleActiveResultSets
  5. Usage of ToList() indiscriminately

My issue occurs only when fetching/executing larger sets, so we can eliminate (1) and (2). Since I can execute a number of queries in the same context getting the exception on fourth or fifth reading, we can eliminate (3). I've checked the connection string and (4) can't be the problem because the multiplicity is already enabled. The last candidate is (5) but it can be eliminated as well, because I'm getting the same issues when wsitching to IEnumerable.

My impression is that the server somehow times out because the current operation takes too long time. I can either fetch my list entirely without filtration for filtration in the client (which takes a slight forever and fails due to that) or I can apply Where (which requires to use Contains versus another list, which is equivalent to IN in SQL'ish and fails due to that).

I'm kind of at the point when banging my head into a wall slowly emerges as a reasonable approach. I have no control over the DB schema nor ISS (so this kind of hints can't even be tested). I have some influence on the configuration of the EF but due to ignorance and outmost respect to the things I don't fully master, I'm very cautious. What can be done here?

The config file looks as follows. 

app.config(50,191): 
<add name="ModelContainer"
     connectionString="metadata=res://*/Model.csdl
       |res://*/Model.ssdl
       |res://*/Model.msl;
       provider=System.Data.SqlClient;
       provider connection string=&quot;
       data source=1.2.3.4;
       initial catalog=DataBaseName;
       user id=NServiceBus;
       password=Abc123();
       MultipleActiveResultSets=True;
       App=EntityFramework&quot;" 
     providerName="System.Data.EntityClient" />

C# (when I switch the execution order, the failure happens on the second line - beep or boop, whichever comes first does succeed).

List<Beep> beeps = context.Beeps.ToList();
List<Boop> boops = context.Boops.ToList();
Community
  • 1
  • 1
Konrad Viltersten
  • 36,151
  • 76
  • 250
  • 438
  • _"The underlying provider failed on Open for Entity Framework"_ is a "catch-all exception". Most causes you found were about incorrectly formatted connection strings and wrong permissions being set, neither of which are the case here. Inspect the InnerException for the actual error. My guess: your database is out of connections. – CodeCaster Sep 06 '15 at 10:34
  • @CodeCaster Good point - that explains the wide range of answers to the same issue. In my case, I'm the only user banging on the SQL door right now (Sunday + test environment) and the inner nag is something like *action not valid for the current state of the transaction* - free translation from Swedish (because coding and error messaging in the native tongue helps **soooo much**)... – Konrad Viltersten Sep 06 '15 at 10:41
  • Try http://unlocalize.com/. It seems to be "The operation is not valid for the state of the transaction", which in turn can be thrown when a transaction times out. Try optimizing the query or increasing the transaction timeout. – CodeCaster Sep 06 '15 at 10:42
  • 1
    @CodeCaster Syntax corrected - good eyes. However, the page you gave me link to gave me this: "Content Encoding Error" followed by "The page you are trying to view cannot be shown because it uses an invalid or unsupported form of compression." It seems it refuses to translate Swedish... – Konrad Viltersten Sep 06 '15 at 10:45
  • Lol, it seems down for now - doesn't work for me either. See my updated comment above. – CodeCaster Sep 06 '15 at 10:46
  • @CodeCaster Right - optimizing the query isn't possible because of the data I'm working with (fetch all invoices that have a field with value from another fetch done on customers). How do I set the transaction timeout? – Konrad Viltersten Sep 06 '15 at 10:47
  • See [Entity Framework Timeouts](http://stackoverflow.com/questions/6232633/entity-framework-timeouts). Again, it doesn't _have_ to be timeouts, inspect the exceptions carefully. – CodeCaster Sep 06 '15 at 10:48
  • Put your comments as a reply. It might in the end be the answer. :)= – Konrad Viltersten Sep 06 '15 at 10:58

0 Answers0