LINQ: multiple Where calls only uses last one???

Question

I'm trying to get a query going that will search multiple tags. The tags are db based and I've related them to the entity with a junction table. If I search with 1 tag, I get the correct results, but if I search with 2 tags, I only get the entities that match the second tag.

Here's the C# code that builds the IQueryable:

  var awTable = db.Artworks.Where( aw => true );  //default get all

  awTable = awTable.Where( aw => (bool)aw.IsArtworkVisible  );


  foreach ( SearchTag tagToMatch in tagList )
                {

                    awTable = awTable.Where( aw => aw.ArtworkName.Contains( tagToMatch.SearchTagText )
                                               || db.SearchTag_x_Artworks.Where( stxa => stxa.SearchTagID == tagToMatch.SearchTagID )
                                               .Select( stxa => stxa.ArtworkID ).Contains( aw.ArtworkID ) );


                }

Here's the resulting SQL, which, if I plug it into a query window and set the parameter values, includes both where clauses and returns the correct list of entities. (WTF!?!?)

    {SELECT [t0].[ArtworkID], [t0].[ArtworkName], ... [t0].[MediumID]
FROM [dbo].[Artworks] AS [t0]
WHERE ((EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[SearchTag_x_Artwork] AS [t1]
    WHERE ([t1].[ArtworkID] = [t0].[ArtworkID]) AND ([t1].[SearchTagID] = @p0)
    )) OR ([t0].[ArtworkName] LIKE @p1)) AND ((EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[SearchTag_x_Artwork] AS [t2]
    WHERE ([t2].[ArtworkID] = [t0].[ArtworkID]) AND ([t2].[SearchTagID] = @p2)
    )) OR ([t0].[ArtworkName] LIKE @p3)) AND (([t0].[IsArtworkVisible]) = 1)
}

This is kind of befuddling, any advice is appreciated. Thanks.

Answer 1

var awTable = db.Artworks.Where( aw => (bool)aw.IsArtworkVisible );  //the first was unnecessary

foreach ( SearchTag tagToMatch in tagList )
{
    awTable = awTable.AndAlso(aw => 
        aw.ArtworkName.Contains(tagToMatch.SearchTagText) ||
        db.SearchTag_x_Artworks.Where(stxa => stxa.SearchTagID == tagToMatch && stxa.ArtworkID == aw.ArtworkID);
}

Answer 2

I think your problem has to do with the way C# handles captured variables in closures like your lambda expressions.

Your capturing the same variable tagToMatch. Please try this:

  foreach ( SearchTag tagToMatch in tagList )
  {
        SearchTag localTagToMatch = tagToMatch;
        awTable = awTable.Where( aw => aw.ArtworkName.Contains( localTagToMatch .SearchTagText )
                                               || db.SearchTag_x_Artworks.Where( stxa => stxa.SearchTagID == localTagToMatch .SearchTagID )
                                               .Select( stxa => stxa.ArtworkID ).Contains( aw.ArtworkID ) );
  }

Please read The Beauty of Closures by Jon Skeet.