0

Designing a database, one property is gender. We're wondering how to represent this. We could use an int, or an varchar(1) for M F or 0 1. We might also have other options going forward, e.g. unspecified or non-binary etc.

Ideally in the code, I'd set up the EF model to map the column to an enum, so if I had:

public enum Gender { Male = 1, Female = 2, Unspecified = 3 }

then I can do if (_db.Users.First().Gender == Gender.Male)

I'm happy to create the enums manually in the code.

I've been googling it but I've been a very confused by some of the other answers.

I'm not sure if the EF designer lets you do this mapping somehow, or if it requires modifications to the T4. Any suggestions or duplicate question?

NibblyPig
  • 51,118
  • 72
  • 200
  • 356

1 Answers1

0

What we do, and this might not be the best way to do so, is actually store the value of the enum in the database. Then, in code, we have an interface that mimics the table, that however has the enum instead of the int. Then we extend the entity class by giving it the interface. It's extra work, but we do so because we cast the entity to a dto object anyways, but might work for you

Example

Let's assume in the table Person we only have the gender

create table Person 
(
    Gender int not null
    --other fields
)

Then what we do is generate an interface like

public interface IPersonEssential
{
    Gender Gender {get;set;}
    // ... Other properties
}

Where Gender is the enum you propose in the OP, "Essential" is something we use to identify these objects that mimics the table, just use whathever convention you have already

Then we extend the EF class by implementing the interface, here we need to convert the int to enum and vice-versa, a most basic way to do so would be:

public partial class Person : IPersonEssential
{
    Gender IPersonEssential.Gender { get =>(Gender)Gender; set => Gender = (int)value; }
    // ... Other properties that don't match the table in type like other enums
}

which would break if unknown enum values are on the database, but you can make a simple library method to convert the int to an enum which fallbacks to a default or whethever you might need

What we also do, which might be urelated to your case, is then make an extension method to cast the entity like:

public static T CastInto<T>(this IPersonEssential input, T output) where T : IPersonEssential
{
            
    output.Gender = input.Gender;
    // .... other properties

    return output;
}

which you can use to convert an entity in a dto object if it has that interface.

we even made a tool to autogenerate this method : link

Hopefully it might be helpful, of course there are other approaches to the problem, or variations on this one.

For example you might this way be able to put "M" and "F" on the database and use the enum in code if you make an appropiate conversion in the EF class implementation of the interface.

You won't be able to use the enum directly in linq to entities, but you can still use it in code like:

_db.Users.Where(x=>x.Gender == (int)Gender.Male).First();
Mattia Mele
  • 129
  • 1
  • 6
  • Thanks that looks promising. I was hoping EF would have a simple mapping option but if there isn't one I can't think of a better way than this! One question is does this support EF query? _db.Person.Where(_ => _.Gender == Gender.Male)? I suppose not and I can't see how any solution could... – NibblyPig Aug 13 '21 at 10:54
  • There is an EF mapping option, this is an alternative approach if you're using an older EF. While you won't be using the enum as is in linq to entities, you can just cast it so you still use it in code, I've edited the post with an example – Mattia Mele Aug 13 '21 at 11:12
  • Cheers, yeah, that might be a cleaner approach for linq2entities although I think you have to pull the (int)Gender.Male part out to stop it evaluating it? – NibblyPig Aug 13 '21 at 11:41