using LinqToDB;

using LinqToDB.AspNet.Logging;

using LinqToDB.Data;

using LinqToDB.DataProvider.SQLite;

using LinqToDB.Mapping;

using Microsoft.Data.Sqlite;

using Microsoft.Extensions.DependencyInjection;

using Microsoft.Extensions.Logging;

using System;

using System.Threading;

using System.Collections.Generic;

using System.Linq;

using System.Data;

​

using System.Threading.Tasks;

public class Program

{

    public static async Task Main()

    {

        Console.WriteLine("Hello World");

        // https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/custom-versions?tabs=netcore-cli

        // Microsoft.Data.Sqlite is built on top of SQLitePCLRaw.

        // Ensuring the SQLite provider is "e_sqlite3" so we get a consistent version on all platforms

        SQLitePCL.raw.SetProvider(new SQLitePCL.SQLite3Provider_e_sqlite3());

        using var sp = new ServiceCollection()

            .AddLogging(x => x.AddConsole())

            .AddSingleton(serviceProvider => {

                var connection = new SqliteConnection("Data Source=dbname;Mode=Memory;Cache=Shared");

                var provider = SQLiteTools.GetDataProvider();

                var options = new DataOptions()

                    .UseConnection(provider, connection)

                    .UseLoggerFactory(serviceProvider.GetService<ILoggerFactory>())

                    ;

                return new DataConnection(options);

            })

            .BuildServiceProvider();

        using var connection = sp.GetService<DataConnection>();

        await connection.CreateTableAsync<TestTable>();

        var myDecimal = 5m;

        var rows = new List<TestTable>();

        foreach(var i in Enumerable.Range(1, 40))

        {

            rows.Add(

            new TestTable() 

            {

                Id = i,

                DecimalProp = i + 0.1m,

            });

        }

        await connection.BulkCopyAsync(rows);

        Console.WriteLine("1");

        var query = connection.GetTable<TestTable>()

            .Where(x => x.DecimalProp > myDecimal);

        Console.WriteLine("2");

        var (result, totalCount) = await QueryPaginator.Paginate<TestTable>(query, 0, 0, CancellationToken.None);

        Console.WriteLine("final");

        Console.WriteLine("No crash? Found " + result.Count() + ", total: "+ totalCount);

    }

}

​

[Table("testTable")]

public class TestTable

{

    [Column]

    public int Id { get; set; }

    [Column]

    public decimal DecimalProp { get; set; }

}

​

public static class QueryPaginator

{

    private record Envelope<T>(int TotalCount, T Data);

​

    public static async Task<(List<T> Data, int TotalCount)> Paginate<T>(

        IQueryable<T> query,

        int skip,

        int take,

        CancellationToken cancellationToken)

    {

        int? totalCount = null;

        var result = new List<T>(take);

​

        if (cancellationToken.IsCancellationRequested)

        {

            throw new TaskCanceledException();

        }

​

        Console.WriteLine("3");

        var queryWithCount = query

            .Select(data => new Envelope<T>(Sql.Ext.Count().Over().ToValue(), data))

            .Skip(skip)

            .Take(take)

            .AsAsyncEnumerable()

            .WithCancellation(cancellationToken);

​

        await foreach (var row in queryWithCount)

        {

            totalCount ??= row.TotalCount;

​

            result.Add(row.Data);

        }

​

        Console.WriteLine("4");

        return (result, totalCount ?? 0);

    }

}