C#•3y ago

Have to call .ToList() on DbSets for .First() or .Single() to work. Sqlite

I have a simple dependency on a database, the schema contains 2 entities and I'm having some simple problems with making it work. I have to call ToList on the DbSets of the entities to make Single/First match elements. Anyone got any idea on whats wrong? I add the context in Program.cs like this:

builder.Services.AddDbContext<SibusyContext>();

builder.Services.AddDbContext<SibusyContext>();

Context looks like this:

public class SibusyContext : DbContext
{
    private string DbPath { get; }

    public DbSet<Pipeline> Pipelines { get; set; }
    public DbSet<Job> Jobs { get; set; }

    public SibusyContext()
    {
        var folder = Environment.SpecialFolder.LocalApplicationData;
        var path = Environment.GetFolderPath(folder);
        Console.WriteLine(path);
        DbPath = Path.Join(path, "sibusy.db");
    }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseSqlite($"Data Source={DbPath}");
    }
}

public class SibusyContext : DbContext
{
    private string DbPath { get; }

    public DbSet<Pipeline> Pipelines { get; set; }
    public DbSet<Job> Jobs { get; set; }

    public SibusyContext()
    {
        var folder = Environment.SpecialFolder.LocalApplicationData;
        var path = Environment.GetFolderPath(folder);
        Console.WriteLine(path);
        DbPath = Path.Join(path, "sibusy.db");
    }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseSqlite($"Data Source={DbPath}");
    }
}

And I use it in a service like this:

public interface IJobService
{
    Task<string> ParseJob(CollectInput input, Guid pipelineId, CancellationToken ct);
}

public sealed class JobService : IJobService
{
    private readonly SibusyContext _sibusyContext;
    private readonly IMapper _mapper;

    public JobService(SibusyContext sibusyContext, IMapper mapper)
    {
        _sibusyContext = sibusyContext;
        _mapper = mapper;
    }
    
    
    public async Task<string> ParseJob(JobInput input, Guid pipelineId, CancellationToken cancellationToken)
    {
        var first = _sibusyContext.Pipelines.First();
        var isTrue = first.Id == pipelineId; // this is true
        var pipeline = _sibusyContext.Pipelines.FirstOrDefault(x => x.Id == pipelineId); // this is null
        var works = _sibusyContext.Pipelines.ToList().FirstOrDefault(x => x.Id == pipelineId); // this works
        if (pipeline is null)
            return "TODO";

        var job = _mapper.Map<Job>(input);

        job.PipelineId = pipelineId;
        await _sibusyContext.AddAsync(job, cancellationToken);
        await _sibusyContext.SaveChangesAsync(cancellationToken);

        return "TODO";
    }
}

public interface IJobService
{
    Task<string> ParseJob(CollectInput input, Guid pipelineId, CancellationToken ct);
}

public sealed class JobService : IJobService
{
    private readonly SibusyContext _sibusyContext;
    private readonly IMapper _mapper;

    public JobService(SibusyContext sibusyContext, IMapper mapper)
    {
        _sibusyContext = sibusyContext;
        _mapper = mapper;
    }
    
    
    public async Task<string> ParseJob(JobInput input, Guid pipelineId, CancellationToken cancellationToken)
    {
        var first = _sibusyContext.Pipelines.First();
        var isTrue = first.Id == pipelineId; // this is true
        var pipeline = _sibusyContext.Pipelines.FirstOrDefault(x => x.Id == pipelineId); // this is null
        var works = _sibusyContext.Pipelines.ToList().FirstOrDefault(x => x.Id == pipelineId); // this works
        if (pipeline is null)
            return "TODO";

        var job = _mapper.Map<Job>(input);

        job.PipelineId = pipelineId;
        await _sibusyContext.AddAsync(job, cancellationToken);
        await _sibusyContext.SaveChangesAsync(cancellationToken);

        return "TODO";
    }
}

4 Replies

Angius•3y ago

Probably not the issue, but you're using async fairly wrong all around. .Add() should be used rather than .AddAsync(), and calls to .First(), .ToList() etc should be .FirstAsync() and .ToListAsync()

.eaxOP•3y ago

Yeah I know, I have fiddled with the code a bit because of the problem, same thing happens if the method is completely synchronous.

Yawnder•3y ago

@Eax Can you show your Pipeline class? Nevermind, what I wanted to know is in the method signature. You're using Guid. That's your problem. It has nothing to do with ToList(). SQLite doesn't support Guid well, so when you're doing your filtering on the server side (with FirstOrDefault(x => x.Id ...)) it doesn't find anything, but if you ToList(), all the entities are brought client side, and there the Guid filter works fine. Similar issues https://github.com/dotnet/efcore/issues/10662 https://github.com/dotnet/efcore/issues/19651

.eaxOP•3y ago

Thanks, that is the issue, didn't know SQLite was that sensitive about guids.

Gaming

Programming

Have to call .ToList() on DbSets for .First() or .Single() to work. Sqlite