I have two tables, DH_MASTER and DH_ALIAS. DH_MASTER contains information about a person, including their name. DH_ALIAS contains AKA records about the person. The tables are linked by the Operator field which is a primary key in DH_MASTER.
The users want to search by the name stored in DH_MASTER as well as search through all of their known aliases. If any matches are found in either DH_MASTER or DH_ALIAS then the DH_MASTER entity should be returned.
I created the query below which should give the results I described (return any DH_MASTER rows where the DH_MASTER.Name == name or DH_MASTER.DH_ALIAs(n).Name == name).
It works fine if I use only ONE of the .Contains lines. It doesn't matter which one I use. But the execution fails when I try to use BOTH at the same time.
qry = From m In Context.DH_MASTERs _
Where (m.Name.Contains(name)) _
OrElse ((From a In m.DH_ALIAs _
Where a.Name.Contains(name)).Count() > 0) _
Select m
The LinqToSQL Query evaluates to the following SQL code (as displayed in the SQL Server Query Visualizer)
SELECT [t0].[Operator], [t0].[Name], [t0].[Version]
FROM [DHOWNER].[DH_MASTER] AS [t0]
WHERE ([t0].[Name] LIKE %smith%) OR (((
SELECT COUNT(*)
FROM [DHOWNER].[DH_ALIAS] AS [t1]
WHERE ([t1].[Name] LIKE %smith%) AND ([t1].[Operator] = [t0].[Operator])
)) > 0)
EDIT: Checking the "Show Original" box in the Query Visualizer reveals the parameterized query as expected so this block of text below should be ignored.
I don't know if this is a problem or not but the `.Contains` evaluates to a `LIKE` expression (which is what I expect to happen) but the parameter is not encapsulated in apostrophes.
The interesting thing is that if I copy/paste the SQL Query into SQL 2005 Query Analyzer and add the apostrophes around the LIKE parameters, it runs just fine. In fact, it's lightning quick (blink of an eye) even with more than 2 million rows.
But when the LINQ query runs, the web app locks up for about 31 seconds before it finally fails with this error on gv.DataBind: Exception has been thrown by the target of an invocation.
With this innerException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Does anyone know why this happens and how the behavior can be worked around? It's driving me nuts because the LinqToSql-generated SQL runs fine in query analyzer!
Update:
I have refactored my code based on the techniques in the answer. This works!
qry = From m In qry _
Where m.Name.Contains(name) OrElse _
m.DH_ALIAs.Any(Function(aliasRec) aliasRec.Name.Contains(name)) _
Select m
-
Linq to sql doesn't specify values directly into the query, it uses parameters. Are you sure it had the contains parameter value directly in the sql?
Anyway, the timeout is likely caused by a deadlock: the query wants to read from a row in a table which is locked by another (insert/update) query /transaction and that query apparently takes longer to complete.
Jeff Robinson : You're right about the first part - when I click the "Show Original" checkbox in the visualizer it shows the parameterized query. However, these tables are never inserted/updated other than through a nightly SSIS package. -
This might not be appropriate, since your problem is different, but I remember a problem in one of my programs: Contains() would not work (in my case it would throw an Exception when evaluating), so maybe the Contains-method is a bit broken.
I replaced
result.Contains( x )with
result.Any( p => p == x )which did work.
Can you try if that works? At least it might be a step in the right direction.
Jeff Robinson : Using your idea, I was able to refactor my code a bit. This works better than my original code but as you can see, it does not eliminate the need for the .Contains call because I still want to return "fuzzy" matches. For some reason, calling .Contains a second time inside a lambda expression works, but calling it a second time outside the lambda expression does not work!
