What does QueryAsync() return when requesting 1..N values from a single string column?

Question

With this code (with or without the ".ToList()"):

public async Task<List<String>> SelectDistinctGroupNames()
{
    var db = new SQLiteAsyncConnection(SQLitePath);
    var groupNames = await db.QueryAsync<List<string>>("SELECT DISTINCT GroupName FROM SOs_Locations");
    return groupNames.ToList();
}

...I get, "Cannot implicitly convert type 'System.Collections.Generic.List>' to 'System.Collections.Generic.List'"

This works:

public async Task<HashSet<String>> SelectDistinctGroupNames()
{
    var db = new SQLiteAsyncConnection(SQLitePath);
    var allLocations = await db.QueryAsync<SOs_Locations>("SELECT * FROM SOs_Locations ORDER BY GroupName");
    HashSet<string> hashsetGroupNames = null;
    foreach (var item in allLocations)
    {
        hashsetGroupNames.Add(item.GroupName);
    }
    return hashsetGroupNames;
}

...but seems wasteful (grabbing all the records, when all I want is distinct values from the GroupName column).

It seems to me that what is needed is a List or even a HashSet when replacing the "*" in the sql query with a "DISTINCT GroupName"

So what exactly is returned when a single column from several records is returned? IOW, what should be within the angle brackets of the call to QueryAsync<>() ?

I would think that this would work:

public async Task<List<String>> SelectDistinctGroupNames()
{
    var db = new SQLiteAsyncConnection(SQLitePath);
    List<string> allLocations = await db.QueryAsync<string>("SELECT DISTINCT GroupName FROM SOs_Locations ORDER BY GroupName");
    return allLocations;
}

...but with that I get, "'string' must be a non-abstract type with a public parameterless constructor in order to use it as parameter 'T' in the generic type or method 'SQLite.SQLiteAsyncConnection.QueryAsync(string, params object[])'"

I had a "string" above to correspond with <SOs_Locations> (not "List<SOs_Locations>") in the working version of the method. When I change it to "List<string>", I get, "Cannot implicitly convert type 'System.Collections.Generic.List<System.Collections.Generic.List' to 'System.Collections.Generic.List<string>'"

Answer 1

From the source:

public Task<List<T>> QueryAsync<T> (string sql, params object[] args)
        where T : new ()

So if you want List<string> as a result, you would need to pass string for T. As you noticed, string cannot satisfy a new() constraint (because it does not have a parameterless constructor), so that's not possible.

I took a look through the code and it looks to me like the new() constraint is unnecessary, so my first stop would be with the sqlite-net folks asking them to remove it (and verifying that T=string will work).

In the meantime, you should be able to create a type for the result of this query:

public sealed class DistinctGroupNamesResult
{
  public string GroupName { get; set; }
}

and use a brief transformation:

public async Task<List<String>> SelectDistinctGroupNames()
{
  var db = new SQLiteAsyncConnection(SQLitePath);
  var result = await db.QueryAsync<DistinctGroupNamesResult>("SELECT DISTINCT GroupName FROM SOs_Locations");
  return result.Select(x => x.GroupName).ToList();
}

There may be simpler solutions, e.g., using the higher-level LINQ provider (not sure if it supports DISTINCT, though). Or it may be possible to use SOs_Locations as the result type instead of DistinctGroupNamesResult.

Answer 2

Try this :

List<String> resultString = new List<string>(); 
var result = await db.QueryAsync<DistinctGroupNamesResult>("SELECT * FROM SOs_Locations");

foreach (string item in result.Select(x=> x.GroupName).Distinct())
{
    resultString.Add(item);
}

return resultString;