I am going to sum up my problem first and then offer massive details and what I have already tried.
Summary:
I have an internal winform app that uses Linq 2 Sql to connect to a local SQL Express database. Each user has there own DB and the DB stay in sync through Merge Replication with a Central DB. All DB's are SQL 2005(sp2or3). We have been using this app for over 5 months now but recently our users are getting a Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Detailed:
The strange part is they get that in two differnt locations(2 differnt LINQ Methods) and only the first time they fire in a given time period(~5mins).
One LINQ method is pulling all records that match a FK ID and then Manipulating them to form a Heirarchy View for a TreeView. The second is pulling all records that match a FK ID and dumping them into a DataGridView. The only things I can find in common with the 2 are that the first IS an IEnumerable and the second converts itself from IQueryable - IEnumerable - DataTable...
I looked at the query's in Profiler and they 'seemed' normal. They are not very complicated querys. They are only pulling back 10 - 90 records, from one table.
Any thoughts, suggestions, hints whatever would be greatly appreciated. I am at my wit's end on this....
public IList<CaseNoteTreeItem> GetTreeViewDataAsList(int personID)
{
var myContext = MatrixDataContext.Create();
var caseNotesTree =
from cn in myContext.tblCaseNotes
where cn.PersonID == personID
orderby cn.ContactDate descending,
cn.InsertDate descending
select new CaseNoteTreeItem
{
CaseNoteID = cn.CaseNoteID,
NoteContactDate = Convert.ToDateTime(cn.ContactDate).
ToShortDateString(),
ParentNoteID = cn.ParentNote,
InsertUser = cn.InsertUser,
ContactDetailsPreview = cn.ContactDetails.Substring(0, 75)
};
return caseNotesTree.ToList<CaseNoteTreeItem>();
}
AND THIS ONE
public static DataTable GetAllCNotes(int personID)
{
using (var context = MatrixDataContext.Create())
{
var caseNotes =
from cn in context.tblCaseNotes
where cn.PersonID == personID
orderby cn.ContactDate
select new
{
cn.ContactDate,
cn.ContactDetails,
cn.TimeSpentUnits,
cn.IsCaseLog,
cn.IsPreEnrollment,
cn.PresentAtContact,
cn.InsertDate,
cn.InsertUser,
cn.CaseNoteID,
cn.ParentNote
};
return caseNotes.ToList().CopyLinqToDataTable();
}
}
