sqlite-net
sqlite-net copied to clipboard
praeclarum
Select of string value bombs out
My Data Model
public class FrameworkUserConfiguration
{
//used to store object in sqlite database
public Guid FrameworkUserUid { get; set; }
public byte[] Picture { get; set; }
public DateTime LastUpdate { get; set; }
public FrameworkUserDataType Type { get; set; }
public string Description { get; set; }
public string AudioFile { get; set; }
public bool? RepeatAudio { get; set; }
public bool ContainsPicture { get; set; }
[SQLite.PrimaryKey]
public string Identifier
{
get
{
return FrameworkUserUid.ToString() + "-" + Type;
}
set
{
}
}
}
public enum FrameworkUserDataType
{
ActualUser,
Admin,
User
}
A simple select bombs out telling me there's no parameterless constructor (after filling the table with some random data entries)
List<string> guids = conn.Table<FrameworkUserConfiguration>().Select(x => x.Identifier).ToList();
At first glance this appears to be an issue because public IEnumerator<T> GetEnumerator () is called for T being System.String - so it then creates a dummy table mapping and once we get to
var obj = Activator.CreateInstance(map.MappedType); things go wrong because System.String has no parameterless constructor.
For anyone still interested, if you want to select ALL values from the table, this works for me:
List<string> guids = conn.Query<FrameworkUserConfiguration>("SELECT Identifier FROM FrameworkUserConfiguration").Select(x => x.Identifier).ToList();
It aint pretty and still requires the creation of all those FrameworkUserConfiguration objects, but it only fetches the Identifier column from the database. If you have large byte[] columns you'll see quite some performance benefit.
I had added some mthods in SQLite.cs in SQLiteCommand:
public IEnumerable<T> ExcuteSingleQuery<T>()
{
if (_conn.Trace)
{
_conn.Tracer?.Invoke("Executing Query: " + this);
}
var stmt = Prepare();
try
{
if (SQLite3.ColumnCount(stmt) != 1)
{
throw new NotSupportedException("Column count error");
}
while (SQLite3.Step(stmt) == SQLite3.Result.Row)
{
var colType = SQLite3.ColumnType(stmt, 0);
var val = ReadCol(stmt, 0, colType, typeof(T));
yield return (T)val;
}
}
finally
{
Finalize(stmt);
}
}
in SQLiteConnection:
public List<T> SingleQuery<T>(string query, params object[] args)
{
var cmd = CreateCommand(query, args);
return cmd.ExcuteSingleQuery<T>().ToList();
}
so you can use it like this:
var singlequery = conn.SingleQuery<string>("select ColumnName from TableName");
Hope for help.
Yea...I too have the same problem....This is really basic functionality that is missing
@kinderry , is your piece of code merged with this repo or we need to add this method manually into the class?
Upping this topic as it seems to be a no brainer, any progress on that? Right now I'm using:
myQueryResult = _databaseAsyncConnection.QueryAsync<T>(query).Result;
With T being a class manually created that contains requested properties as fields, and "query" the query string looking to get those properties from a table. Ex:
public class NameWithoutImportance // will be used as T, class can be private instead too
{
public String Description { get; set; }
public DateTime Birthdate { get; set; }
}
string query = "SELECT Description, Birthdate FROM TableName";
It works very well, and definitely saves on performance against getting the full object for the table, but it requires to write the class for T even if you want just one property, which in that last case may not come as a straightforward solution.
A workaround in this situation would be to internally dynamically create a class (through reflection maybe?) around a single attribute name (or a list of attributes names) and to send back a List of generic objects being those typed properties (type resolution via polymorphysm)?
Please @praeclarum include the code from @kinderry above
https://github.com/praeclarum/sqlite-net/issues/159#issuecomment-465093954
@praeclarum is there any reason the code above is not being merged? Are you waiting for a PR? It appears that many people encounter this little problem and there are also a lot of duplicate issues on this, which could also be closed with a small code addition that seems to have no negative side-effects from what I can tell.
