Timeout Expired error Using LINQ

Posted by Refracted Paladin on Stack Overflow See other posts from Stack Overflow or by Refracted Paladin
Published on 2010-06-01T15:25:53Z Indexed on 2010/06/01 18:23 UTC
Read the original article Hit count: 709

Filed under:
|
|
|
|

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();
    }
}

© Stack Overflow or respective owner

Related posts about c#

Related posts about .NET