C#•3w ago

What's wrong with my LINQ expression?

Hi! I'm trying to get data using LINQ expressions below. I use Sqlite

public async Task OnGetAsync()
{
    var now = DateTimeOffset.Now;

    LockedOutUsers =
        await _userManager
            .Users
            .Where(u => u.LockoutEnd != null && u.LockoutEnd > now)
            .OrderBy(u=>u.Email)
            .ToListAsync();

    OtherUsers = await _userManager
        .Users.Where(u => u.LockoutEnd == null || u.LockoutEnd <= now)
        .OrderBy(u => u.Email)
        .ToListAsync();
}

public async Task OnGetAsync()
{
    var now = DateTimeOffset.Now;

    LockedOutUsers =
        await _userManager
            .Users
            .Where(u => u.LockoutEnd != null && u.LockoutEnd > now)
            .OrderBy(u=>u.Email)
            .ToListAsync();

    OtherUsers = await _userManager
        .Users.Where(u => u.LockoutEnd == null || u.LockoutEnd <= now)
        .OrderBy(u => u.Email)
        .ToListAsync();
}

But I'm getting exception

InvalidOperationException: The LINQ expression 'DbSet<IdentityUser>() .Where(i => i.LockoutEnd != null && i.LockoutEnd.Value > __now_0)' could not be translated.

InvalidOperationException: The LINQ expression 'DbSet<IdentityUser>() .Where(i => i.LockoutEnd != null && i.LockoutEnd.Value > __now_0)' could not be translated.

32 Replies

Angius•3w ago

Try splitting your where?

.Where(u => u.LockoutEnd != null && u.LockoutEnd > now)

.Where(u => u.LockoutEnd != null && u.LockoutEnd > now)

into

.Where(u => u.LockoutEnd != null)
.Where(u => u.LockoutEnd > now)

.Where(u => u.LockoutEnd != null)
.Where(u => u.LockoutEnd > now)

It should work from what I can tell, maybe a quirk of SQLite

SleepWellPupper•3w ago

The closure includes a DateTimeOffset variable, whose operator > the expression compiler can likely not translate to sql , it's a limitation of the system.

SleepWellPupper•3w ago

See: https://stackoverflow.com/questions/59616838/how-to-use-dates-in-where-clause-in-ef-core

Stack Overflow

How to Use Dates in Where Clause in EF Core?

I need to filter my queries by dates but I don't care in this case about time portion of it that is stored in SQL Database. I first tried to something like var now = DateTime.Now.Date; Where(x =&...

SleepWellPupper•2w ago

They're using a DateTime instance, so maybe try doing that instead of DateTimeOffset Notice that database-bound operations are limited in this manner, but process-bound (enumerated) operations are not. I.e. you can use arbitrary linq operations after you enumerate your query out of the database into memory via, e.g., ToListAsync().

SleepWellPupper•2w ago

You can implement custom functions as per this article: https://learn.microsoft.com/en-us/ef/core/querying/user-defined-function-mapping

User-defined function mapping - EF Core

Mapping user-defined functions to database functions

tera•2w ago

sqlite doesnt have a date and time types if you want to compare based on that you likely need to add a converter there's a few options DateTimeOffsetToBinaryConverter or something custom... but probably no need for custom its just sqlite limitation. it would work fine in e.g. postgres and no need for this add a converter and you can keep those operations db side

SleepWellPupper•2w ago

How will the conversion enable using the > operator though?

tera•2w ago

exactly it will

tera•2w ago

https://learn.microsoft.com/en-us/ef/core/providers/sqlite/limitations

SQLite Database Provider - Limitations - EF Core

Limitations of the Entity Framework Core SQLite database provider as compared to other providers

tera•2w ago

datetime solution is... 😬 i would ignore datetime ever exists in the language its a can of worms

SleepWellPupper•2w ago

I mean the convert to utc via converter seems fine to me

tera•2w ago

sure but rather keep DTO $datetimelie

MODiX•2w ago

please please please get into the habit of using DateTimeOffset instead of DateTime in your code everywhere you possibly can. DateTimeOffset contains the UTC offset with the data so is lossless. DateTime is very very weird in how it stores data. DateTime only knows UTC and "local" time. But your local time zone can change! Laptops easily move across state lines, even while an app is currently running. (So you are in California running an app, put some DateTime instance in memory, hop on a plane to New York, then resume your app 3 time zones ahead. What on earth will be contained within the DateTime instance?) But wait, this was a lie. DateTime actually has 3 ways it keeps track of time: UTC, local (which can change while an app is running), and unspecified (unknown, essentially). (Unknown = I don't know if it's UTC or local, the app will figure it out later and call ToUniversal or ToLocal before calling ToString or any comparison routine.) But wait, this was a lie. DateTime actually has a secret fourth way of storing data that's not exposed in the standard API surface! It's truly an abomination of a type. All of this nonsense is irrelevant if we just pretend DateTime doesn't exist and we instead use DateTimeOffset everywhere.

