Dynamic LINQ .Contains() Throws Timeout expired. Error

He开发者_StackOverflow中文版y Guys,

I'm working on this query to export some data from a sql 2008 database on vb.net 3.5 website. I'm using dynamic linq after the inital query to filter down the results based on date, state, country, specialty, and hospital affiliation.

Each of those filter is has a singular value in the query. Except Hospital Affiliation which has a comma delimited list. ex: OSUMC, Hospital West, Hopsital East

When filtering, I need to do a .Contains to see if the comma delimted list contains the specific hospital. ex: (OSUMC, HospitalWest, Hospital East).contains("OSUMC")

If i do a filter on Date, State, Country, and Specialty the query runs perfectly fine. However if I add in a filter for hospital affiliation which is a .Contains() filter then I get Timeout Errors.

Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Dim ctx As New OSUMCEntities
    Dim query = (From ca As OSUMCModel.vw_ConferenceAttendance In ctx.vw_ConferenceAttendance Select New With _
                                                                                                     { _
                                                                                                         .LastName = ca.LastName, _
                                                                                                         .FirstName = ca.FirstName, _
                                                                                                         .UserID = ca.UserID, _
                                                                                                         .UserName = ca.Username, _
                                                                                                         .Degree = ca.DegreeName, _
                                                                                                         .Specialty = ca.Specialty, _
                                                                                                         .Profession = ca.Profession, _
                                                                                                         .HospitalAffiliations = ca.HospitalAffiliations, _
                                                                                                         .Address1 = ca.MailingAddress1, _
                                                                                                         .Address2 = ca.MailingAddress2, _
                                                                                                         .City = ca.MailingCity, _
                                                                                                         .State = ca.MailingState, _
                                                                                                         .ZipPostal = ca.MailingZip, _
                                                                                                         .Country = ca.MailingCountry, _
                                                                                                         .ConferenceID = ca.ConfID, _
                                                                                                         .Title = ca.Title, _
                                                                                                         .Date = ca.StartDate, _
                                                                                                         .MaxCredits = ca.Credits, _
                                                                                                         .CreditsAwarded = ca.CreditHours, _
                                                                                                         .CreditsAssignedOn = ca.AssignedOn, _
                                                                                                         .LastUpdated = ca.LastUpdate _
                                                                                                     })
    If (txtDateStart.Text <> "" And txtDateEnd.Text <> "") Then
        Dim StartDate As Date = txtDateStart.Text
        Dim EndDate As Date = txtDateEnd.Text
        query = query.Where("(Date >= @0 And  Date <=@1)", StartDate, EndDate)
    ElseIf (txtDateStart.Text <> "") Then
        Dim StartDate As Date = txtDateStart.Text
        query = query.Where("Date >= @0", StartDate)
    ElseIf (txtDateEnd.Text <> "") Then
        Dim EndDate As Date = txtDateEnd.Text
        query = query.Where("Date <= @0", EndDate)
    End If
    If (ddlState.SelectedValue <> "Export All") Then
        query = query.Where("State = @0", ddlState.SelectedValue)
    End If
    If (ddlCountry.SelectedValue <> "Export All") Then
        query = query.Where("Country = @0", ddlCountry.SelectedValue)
    End If
    If (ddlSpecialty.SelectedValue <> "Export All") Then
        query = query.Where("Specialty = @0", ddlSpecialty.SelectedValue)
    End If
    If (ddlHospitals.SelectedValue <> "Export All") Then
        Dim Hospital As String = ddlHospitals.SelectedValue
        query = query.Where("HospitalAffiliations.Contains(@0)", Hospital)
    End If

analyze the query that Linq execute in sql server and check the time in sql profiler.