What is causing these SQL casting errors on my ASP.NET MVC/AJAX site? [Video showing problem]
I'm frustrated... my site has suddenly become very unstable. So much so that hitting refresh over and over will cause it to crash. To investigate, I turned off all error handling so I could see some YSOD's.
Instead of trying to write it all out, I made a video showing the issue. You can see it here on YouTube.
Here's a copy of the 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.Controll开发者_C百科ers.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's trying to load there are two ajax calls to views that hit the database, render a table and return html. Portions of the menu at the top of the site are pulled from the database and then cached to prevent multiple return trips. Hosting is on IIS6 in a medium trust, shared environment.
Crazy how it works and then stops working for 3-4 minutes and then works again... perhaps a sql connection hanging and then timing out after a few minutes?
Any ideas would be very greatly appreciated! Thanks in advance!
Update: added data access code
I'm using LINQ to SQL in a repository pattern. Here's a query that one of the AJAX calls is using.
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 like so:
var ads = _db.FindAdsPerBase(MilBase, AdStatus.Active).Take(11);
You haven't given nearly enough information to troubleshoot, and (no offense) I'm not heading over to YouTube to watch a video. Here are some general troubleshooting steps I'd take and questions I'd ask myself:
When it fails, is it always the exact same exception? Or does it vary? If the same exception is always thrown then there might be a defect in your logic somewhere. If you get totally random exceptions then you may have a hardware or infrastructure issue.
First thing to do when you get LINQ to SQL exceptions is hook up SQL Profiler to see the exact query statement being sent to the server. Copy/paste that into SQL Management Studio and run it by hand. Look at the results and compare them to the data types of the object you're loading: is the query mapping an empty value to a non-nullable field? Maybe a query column is being mapped 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 time-specific code in your project. Are you doing any caching? Maybe the issue is related to the behavior that occurs when the cache is stale versus when it's not stale, or vice versa. Maybe you have a date/time calculation that overflows or does something funky for certain inputs?
Hook up a debugger and have it catch the exception. Then walk up the stack trace and look at the program state during a failure. Compare it to the program state when the app is working correctly. Anything stand out?
You may have changed something in your database (like datatype of a column) without remembering to re-create your LINQ classes. Thus LINQ to SQL may be causing that casting exception.
精彩评论