What causes these SQL casting errors on my ASP.NET MVC / AJAX site? [Video display problem]

Question

I'm upset ... my site suddenly became very erratic. So much so that the repetition of the blow again and again will lead to its failure. To research, I turned off all error handling so that I could see some YSODs.

Instead of trying to write all this, I made a video showing the problem. You can see it here on YouTube .

Here is a copy of stacktrace:

[InvalidCastException: Specified cast is not valid.] System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) +847 System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries) +113 System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) +344 System.Data.Linq.DataQuery`1.System.Linq.IQueryProvider.Execute(Expression expression) +23 System.Linq.Queryable.Count(IQueryable`1 source) +240 MvcPaging.PagedList`1.Initialize(IQueryable`1 source, Int32 index, Int32 pageSize, Nullable`1 totalCount) in C:\Users\BikGame\Desktop\src\MvcPaging\PagedList.cs:63 MvcPaging.PagedList`1..ctor(IQueryable`1 source, Int32 index, Int32 pageSize, Nullable`1 totalCount) in C:\Users\BikGame\Desktop\src\MvcPaging\PagedList.cs:25 MvcPaging.PagedList`1..ctor(IQueryable`1 source, Int32 index, Int32 pageSize) in C:\Users\BikGame\Desktop\src\MvcPaging\PagedList.cs:19 MvcPaging.PagingExtensions.ToPagedList(IQueryable`1 source, Int32 pageIndex, Int32 pageSize) in C:\Users\BikGame\Desktop\src\MvcPaging\PagingExtensions.cs:63 ApoAds.Controllers.HomeController.Index() in C:\Users\BikGame\Documents\Visual Studio 2008\Projects\APOAds-MultiBaseBiz\Controllers\HomeController.cs:22 lambda_method(ExecutionScope , ControllerBase , Object[] ) +39 System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +17 System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) +178 System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +24 System.Web.Mvc.<>c__DisplayClassa.<InvokeActionMethodWithFilters>b__7() +53 System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation) +258 System.Web.Mvc.<>c__DisplayClassc.<InvokeActionMethodWithFilters>b__9() +20 System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation) +258 System.Web.Mvc.<>c__DisplayClassc.<InvokeActionMethodWithFilters>b__9() +20 System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList`1 filters, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +193 System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName) +382 System.Web.Mvc.Controller.ExecuteCore() +123 System.Web.Mvc.ControllerBase.Execute(RequestContext requestContext) +23 System.Web.Mvc.ControllerBase.System.Web.Mvc.IController.Execute(RequestContext requestContext) +7 System.Web.Mvc.MvcHandler.ProcessRequest(HttpContextBase httpContext) +144 System.Web.Mvc.MvcHandler.ProcessRequest(HttpContext httpContext) +54 System.Web.Mvc.MvcHandler.System.Web.IHttpHandler.ProcessRequest(HttpContext httpContext) +7 System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +181 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +75 

On the page that is trying to load, there are two ajax calls for views that fall into the database, display the table and return the html. Parts of the menu at the top of the site are retrieved from the database and then cached to prevent multiple return trips. The hosting is on IIS6 in a medium of trust, a shared environment.

Crazy, how does it work, and then stops working for 3-4 minutes, and then works again ... maybe the connection to the site hangs and then disconnects after a few minutes?

Any ideas would be greatly appreciated! Thanks in advance!

Update: added data access code

I am using LINQ to SQL in the repository template. This uses a query that uses one of the AJAX calls.

 public IQueryable<Ad> FindAdsPerBase(string baseName, AdStatus status) { return (from ad in _db.Ads join b in _db.AdBases on ad.AdID equals b.AdID where b.MilBase.BaseName.ToLower() == baseName.ToLower() && ad.Status == (byte)status select ad).Distinct().OrderByDescending(x => x.DateEntered); } 

and it is called in the controller as follows:

 var ads = _db.FindAdsPerBase(MilBase, AdStatus.Active).Take(11); 

Answer 1

You haven’t provided enough troubleshooting information, and (no offense) I don’t go to YouTube to watch the video. Here are some general troubleshooting steps I would take and the questions I asked myself:

• When it fails, is it always the same exception? Or is it changing? If the same exception is always thrown, then there may be a flaw in your logic. If you get completely random exceptions, then you may have a problem with the equipment or infrastructure.

• The first thing to do when you get LINQ to SQL exceptions is to enable SQL Profiler to see the exact query request sent to the server. Copy / paste this into SQL Management Studio and run it manually. Look at the results and compare them with the data types of the object you are loading: is it a query matching a null value with a non-zero field? Maybe the query column maps to a property of a different type?

• If it works for 3-4 minutes, then stops for 3-4 minutes, then works again, look for any specific time code in your project. Do you do caching? Maybe the problem is related to the behavior that occurs when the cache is obsolete, or if it is not outdated, or vice versa. Maybe you have a date / time calculation that overflows or does something funky for certain inputs?

• Connect a debugger and eliminate the exception. Then go along the stack trace and look at the state of the program during the crash. Compare it with the state of the program when the application is working correctly. Does anything stand out?

Answer 2

You may have changed something in your database (for example, the column data type), remembering to recreate your LINQ classes. Thus, LINQ to SQL can throw an exception exception.