Use the Subsonic.Select() ExecuteTypedList Method with String

Question

This is more a question regarding generics than subsonic:

Imagine if have the following code:

    List<int> result = 
      DB.Select(Product.Columns.Id)
        .From<Product>()
        .ExecuteTypedList<int>();

That works great and returns a generic list with the ids from my Product table.

But if I want to get a list of the ProductName:

    List<String> result = 
      DB.Select(Product.Columns.ProductName)
        .From<Product>()
        .ExecuteTypedList<String>();

it throws a compiler message (translated from german):

"string" has to be a non-abstract type with a public Constructor without parameter, in order to be used as a generic type or in the generic method "SubSonic.SqlQuery.ExecuteTypedList()" as param "T".

cause: String has no empty contructor:

int i = new int;       // works
String s = new String; // compiler error: "string" does not contain a constructor that takes '0' argument

If I use a List<Object> instead it works, but is there a more elegant way, where I can use List<String> ?

Update: List<Object> does not work. I indeed get a list of objects but that seem to be "empty" object that doesn't contain my ProductNames ( object.ToString() returns {Object} )

Answer 1

Well, I can think of this, but it is hardly more elegant:

List<string>result=DB.Select(Products.Columns.ProductName)
.From<Product>()
.ExecutTypedList<StringBuilder>()
.ConvertAll(sb=>sb.ToString());

Answer 2

It doesn't look like SubSonic has proper support for ExecuteTypedList with string values. The method that builds the list first looks for a SubSonic type and if there's no match checks to see if T is a value type. Since string isn't a value type it falls through to the last condition which tries to match property names to the column names returned. Since string doesn't have a property name that you can assign, it fails.

See: BuildTypedResult method: http://subsonicproject.googlecode.com/svn/trunk/SubSonic/SqlQuery/SqlQuery.cs

Answer 3

With a little bit dotnet magic it is possible without patching the subsonic code.

1. Create a new class SubsonicSqlQueryExtensionMethods and drop this code:

   using System;
   using System.Collections.Generic;
   using System.Linq;
   using System.Text;
   
   
   using SubSonic;
   
   
   namespace MyUtil.ExtensionMethods
   {
       public static class SubSonicSqlQueryExtensionMethods
       {
           public static List<String> ExecuteTypedList(this SqlQuery qry)
           {
               List<String> list = new List<String>();
               foreach (System.Data.DataRow row in qry.ExecuteDataSet().Tables[0].Rows)
               {
                    list.Add((String)row[0]);
               }
               return list;
           }
       }
   }
   

Now add a reference to MyUtil.ExtensionMethods to your class:

    using MyUtil.ExtensionMethods;

And finally this works:

    List<String> result = DB.Select(User.Columns.Name).From<User>().ExecuteTypedList();

Please note that the above extension method overloads the ExecuteTypedList() method with no type-argument (unfortunately this snippet requires dotnet 3.5, but for me it works)

Answer 4

I know I am late to this party but I found a neat way of 'tricking' this problem.

    List<String> result = 
  DB.Select()
    .From<Product>()
    .ExecuteTypedList<String>().Select(p => p.ProductName).ToList<String>();

This works like a charm for me.

Hope is helps someone somewhere, As I am sure you are far past the issue.