C#•6mo 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•6mo 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•6mo 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•6mo 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•6mo 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•6mo 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•6mo 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•6mo ago

How will the conversion enable using the > operator though?

tera•6mo ago

exactly it will

tera•6mo 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•6mo ago

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

SleepWellPupper•6mo ago

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

tera•6mo ago

sure but rather keep DTO $datetimelie

MODiX•6mo 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)

AlexOP•6mo ago

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

tera•6mo ago

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

AlexOP•6mo 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•6mo ago

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

AlexOP•6mo ago

I think it's correct

tera•6mo 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

AlexOP•6mo ago

should I add it instead of previous code?

tera•6mo ago

yea

AlexOP•6mo 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•6mo ago

applied migrations?

AlexOP•6mo ago

yes

tera•6mo 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

AlexOP•6mo 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•6mo ago

cool

AlexOP•6mo ago

thank you!

tera•6mo ago

np also $close

MODiX•6mo ago

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

AlexOP•6mo ago

i'll try to close it again later

tera•6mo ago

😆 ok then bot broke

Gaming

Programming

What's wrong with my LINQ expression?