LinqToSql generates an incorrect query to the database

Question

I have a problem with LinqToSql and PredicateBuilder

I have the following piece of code

Dim vQuery As IQueryable(Of Table1) = pContext.Table1
Dim predicate As Expression(Of System.Func(Of Table1, Boolean)) = Nothing

....

Dim predicateAdd As Expression(Of System.Func(Of Table1, Boolean)) = PredicateBuilder.True(Of Table1)()

predicateAdd = predicateAdd.And(Function(a As Table1) a.Table2.Table3.Select(Function(c) c.Column1).Contains(TextToSearch))

predicate = predicate.And(predicateAdd)

....

Me.Grid.DataSource = vQuery.Where(predicate)

which generates the following query to the database

SELECT [t0].[ID], ...
FROM [dbo].[Table1] AS [t0]
WHERE EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[Table2] AS [t1], [dbo].[Table3] AS [t2]
    WHERE ([t2].[Column1] = @p0) AND ([t1].[ID] = [t0].[ID]) AND ([t2].[ID] = [t1].[ID])
    )   
-- @p0 is TextToSearch

But that's not what I want.

This line of code

a.Table2.Table3.Select(Function(c) c.Column1).Contains(TextToSearch)

would have to generate the next query to the database

WHERE ([t2].[Column1] Like @p0)

but not this

WHERE ([t2].[Column1] = @p0)

If I'm wrong somewhere or is this a bug in LinqToSql

score 1 · Answer 1 · answered Nov 16 '11 at 12:48

You are calling Contains on the result of a call to Select(), which is not the same as calling it on a string field. The code being generated is exactly right for what you have written.

Since you called Contains() that way, it is testing for equality on each item in the returned collection.

LinqToSql generates an incorrect query to the database

1 Answers1