What does QueryAsync () return when querying 1..N values ​​from a single column in a row?

Question

With this code (with or without .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: "It is not possible to implicitly convert the type" System.Collections.Generic.List> "to" System.Collections.Generic.List "

It 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 it seems wasteful (capturing all the records when all I need is different values ​​from the GroupName column).

It seems to me that you need a List or even a HashSet when replacing "*" in the sql query "DISTINCT GroupName"

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

I would think 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 this I get: "String" should not be an abstract type with an open constructor without parameters, to use it as a parameter "T" in the generic type or method "SQLite.SQLiteAsyncConnection.QueryAsync (string, params object []) '"

I had a "string" above to match <SOs_Locations > (not "List <SOs_Locations >") in the working version of the method. When I change it to "List <string >", I get: "It is not possible to implicitly convert the type" System.Collections.Generic.List <System.Collections.Generic.List "to" System.Collections.Generic.List <string > " "

Answer 1

From source :

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

So, if you want a List<string> as a result, you need to pass a string for T As you noticed, string cannot satisfy the new() constraint (because it does not have a constructor without parameters), so this is not possible.

I looked through the code, and it seems to me that the new() constraint is not required, so my first stop will be that sqlite-net users will be asked to remove them (and checking 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 short conversion:

 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, for example, using a higher level LINQ provider (not sure if it supports DISTINCT ). Or you can use SOs_Locations as the result type instead of DistinctGroupNamesResult .

Answer 2

  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;