none
Why does this Linq2Sql create some weird sql syntax? RRS feed

  • Question

  • Hi folks,

        I'm trying to do a Full-Text Search in my linq. Yes, i know it's not supported, so I was trying to do it with a poor man's implementation using the CONTAINS keyword.

     

    When i run this code, the values in the sql are the same! So if i pass in the string 'Best Restaurants' and i create a dynamic query on that string, split by space .. i get the following weird result..

     

    Code Snippet

    IQueryable<MyTable> query;

    string keywords = "Best Restaurants";

     

    using (MyDatabaseContext db = new MyDatabaseContext())

    {

        query = db.MyTable;

     

        foreach (string word in keywords.Split(' '))

        {

            query = query.Where(q => q.Subject.Contains(word));

        }

     

        var x = (from q in query

                select q).ToList();

    }

     

     

     

     

    that compiles fine .. but the sql syntax is..

    Code Snippet

     

    SELECT [t0].[IdMyTable], [t0].[Subject]

    FROM [dbo].[MyTable] AS [t0]
    WHERE ([t0].[Subject] LIKE '%restaurant%') AND ([t0].[Subject] LIKE '%restaurant%')

     

     

    Now please take note of the HIGHLIGHTED keywords... notice how they are the same? the word 'Best' is not in there ... Sad

     

    also .. doing a %xx% does a table scan .. which is *** ugly .. so anyone have any other recommendations?

     

    I don't mind passing the split string to a stored proc via linq2sql ... but don't know how to do that, nor the sql for the stored proc.

     

    or do i need to create a dynamic string and use linq2sql to exec that?

     

    cheers Smile

    Monday, July 7, 2008 7:32 AM

Answers

  • If you change your inner loop to store the enumerated variable in a temporary one that goes out of scope it will work:

     

    foreach (string word in keywords.Split(' '))

        {

            string newWord = word;

            query = query.Where(q => q.Subject.Contains(newWord));

        }

     

    This is not actually a LINQ to SQL specific issue but is caused by the way lambda functions take a reference to the variable and defer execution. As the foreach variable does not go out of scope but is reused you end up with the same value for each.

     

    [)amien

    Monday, July 7, 2008 11:49 PM
    Moderator

All replies

  • If you change your inner loop to store the enumerated variable in a temporary one that goes out of scope it will work:

     

    foreach (string word in keywords.Split(' '))

        {

            string newWord = word;

            query = query.Where(q => q.Subject.Contains(newWord));

        }

     

    This is not actually a LINQ to SQL specific issue but is caused by the way lambda functions take a reference to the variable and defer execution. As the foreach variable does not go out of scope but is reused you end up with the same value for each.

     

    [)amien

    Monday, July 7, 2008 11:49 PM
    Moderator
  • Thanks heaps [)mien Smile Works. I was wondering if there was some funky referencing going on... cause it was always picking up the last reference.

     

    cheers mate!

    Tuesday, July 8, 2008 12:25 AM
  • What about:

    IQueryable<MyTable> query;
    string keywords = "Best Restaurants";
    using (MyDatabaseContext db = new MyDatabaseContext())
    {
        string[] words = keywords.Split(' ');
        query = db.MyTable.Where( q => words.Contains( q.Subject ) );
    }


    Best regards,
    Marcus

    Friday, June 12, 2009 10:50 AM