- GrabYourPitchforks (start: https://discord.com/channels/143867839282020352/143867839282020352/988353756108312607)

Alex•2w ago

I tried to fix it with DateTimeOffsetToBinaryConverter (onModelCreating) but it doesn't work. I can switch to postgresql, there's no problem

tera•2w ago

did you create migration after sqlite is perfectly fine.. if it suits your needs

Alex•2w ago

yes, I added code:

            if (Database.IsSqlite())
            {
                // SQLite does not have proper support for DateTimeOffset via Entity Framework Core, see the limitations
                // here: https://docs.microsoft.com/en-us/ef/core/providers/sqlite/limitations#query-limitations
                // To work around this, when the Sqlite database provider is used, all model properties of type DateTimeOffset
                // use the DateTimeOffsetToBinaryConverter
                // Based on: https://github.com/aspnet/EntityFrameworkCore/issues/10784#issuecomment-415769754
                // This only supports millisecond precision, but should be sufficient for most use cases.
                foreach (var entityType in builder.Model.GetEntityTypes())
                {
                    var properties = entityType.ClrType.GetProperties().Where(p => p.PropertyType == typeof(DateTimeOffset)
                        || p.PropertyType == typeof(DateTimeOffset?));
                    foreach (var property in properties)
                    {
                        builder
                            .Entity(entityType.Name)
                            .Property(property.Name)
                            .HasConversion(new DateTimeOffsetToBinaryConverter());
                    }
                }
            }

            if (Database.IsSqlite())
            {
                // SQLite does not have proper support for DateTimeOffset via Entity Framework Core, see the limitations
                // here: https://docs.microsoft.com/en-us/ef/core/providers/sqlite/limitations#query-limitations
                // To work around this, when the Sqlite database provider is used, all model properties of type DateTimeOffset
                // use the DateTimeOffsetToBinaryConverter
                // Based on: https://github.com/aspnet/EntityFrameworkCore/issues/10784#issuecomment-415769754
                // This only supports millisecond precision, but should be sufficient for most use cases.
                foreach (var entityType in builder.Model.GetEntityTypes())
                {
                    var properties = entityType.ClrType.GetProperties().Where(p => p.PropertyType == typeof(DateTimeOffset)
                        || p.PropertyType == typeof(DateTimeOffset?));
                    foreach (var property in properties)
                    {
                        builder
                            .Entity(entityType.Name)
                            .Property(property.Name)
                            .HasConversion(new DateTimeOffsetToBinaryConverter());
                    }
                }
            }

to the protected override void OnModelCreating(ModelBuilder builder) and created a migration and updated database

tera•2w ago

~~i just registered each one manually~~ 😂 but that looks ok? does the migration look fine?

Alex•2w ago

I think it's correct

tera•2w ago

actually 1sec do it like here https://learn.microsoft.com/en-us/ef/core/modeling/value-conversions?tabs=data-annotations#bulk-configuring-a-value-converter

protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
    configurationBuilder
        .Properties<DateTimeOffset>()
        .HaveConversion<DateTimeOffsetToBinaryConverter>();
}

protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
    configurationBuilder
        .Properties<DateTimeOffset>()
        .HaveConversion<DateTimeOffsetToBinaryConverter>();
}

configureconventions

Alex•2w ago

should I add it instead of previous code?

tera•2w ago

yea

Alex•2w ago

InvalidOperationException: The LINQ expression 'DbSet<IdentityUser>()
.Where(i => i.LockoutEnd != null && i.LockoutEnd > (DateTimeOffset?)DateTimeOffset.UtcNow)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

InvalidOperationException: The LINQ expression 'DbSet<IdentityUser>()
.Where(i => i.LockoutEnd != null && i.LockoutEnd > (DateTimeOffset?)DateTimeOffset.UtcNow)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

tera•2w ago

applied migrations?

Alex•2w ago

yes

tera•2w ago

try DateTimeOffset.UtcNow instead of .Now just to be sure actually show the code if you changed something it should be like this except UtcNow instead of Now dont do DateTimeOffset.UtcNow inside .where it cant translate to sql

Alex•2w ago

that's was the problem, I changed .Where(u => u.LockoutEnd != null && u.LockoutEnd > DateTimeOffset.UtcNow) to var utcNow = DateTimeOffset.UtcNow and .Where(u => u.LockoutEnd != null && u.LockoutEnd > utcNow) and it works fine

tera•2w ago

cool

Alex•2w ago

thank you!

tera•2w ago

np also $close

MODiX•2w ago

If you have no further questions, please use /close to mark the forum thread as answered

Alex•2w ago

i'll try to close it again later

tera•2w ago

😆 ok then bot broke

Gaming

Programming

What's wrong with my LINQ expression?