Sqlite-net: Select result to Array of strings?

[arturdev]

Is it possible to do Select query and get result as list of Strings (i.e. to not map to object)?
Like:

C# String query = "select columnName from table"; List<String> result = DatabaseManager.Database.Query<???>(query);

[alainjlavoie]

I don't think so. You have to use your own class.

[nurchi]

Keep in mind that List<String> implies that you have a list (rows) with only one column and that column is of type String. If this is what you want, make sure your query returns exactly one column ot the corresponding type (String in this case). If not, consider List<object[]> or List<string[]> (then make sure that your query returns nothing but strings.

It is definitely possible, but you'll end up doing more work messing with values at runtime using reflection, which is a lot more prone to errors.

If relevant, here is a partial example:

String query = "select columnName from table";
using (var db=new SQLiteConnection("path to DB"))
{
    var query=db.Query(query);

    // Now mess with reflection, prone to errors
    // I have no compiler handy, so I will stop here
}

Secondly, (assuming this was easily achievable) by doing using it way, you kind of defy the whole purpose of this project. If this is how you want to do it, just grab the SQLite ADO.NET driver from the sqlite homepage and use it like you would any other database (Ms Access, Ms SQL, etc).

Hope this clears things up

[praeclarum]

Selecting scalars is not supported, but would be a great feature.

[bill2004158]

any update about this feature?

[fastlater]

@bill2004158 waiting for the same. Did you find a solution? If the type is not a class. It does return wrong values when requesting only one column.

[alainjlavoie]

Don't hold your breath

On Mon, Nov 19, 2018, 4:31 AM fastlater <[email protected] wrote:

@bill2004158 https://github.com/bill2004158 waiting for the same. Did
you find a solution? If the type is not the class, it does return wrong
values when requesting only one column

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/praeclarum/sqlite-net/issues/306#issuecomment-439827578,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AItzvwgXXlMALk3aYhH7OcowQoret7Roks5uwnqAgaJpZM4CPeKZ
.

[teekay]

This is how I solved it. Is it hacky?

Given that I have a table named "Tracks" and a corresponding model named Track that has a string property Genre, I do this to retrieve a list of all genres in my track collection, with Connection being an instance of SQLiteConnection:

Connection.Query(Connection.GetMapping<Track>(), "select distinct Genre from Track").Select(t => ((Track) t).Genre)

I think I can safely cast the objects returned by the query to my Track class since the first argument of the Query method provides the table mapping - am I right?

[RSF-Deus]

Oh my god so that's why I have been loosing two entire days, I was almost sure that it was possible to execute scalar and return a collection of strings, or at least a generic object, the feature just simply isn't there actually, it would be absolutely amazing if it existed

This is how I solved it. Is it hacky?

Given that I have a table named "Tracks" and a corresponding model named Track that has a string property Genre, I do this to retrieve a list of all genres in my track collection, with Connection being an instance of SQLiteConnection:

Connection.Query(Connection.GetMapping<Track>(), "select distinct Genre from Track").Select(t => ((Track) t).Genre)

I think I can safely cast the objects returned by the query to my Track class since the first argument of the Query method provides the table mapping - am I right?

I'm not sure I get how you did that, if I'm understanding you correctly, you could pass a class that has a single parameter string in it and use it as a TableMapping right ? So you would retrieve a collection of instanciated objects from that class that has all string values in each instance of the collection ? I'm definitely gonna try that ^^

EDIT : sadly, it doesn't work.
The only way to make it work in my case, was to use the mother class of my objects as type for Query<> and then extract the parameter via Linq, which works great, but only for the mother properties, since Query<> expects a known type and linq won't select unknown properties anyways

[kinderry]

I have pulled this,but no merged.You can find my answer in #159.

[RSF-Deus]

My bad, I forgot what I was dealing with, I was trying to retrieve only the IDs with a custom object

    public class ColumnData
    {
        public String Id;
    }

but the object needs to be formatted in the SQLite.net standard, which is :

    public class ColumnData
    {
        [PrimaryKey]
        public String Id { get; set; }
    }

and now it works fine :)