Is doing .ToList () more than once a bad idea?
I wrote the following function to query a SQLite DB in a Xamarin forms application. But, since I have to call .ToList () twice , I'm not very sure about this. Is this bad code? Any feedback will be highly appreciated.
public static List<string> GetAllLocationIds()
{
try
{
lock (CollisionLock)
{
//TableQuery<TResult> First .ToList() result
//List<string> Second .ToList() result
return Database.Table<Location>().ToList().Select(loc=>loc.LocationId).ToList();
}
}
catch (Exception ex)
{
Insights.Report(ex);
return null;
}
}
Performance. Selecting directly on Database.Table<Location>()
results in the following exception.
System.MissingMethodException: Default constructor not found for type
System.String at System.RuntimeType.CreateInstanceMono`
source to share
You simply cannot do Linq projections to string
types like c sqlite-net(-pcl)
, since it requires a parameterless constructor without parameters.
It follows that the "best way" mimics the "Linq projection" that I found when considering mobile memory and performance.
- Use custom class with only columns that need projected
- Use a SQL query with only the columns needed to match against this custom class (
where
filter in the select statement if needed) - Convert to custom type
Actual class of the table:
class Location
{
[PrimaryKey]
public int Column1 { get; set; }
public int Column2 { get; set; }
~~~
public string LocationId { get; set; }
}
Now create a new class that describes your "projection" needs, in this case I only need the column LocationId
.
Projection class
class SimpleList
{
public string LocationId { get; set; }
}
SQL Select (Select only columns mapped to projection class)
SQLiteConnection.Query<SimpleList>("select LocationId from [Location]")
Now that you have it List<SimpleList>
, you can convert it to List<string>
if you really need:
SQLiteConnection.Query<SimpleList>("select LocationId from [Location]").ConvertAll(x => x.LocationId);
Is it worth it? If you have a large number of rows and / or columns in a table and cannot use a lazy query and / or avoid the Linq projection ... IMHO yes ... Use a profiler to validate; -)
If you have a couple dozen lines? Perhaps not, but even then the amount of pace. the objects that get instanced shrink and for me it is a payoff on mobile.
source to share
Yes it is.
ABOUT
Database.Table<Location>().ToList()
You are materializing the entire table Location
. Then you choose LocationId
in memory.
Use instead:
Database.Table<Location>().Select(loc=>loc.LocationId).ToList();
Works directly on IQueryable<Location>
and materializes only LocationId
. Assuming that Table<Location>
IQueryable<Location>
.
source to share