Devart Forums

Posted: **Tue 15 Jun 2021 12:38**

Hi

I'm having problems performing a query where the filter field is of type ENUM. In the database, this field is a char, where it stores the values (A, E, N) that correspond to the first letter of each item in it. However, the SQL generated by the handle and entire description of the item. Note that for SQLSERVER the problem is not presented.

Ex:

Entity

public Filter Filter { get; set; }

Map

builder.Property(e => e.Filter)
.IsRequired()
.HasColumnName("FILTER")
.HasMaxLength(1)
.IsUnicode(false)
.HasConversion(new EnumToCharConverter<Filter>());

This converter takes the first letter of the ENUM item, but is simply being ignored when using the Devart driver.

Enum

public enum Filter
{
Abs = 1,
Ext = 2,
Nen = 3,
}

Query generated

WHERE ("x".FILTER = 'Ext')

Query intended

WHERE ("x".FILTER = 'E')

Technical details

EfCore: 2.2
Driver Devart Version: 9.11.980.0

Posted: **Fri 18 Jun 2021 20:10**

The following code works OK with EF Core 5 and dotConnect for Oracle v9.14.1273:

Code: Select all

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion;
using System.Linq;

namespace EnumOracleCrashDemo {
  public class Program {
    public static string connectionString = @"User Id=scott;Password=tiger;Server=192.168.10.41/orclpdb;Direct=True;license key=...;";
    //public static string connectionString = @"Data Source=192.168.10.57\sql2019; User Id=sa; Initial Catalog=test; Application Name=abcd;";

    static void Main(string[] args) {

      var monitor = new Devart.Data.Oracle.OracleMonitor() { IsActive = true, Port = 1000, Host = "127.0.0.1" };

      using (var db = new TestModel()) {

        db.Database.EnsureDeleted();
        db.Database.EnsureCreated();

        var e = new EnumThing() { ID = 1, Filter = Filter.Abs };
        db.EnumThings.Add(e);
        db.SaveChanges();
      }

      using (var db = new TestModel()) {

        var result = db.EnumThings.Where(x => x.Filter == Filter.Abs).FirstOrDefault();
      }

    }
  }

  class TestModel : DbContext {
    public virtual DbSet<EnumThing> EnumThings { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder) {
      base.OnModelCreating(modelBuilder);

      var v = modelBuilder.Entity<EnumThing>().ToTable("TESTTABLEDELETETHIS");
      v.HasKey(x => x.ID);
      v.Property(e => e.Filter)
        .IsRequired()
        .HasColumnName("FILTER")
        .HasMaxLength(1)
        .IsUnicode(false)
        .HasConversion(new ValueConverter<Filter, string>(
            v => v.ToString().Substring(0, 1),
            v => typeof(Filter).GetEnumValues().Cast<Filter>().First(e => e.ToString()[0] == v[0])
          ));
    }


    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
      optionsBuilder.UseOracle(Program.connectionString);
      //optionsBuilder.UseSqlServer(Program.connectionString);

      base.OnConfiguring(optionsBuilder);
    }
  }

  public enum Filter {
    Abs = 1,
    Ext = 2,
    Nen = 3,
  }
  class EnumThing {
    public int ID { get; set; }
    
    public Filter Filter { get; set; }
  }
}

Please upgrade.

Devart Forums

EF Core problem with Enum

EF Core problem with Enum

Re: EF Core problem with Enum