SQLite-net TableQuery<T>.Select() poor performance

Question

Consider the full code block further below, specifically this part - the object initialization (is that what you'd call it?):

new LocalFileInfo() {
  IsFavorite = p.IsFavorite,
  ...
  WhenCrawled = p.WhenCrawled
}

Is there anyway to abstract away this code into something such as a method that I can reuse, instead of copy-n-pasting the object initialization code into every query? My strong preference is for the best performing code rather than the easiest to maintain (but obviously easy to maintain would be desirable).

public static List<LocalFileInfo> RecentlyCrawledFiles(int take)
{
    if (take < 1) take = 1;

    List<LocalFileInfo> list = new List<LocalFileInfo>();

        using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), sqliteDb))
        {
            // works but is slower.
            // list = (from p in conn.Table<LocalFileInfo>() select p).OrderBy(f => f.WhenCrawled).Take(take).ToList();

            list = (from p in conn.Table<LocalFileInfo>() select new LocalFileInfo() {
                IsFavorite = p.IsFavorite,
                LastModified = p.LastModified,
                Name = p.Name,
                ParentFolder = p.ParentFolder,
                Path = p.Path,
                Size = p.Size,
                SourceId = p.SourceId,
                SourceName = p.SourceName,
                SourceType = p.SourceType,
                WhenCrawled = p.WhenCrawled
            })
            .OrderByDescending(f => f.WhenCrawled)
            .Take(take)
            .ToList();
        };

    return list;
}

Answer 1

I've taken a look at the source code of the master branch in SQLite-net

That library appears to ignore your projections so I don't think having an expression for them will help in any way

See for example in SqlLite.cs the TableQuery<T> class

    public IEnumerator<T> GetEnumerator ()
    {
        if (!_deferred)
            return GenerateCommand("*").ExecuteQuery<T>().GetEnumerator();

        return GenerateCommand("*").ExecuteDeferredQuery<T>().GetEnumerator();
    }

This GetEnumerator() method is used every time you do ToList() or foreach (var item in query).

The GenerateCommand() will build the SQL, and it "theoretically" supports a string parameter selectList, but the library will never use this parameter

Also, when you do a Select(Expression) the libary stores a _selector private property but will never use it in the call to GenerateCommand(), or any other call

This issue has been reported

So, I think your best bet regarding performance is this:

var list = conn.Table<LocalFileInfo>()
    .OrderByDescending(f => f.WhenCrawled)
    .Take(take)
    .ToList();

You can do a Select<T>() after this, but the library has already loaded the whole entity list along with every single property. Though, maybe, selecting after the fact can help you with GC if you are keeping those entities around (that is, projection after ToList() won't give you any immediate gains).

Or use the Query<T>() from the connection object... which will lead you back into the SQL land.

Alternatively, wait for Entity Framework Core to support Xamarin

There's a mention in the Roadmap:

Xamarin works in some scenarios but has not been fully tested as a supported scenario.

Sorry but that's the best I can come up with for the moment :(

EDIT: In fact, I believe that if you do a Select(p => p.IsFavorite) the library will fail miserably since it can't map your projection (not bashing the library, just a heads up)