none
Sorting with a function call that has no supported translation to SQL RRS feed

  • Question

  • I am doing a query that I'd like to do a 2 level sort on that contains a call to .TrimStart() on one of the table members but I'm getting a "Method 'System.String TrimStart(Char[])' has no supported translation to SQL" error. I'm relatively new to LINQ so go easy on me :)

    So here's ideally what I'm trying to accomplish. I've got a simple table of categories and names, both strings.

    var data = from c in dataContext.Name_tables
    orderby c.category, c.name.TrimStart('.', ':')
    select new {c.category, c.name};

    obviously the TrimStart is causing the error since it won't translate to SQL. So I tried commenting out the orderby clause and converting it to a list with .ToList(), but it looks like I can't do a 2 level sort on a list using the .Order method on the list.

    Can someone give me a hint how I might accomplish this? Essentially I need to sort on name but the sorting must ignore certain characters at the beginning of the name string. So for example a name of ".Joe" would be sorted near "John" and not near the top of the sort order because of the '.' character (for that category).

    Monday, April 6, 2009 2:54 AM

Answers

  • var data = from co in
                (from c in dataContext.Name_tables
                 select new {c.category, c.name}
                ).ToList()
               orderby co.category, co.name.TrimStart('.', ':')
               select co;

     


    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com
    • Marked as answer by esassaman Monday, April 6, 2009 11:48 PM
    Monday, April 6, 2009 10:20 AM
    Answerer

All replies

  • var data = from co in
                (from c in dataContext.Name_tables
                 select new {c.category, c.name}
                ).ToList()
               orderby co.category, co.name.TrimStart('.', ':')
               select co;

     


    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com
    • Marked as answer by esassaman Monday, April 6, 2009 11:48 PM
    Monday, April 6, 2009 10:20 AM
    Answerer
  • Excellent, thank you. Works great. Help me understand what is going on with this solution. It looks like the inner query is being converted to a list type (which forces the SQL query to execute and get the data from the database, right?), and the outer query, now that it's working on a list type, can call .TrimStart on the result members, is that right? After the .ToList() call, where is the data? Is it in local memory? So the outer query is simply working on the in-memory list type to do the query, and not going to the SQL store, is that right?
    Monday, April 6, 2009 11:56 PM
  • Excellent, thank you. Works great. Help me understand what is going on with this solution. It looks like the inner query is being converted to a list type (which forces the SQL query to execute and get the data from the database, right?), and the outer query, now that it's working on a list type, can call .TrimStart on the result members, is that right? After the .ToList() call, where is the data? Is it in local memory? So the outer query is simply working on the in-memory list type to do the query, and not going to the SQL store, is that right?

    Correct. ToList forces the inner L2S query to run and materializes the results to a list (in local memory). The outer query works against that list using Linq-to-Objects.
    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com
    Tuesday, April 7, 2009 12:59 AM
    Answerer