none
LIKE operator problem RRS feed

  • Question

  •  

    Hi

    I am working on a gaming website that has many game starting from numbers to alphabets like 2000-sudoku and wave-blaster etc. I wanna display all games list with prefix like [0-9](Games here) A (games here) and so on. I have 2 repeater controls 1 that binds the GamePrefix and for each row-bound I am applying a LINQ query on my allgames List<Game> to filter the games. Here is what I am doing

    protected void rptGamesPrefix_ItemDataBound(object sender, RepeaterItemEventArgs e)
      {
        // pattern = pattern.Replace("%", "");
        List<Game> games = allGames.Where(g => g.Title.StartsWith(pattern)).OrderBy(g => g.Title).ToList();
        rptGames.DataSource = games;
        rptGames.DataBind();
      }
    

    where pattern comes from GamePrefix table like [0-9], A, B. for simple Alphabets I get the filtered games but for [0-9] pattern I get none. Running this query in MSSQL query windows gives results. "SELECT * FROM [Games] WHERE [Title] LIKE '[0-9]%' ".so the problem is with this particular pattern for which i want games that start with any number. Is there any way to do this in LINQ?

    NOTE: Pattern values in DB are with % (A%, B%) but i Remove them in code.

     

    Monday, August 9, 2010 7:07 AM

Answers

  • The whole idea behind linq is to provide query syntax in the IDE - query independence is really a secondary goal and there is no guarantee that another provider you use will detect calls to string.StartsWith.

    Anyway, another alternative is just to OR them together, e.g.

    List<Game> games = allGames;

    .Where(g => g.Title.StartsWith("0") || g.Title.StartsWith("1") || g.Title.StartsWith("2") || g.Title.StartsWith("3") ... ).OrderBy(g => g.Title).ToList();

    [)amien

    • Marked as answer by Syed Emad Wednesday, August 11, 2010 5:47 AM
    Tuesday, August 10, 2010 3:02 PM
    Moderator

All replies

  • Well, not all commands have equivalent in linq, LIKE is one of them, now forget this is linq, how would you check if a string first char is a number

    http://msdn.microsoft.com/en-us/library/system.char.isnumber.aspx

    Regards

    Monday, August 9, 2010 1:23 PM
  • Hi,

    You may use SQLMethods.Like () in your LINQ To SQL queries and utilize your TSQL pattern.

    allGames.Where( g=>SqlMethods.Like( g.Title, "[0-9]%" ) );

    Regards,


    Syed Mehroz Alam
    My Blog | My Articles
    Monday, August 9, 2010 2:03 PM
  • Yeah but that is no linqy at all, if you want to you can always use raw sql or use a regex match expresion to build a pattern

    Regards

    Monday, August 9, 2010 2:20 PM
  • The SQLMethods class is part of LINQ to SQL and provides a number of special operations including LIKE. 

    [)amien

    Monday, August 9, 2010 2:49 PM
    Moderator
  • I'm not saying that is not part of the library or that is incorrect to use it, only that (this is copied from the page http://msdn.microsoft.com/en-us/library/bb355235.aspx )

    The SQL Server LIKE functionality cannot be exposed through translation of existing common language runtime (CLR) and .NET Framework constructs, and is unsupported outside of a LINQ to SQL context. The use of this method outside of LINQ to SQL will always throw an exception of type NotSupportedException.

    What does this mean?, this mean that this actually build a query to the server, so that the server interpret LIKE, so actually there is no support for LIKE as a command in linq syntax.

    Most if not all method in that class are like this, only supported in LINQ to SQL because is SQL who actually understand "Like" not LINQ.

    I like my queries to be independent of context, that is the whole idea behind linq, using SQLMethods.Like() is akin to using a sql command string.

    Regards

    Monday, August 9, 2010 3:08 PM
  • The whole idea behind linq is to provide query syntax in the IDE - query independence is really a secondary goal and there is no guarantee that another provider you use will detect calls to string.StartsWith.

    Anyway, another alternative is just to OR them together, e.g.

    List<Game> games = allGames;

    .Where(g => g.Title.StartsWith("0") || g.Title.StartsWith("1") || g.Title.StartsWith("2") || g.Title.StartsWith("3") ... ).OrderBy(g => g.Title).ToList();

    [)amien

    • Marked as answer by Syed Emad Wednesday, August 11, 2010 5:47 AM
    Tuesday, August 10, 2010 3:02 PM
    Moderator
  • Most linq queries will work regardless of the provider and I like that, so I tend to take advantage of it.

    Is a cool idea for me.

    You can use it or not, this is a sugestion at most and if you use basic operators that are part of the core libraries it will definitely work.

    We are somewhat getting sidetracked, is the matter resolved?, anything posted here would solve the OP problem, so let us know the result of our sugestions

    Regards

    • Marked as answer by Syed Emad Wednesday, August 11, 2010 5:46 AM
    • Unmarked as answer by Syed Emad Wednesday, August 11, 2010 5:47 AM
    Tuesday, August 10, 2010 4:21 PM