Linq to entity enum not comparing-CodePudding

Here is my Enum:

public enum AdvertStatus
{
   Active,
   Archived
}

And my entity type:

public record Advertisement
{
...
    public AdvertStatus Status { get; set; }
...
}

In database it's stored as int, Database is Postgree

When I try to compare it like so:

data = data.Where(x => x.Status == searchValues.Status);

Entity Framework throws an exception sayng:

.Status == (int)__searchValues_Status_3)' 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'.

I tried solutions from this question: LINQ TO ENTITY cannot compare to enumeration types but it did't work.

EDIT 1:
data is database table context IQueryable<AdvertisementDTO>
searchValues.Status is type of AdvertStatus from search filter

CodePudding user response：

The issue may be higher up in your Linq query, such as you are attempting to project with a Select or ProjectTo before filtering. For simple types like int/string this should work, but depending on how your DTO is declared you might be introducing problems for mpgsql.

For instance if your query is something like:

var query = _context.Advertisements
    .Select(x => new AdvertisementDTO
    {
        // populate DTO
    }).Where(x => x.Status == searchValues.Status)
    // ....

then npgsql may be having issues attempting to resolve the types between what is in the DTO and the enumeration in your searchValues. From what the exception detail looks like, npgsql is trying to be "safe" with the enum and casting to intbut feeding that to PostgreSQL that results in invalid SQL. I did some quick checks and the DTO would need to be using the same Enum type (C# complains if the DTO cast the value to int, cannot use == between AdvertStatus and int fortunately) The project may have something like a value converter or other hook trying to translate enumerations which is getting brought into the mix and gunking up the works.

Try performing the Where conditions prior to projection:

var query = _context.Advertisements
    .Where(x => x.Status == searchValues.Status)
    .Select(x => new AdvertisementDTO
    {
        // populate DTO
    })
    // ....

If the data value is stored as an Int then this should work out of the box. npgsql does support mapping to string (which would require a ValueConverter) as well as database declared enumerations. (https://www.postgresql.org/docs/current/datatype-enum.html) However, Int columns should work fine /w enums.

If that doesn't work, I'd try with a new DbContext instance pointed at the DB and a simple entity with that Enum to load a row from that table to eliminate whether npgsql is translating the enum correctly, just to eliminate any possible converters or other code that the main DbContext/models/DTOs may be contributing.

CodePudding user response：

It was all my mistake in higher repo Select projection.
Thanks you all for help. Cheers.