Is mixing ADO.NET and LINQ-TO-SQL bad? My data layer isn't working...

Question

UPDATE

As Mathias notes below, this exact problem has been reported and resolved here: ASP.NET-MVC (IIS6) Error on high traffic: Specified cast is not valid

ORIGINAL POST

This may be too specific a debugging issue to be posted here, but I'm posting it anyway in the hopes that it produces a solution that others find useful.

I have a web application that operates under moderate load -- maybe 5 requests per second. It has some older code talking to Sql via ADO.NET + DataReaders and has been using this same technique for at least five years without problem. It also has some newer code using LINQ-to-SQL. Both techniques use the same connection string to maximize connection pool reuse.

Recently I'm experiencing a very weird behavior described by these symptoms:

1. Everything will work perfectly for about a day, then suddenly every call (or nearly every call) to the data layer (both ADO.NET and LINQ) returns data that cannot be parsed by my code -- I'll get exceptions like "Unable to cast object of type 'System.Int32' to type 'System.String'." or "Sequence contains no elements" or "IndexOutOfRangeException" or "Invalid attempt to call Read when reader is closed".

2. Interestingly, I never get exceptions from SqlCommand.ExecuteReader() or DataReader.Read() -- the exceptions only occur when I try to parse the IDataRecord that is returned.

3. I am able to fix the problem temporarily by restarting either Sql or IIS. After a few hours it comes back again.

4. I've tried monitoring the number of connections in the connection pool and it never goes above 3 or so. Certainly never above 100.

5. I'm not getting anything in the event log that indicates any problem with Sql or IIS.

6. The drive has 9 GB empty space.

7. I suspected bad RAM, but the server is using registered ECC DIMMs.

8. I have other applications using ADO.NET that work fine and never exhibit the problem.

9. When the problem is occurring I can call the exact same stored procedures via Management Studio and they return the correct, expected results.

Here is my pattern for ADO.NET access:

using (var dbConn = Database.Connection) // gets already-open connection
{
  var cmd = new SqlCommand("GetData", dbConn);
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Parameters.AddWithValue("@id", id);

  SomeDataObject dataObject = null;

  var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleRow);

  if (dr.Read())
    dataObject = new SomeDataObject(dr);

  dr.Close();

  return dataObject;
}

Theory: Is it possible that the combination of ADO.NET in one part of the code and LINQ in another part of the code, both using the same connections from the connection pool, is having some weird side-effect?

Question: Are there any debugging steps I should be trying? Any events logs or performance metrics that might help?

Answer 1

20+ open connections on 5 hits/second is a red flag to me. We have close to 100 hits/sec and hover around 10 connections.

What about memory use? Is it high?

I suspect you're having problems with releasing resources. I'm still getting my feet wet with LINQ to SQL and I too have a long positive expereince with ADO.NET. I wonder if you're missing a pattern with LINQ to SQL that cleans up connections, etc.

Try this - can you isolate the ADO.NET code from LINQ in the application? If you ONLY make ADO.NET calls, what happens to memory, connection count, etc? Then add in the LINQ stuff and see how it affects it.

Resource problems seem to 'start up late' becuase they take a while to accumulate.

Answer 2

UPDATE

I found someone on SO who apparently has the same issue

ASP.NET-MVC (IIS6) Error on high traffic: Specified cast is not valid.

It is expained in the answer from ATLE

ORIGINAL POST

I have seen issues in a linq-to-sql application when under load. I used MVC - Storefront from Rob Connery so I guess a lot of people use this kind of application layout. The application worked perfectly when under little load, but there where strange errors that sounded like the one you describe when under medium load.

I suspected that it was an issue with where the db-context was stored.

In my case it was easy to reproduce: I used jmeter and had 5 threads each having a couple of requests per second (20 I guess). I realy needed to have load originating from multiple threads.

So my advice is: Try to reproduce the error in development by creating some load with Jmeter (not good for ASP.NET but for ASP.NET MVC) or application center test.