Question: Is performing .ToList() more than once is a bad idea?

Question

Is performing .ToList() more than once is a bad idea?

Answers 2
Added at 2017-06-04 04:06
Tags
Question

I've written the below function to query the SQLite DB in my Xamarin forms app. But, since I have to call .ToList() twice, I'm not very confident about it. 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;
        }
    }

Performing .Select directly on Database.Table<Location>() results in the following exception.`

System.MissingMethodException: Default constructor not found for type 
System.String at System.RuntimeType.CreateInstanceMono`
Answers to

Is performing .ToList() more than once is a bad idea?

nr: #1 dodano: 2017-06-04 05:06

Yes it is.

On

Database.Table<Location>().ToList()

You are materializing all of Table Location. Then you are only selecting the LocationId in Memory.

Instead use:

Database.Table<Location>().Select(loc=>loc.LocationId).ToList();

Which is working directly on IQueryable<Location> and only materializes the LocationId. Assuming Table<Location> is IQueryable<Location>.

nr: #2 dodano: 2017-06-04 06:06

You just can not do Linq projections to string types like that with sqlite-net(-pcl) as it needs a default parameterless constructor.

What follows is the "best way" simulate a "Linq projection" that I have found when Mobile memory and performance is considered.

  1. Use a custom Class with only the columns that need projected
  2. Use a SQL query with only the columns needed to map to that custom class (where filter in the select statement if needed)
  3. Convert to custom type

Actual Table Class:

class Location
{
    [PrimaryKey]
    public int Column1 { get; set; }
    public int Column2 { get; set; }
    ~~~
    public string LocationId { get; set; }
}

Now make a new class that describes your "projection" needs, in this case I only want the LocationId column.

Projection Class

class SimpleList
{
    public string LocationId { get; set; }
}

SQL Select (selecting only the columns that map to the projection class)

SQLiteConnection.Query<SimpleList>("select LocationId from [Location]")

Now you have a List<SimpleList>, you can convert it to a List<string> if you really need to:

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 your table and cannot use a deferred query and/or avoid a Linq projection... IMHO yes... Use the profiler to confirm ;-)

If you have a couple dozen rows? Maybe not, but even then the number of temp. objects that get instanced is reduced, and for me that is a win on mobile.

Source Show
◀ Wstecz