0

I'm working with a legacy Oracle database that has a column on a table which stores boolean values as 'Y' or 'N' characters.

I have mapped/converted this column out like so:

MappingSchema.Default.SetConverter<char, bool>(ConvertToBoolean);
MappingSchema.Default.SetConverter<bool, char>(ConvertToChar);

ConvertToBoolean & ConvertToChar are simply functions that map between the types.

Here's the field:

    private char hasDog;
        
    [Column("HAS_DOG")]
    public bool HasDog
    {
        get => ConvertToBoolean(hasDog);
        set => hasDog = ConvertToChar(value);
    }

This has worked well for simply retrieving data, however, it seems the translation of the following:

var humanQuery = (from human in database.Humans
                            join vetVisit in database.VetVisits on human.Identifier equals vetVisit.Identifier
                            select new HumanModel(
                               human.Identifier
                               human.Name,
                               human.HasDog,
                               vetVisit.Date,
                               vetVisit.Year,
                               vetVisit.PaymentDue
                                ));

// humanQuery is filtered by year here

            var query = from vetVisits in database.VetVisits
                        select new VetPaymentModel(
                            (humanQuery).First().Year,
                            (humanQuery).Where(q => q.HasDog).Sum(q => q.PaymentDue), -- These 2 lines aren't correctly translated to Y/N
                            (humanQuery).Where(q => !q.HasDog).Sum(q => q.PaymentDue)
                            );

As pointed out above, the .Where clause here doesn't translate the boolean comparison of HasDog being true/false to the relevant Y/N values, but instead a 0/1 and results in the error

ORA-01722: invalid number

Is there any way to handle this case? I'd like the generated SQL to check that HAS_DOG = 'Y' for instance with the specified Where clause :)

Notes

  • I'm not using EntityFramework here, the application module that this query exists in doesn't use EF/EFCore
jarodsmk
  • 1,876
  • 2
  • 21
  • 40

1 Answers1

1

You can define new mapping schema for your particular DataConnection:

var ms = new MappingSchema();
builder = ms.GetFluentMappingBuilder();

builder.Entity<Human>()
    .Property(e => e.HasDog)
    .HasConversion(v => v ? 'Y' : 'N', p => p == 'Y');

Create this schema ONCE and use when creating DataConnection

Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32