EF core fetch string as enum type-CodePudding

I'm running a large SELECT with ordes of magnitude of 100.000 rows on an MYSQL database. The table i'm fetching has a column called type which has 4 different string values.

|Id|Type  |MetaKey|MetaValue|
|===========================|
|01|'line'|'AKey' |AValue   |
|02|'line'|'AKey1'|AValue   |
|03|'Ship'|'AKey2'|AValue   |
|04|'Fee' |'AKey3'|AValue   |
|05|'Ship'|'AKey4'|AValue   |

Currently the main bottleneck seems to be network transit so optimally i'd like to cast these 4 different string values to an int enum (or something small) so that i save memory for transit.

I gues MYSQL CASE would allow to do this in SQL.

Is this possible with EF core? What would the LINQ syntax look like?

To reiterate - i want the conversion to done in the database, not in the C# client.

EDIT: Apologies - i got confused, this is an MYSQL database not an MSSQL database.

CodePudding user response：

Your query should looks like this:

public enum MyEnum
{
    Unknown = 0,
    Line = 1,
    Ship = 2,
    Fee = 3
}

var query = 
    from t in ctx.Table
    select new 
    {
        c.Id,
        c.MetaKey,
        c.MetaValue,
        Type = (MyEnum)(
            c.Type == "line" ? 1 :
            c.Type == "Ship" ? 2 :
            c.Type == "Fee" ? 3 : 0)
    };

EF Core generates optimal expression for returning integer:

CAST(CASE
    WHEN [e].[Type] = N'line' THEN 1
    WHEN [e].[Type] = N'Ship' THEN 2
    WHEN [e].[Type] = N'Fee' THEN 3
    ELSE 0
END AS int)