none
Sorting in a LINQ to SQL query with a String-type column name provided RRS feed

  • Question

  • Hi,

    please can you help me with some advice?

    Here is a piece of code as a sample scenario:

    dim SortAttribute as String = "address" 
    dim SortDirection as String = "ASC" 
     
    from persons in dc.persons _  
      select new ResultSet with { _  
        .name = persons.name, _  
        .birth_date = persons.birth_date, _  
        .address = persons.address _  
      }  
     

    The important part is that I don't want to sort the resultset after the query execution, but instead I'd like to have the SQL server do the sorting.
    Tuesday, February 3, 2009 7:59 PM

Answers

  • hmmm, the more I think about it, I don't think this will work because it would try to do it in SQL instead, and since there is no reflection in SQL, there's no way to do something like that with just a string, so I'm thinking you might just need to do a switch.
    Daniel - http://webs.neumont.edu/dstafford
    • Marked as answer by vmware Wednesday, February 11, 2009 9:53 AM
    Monday, February 9, 2009 4:01 PM

All replies

  • I'm not all that great at VB, but I'm pretty sure this maps pretty much right over from C#:
    Dim sortAttribute as String = "address"  
    Dim sortDirection as String = "ASC"  
      
    Dim people = from person in dc.persons 
                 orderby person.GetType().GetProperty(sortAttribute).GetValue(person, null) 
                 select new { Name = person.name, BirthDate = person.birth_date, Address = person.Address }; 
    As for the sort direction, you'll just have to do an if statement to check it.

    Daniel - http://webs.neumont.edu/dstafford
    Wednesday, February 4, 2009 4:37 AM
  • This doesn't work.

    The error message I get is
    Cannot order by type 'System.Object'.
    Monday, February 9, 2009 2:37 PM
  • My exact translation to VB.NET was the following:

    From persons in dc.persons _ 
      Order By persons.GetType().GetProperty(sortAttribute).GetValue(persons, Nothing) _ 
      Select persons 

    Monday, February 9, 2009 2:40 PM
  • You'll need to cast to the appropriate type, so if address is a string, you'll have to either cast to a string or use .ToString().
    Daniel - http://webs.neumont.edu/dstafford
    Monday, February 9, 2009 2:43 PM
  • Hm, I don't understand which part of the code I should cast.

    In VB.NET I have Option Strict turned on.

    I have also tried this code with C#, and I get the same error message.
    Monday, February 9, 2009 2:53 PM
  • using System.Linq; 
     
    namespace ConsoleApplication1
      class Program 
      { 
        static void Main(string[] args) 
        { 
          string sortAttribute = "ProductName"
          using (DataClasses1DataContext dc = new DataClasses1DataContext()) 
          { 
            int result = ( 
              from products in dc.Products 
                orderby products.GetType().GetProperty(sortAttribute).GetValue(products, null) 
                select products).Count(); 
          } 
        } 
      } 

    Here is the full code that results in the runtime error.
    Monday, February 9, 2009 3:14 PM
  • You need to cast the result of GetValue() to the type of that property, so in this example, it would be:
    namespace ConsoleApplication1
      class Program 
      { 
        static void Main(string[] args) 
        { 
          string sortAttribute = "ProductName"
          using (DataClasses1DataContext dc = new DataClasses1DataContext()) 
          { 
            int result = ( 
              from products in dc.Products 
                orderby (string)products.GetType().GetProperty(sortAttribute).GetValue(products, null) 
                select products).Count(); 
          } 
        } 
      } 
    }


    Daniel - http://webs.neumont.edu/dstafford
    Monday, February 9, 2009 3:31 PM
  • In this new case the runtime error message I get is:

    The argument 'value' was the wrong type. Expected 'System.String'. Actual 'System.Object'.


    (Additional information is that the ProductName field in the database really is a string,
    though I don't know whether or not this piece of information is needed.)
    Monday, February 9, 2009 3:43 PM
  • hmmm, the more I think about it, I don't think this will work because it would try to do it in SQL instead, and since there is no reflection in SQL, there's no way to do something like that with just a string, so I'm thinking you might just need to do a switch.
    Daniel - http://webs.neumont.edu/dstafford
    • Marked as answer by vmware Wednesday, February 11, 2009 9:53 AM
    Monday, February 9, 2009 4:01 PM
  • That will be it. Nor do I think this would work without a "Select Case" operator.

    The reason why I asked this question was that an ObjectSource sends the sorted column name in string format to the business logic layer.
    Monday, February 9, 2009 7:32 PM