none
Impact of using SqlMethod.Like() RRS feed

  • Question

  •  

    I've been working on a database issue using LINQ and wildcard searches.

    I found some chunks of code in answer to other peoples queries but had issues getting them to work. I encountered all sorts of issues, not the least of which is my beginners knowledge of LINQ.

    Although i noticed in the documentation that the description of the SqlMethods.Like() method included "This API supports the .NET Framework infrastructure and is not intended to be used directly from your code." and it should throw a "NotSupportedException always.", I gave it a go and it works perfectly.

    What are the issues of using this method in my code? Are they purely the risk they may not be supported in a later or final release or more serious?

    I really have to ask why something so useful has been tagged as not being for code use?

    Here's the code I included it in. Assuming in my eagerness to move past this issue I've done something completely insane, anyone got any ideas on a better/right way to do it?

    private void searchToolStripMenuItem_Click(object sender, EventArgs e)
            {
                int minQL = 0;
                int maxQL = 300;
                string l = "%";
                string[] cats = { "" };
                Search frmSearch = new Search();
               
                if (frmSearch.ShowDialog() == DialogResult.OK)
                {
                    minQL = frmSearch.GetMinQL();
                    maxQL = frmSearch.GetMaxQL();
                    l = "%" + frmSearch.GetName().Replace(" ", "%").Replace("*", "%") + "%";
                    cats = frmSearch.GetCats();
                }
               
                AODBDataContext db = new AODBDataContext();
               
                var fItems = from item in db.Items
                             where SqlMethods.Like(item.Name, l)
                             where cats.Contains(item.ItemType)
                             where item.QL >= minQL
                             where item.QL <= maxQL
                             select item;

                ItemList il = new ItemList(fItems);
                il.MdiParent = this;
                il.Show();
            }

    A few things that might be interesting for this thread. The table it is accessing has approximately 200K rows, and there is only a minor delay when it conducts the search, a second or two maximum.

    The problem i am trying to solve is to take 1 or more whole or partial word and search the table for names that contain those char sets.

    I'd also be interested in knowing how i can search for them in any order, rather than just the order they are entered, if anyone has some ideas on that?

    Cheers

    Wednesday, June 25, 2008 10:55 PM

Answers

  • There's actually no issue using the method in your code (the documentation phrase was chosen poorly.)  However, the method only works when used in a LINQ to SQL query.  You cannot call the method directly in code that is not going to be translated to SQL.  If you do, you'll get a NotImplementedException.

     

    Thursday, June 26, 2008 8:06 PM
    Moderator

All replies

  • Moving this question to the LINQ to SQL forum.

     

    Thanks,

    Diego

    Wednesday, June 25, 2008 11:25 PM
  • There's actually no issue using the method in your code (the documentation phrase was chosen poorly.)  However, the method only works when used in a LINQ to SQL query.  You cannot call the method directly in code that is not going to be translated to SQL.  If you do, you'll get a NotImplementedException.

     

    Thursday, June 26, 2008 8:06 PM
    Moderator
  • Thanks for the reply Matt. Perhaps if any MS people are reading this they can look at improving those documentation examples that could be a little misleading.

     

    Any ideas on the issue of searching the phrases in any order? Currently if you typed in "big little gun" it would translate to "%big%little%gun%".

     

    How could i get it to return results like "Little Big Gun" or "Little Gun Bigger Than the Last"? It would also be handy to be able to do an OR filter for any of the phrases?

    Friday, June 27, 2008 1:17 AM
  • Try using the string's .StartsWith, .EndsWith or .Contains methods rather than Like. In addition, these work with LINQ to Object queries where the like will not.

     

    Jim Wooley

    www.ThinqLinq.com

     

    Friday, June 27, 2008 8:46 PM
    Moderator
  •  

    >Perhaps if any MS people are reading this they can look at improving those

    >documentation examples that could be a little misleading.

     

    We understand, and have a bug tracking this issue. The documentation will be updated as part of SP1.

     

    Thanks,

    --Samir

    Saturday, June 28, 2008 1:30 AM
  • I know how to split the search string to an array etc. How do you consume the array or list within the LINQ query though? ie. can you enumerate a collection within a query someone how and apply a Like method to it? I won't know in advance how many words there may be in the search term. It could be one or five or more.

     

    Also, is there a way to do a conditional query? For example, depending on the value of a radio button group add the array elements as an AND condition or an OR one?

     

    Rather than have two or more linq queries I'd like to conditionally build them depending on the number of elements in the array and whether they want to do an AND or OR search?

    Sunday, June 29, 2008 11:39 PM
  • See if the following link offers any help for building the search criteria dynamically: http://www.thinqlinq.com/Default/Dynamically-extending-LINQ-queryies-without-building-expression-trees.aspx

     

    Jim Wooley

    www.ThinqLinq.com

    Monday, June 30, 2008 12:32 PM
    Moderator
  • Thanks Jim,

     

    That works great for the AND case, what about the OR case where I want to generate SQL like this in your example:

     

    SELECT [t0].[Id], [t0].[Title], [t0].[Description], [t0].[Author], [t0].[PublicationDate], [t0].[TimeStamp]
    FROM [dbo].[PostItems] AS [t0]
    WHERE ([t0].[Description] LIKE @p0) OR
                 ([t0].[Description] LIKE @p1) OR 
                 ([t0].[Description] LIKE @p2) OR 
                 ([t0].[Description] LIKE @p3)

     

     

    Cheers

    Brian

    Thursday, July 3, 2008 12:19 AM