One out of 2 properties should be null (EntityFramework Code First)

Question

I've searched a lot in the forum, but haven't found anything about regarding this issue.

I have 2 properties in the EntityFramework Code First:

    [Column(TypeName = "Money")]
    public decimal? Debit { get; set; }
    [Column(TypeName = "Money")]
    public decimal? Credit { get; set; }

One of them should be not null, but the other one should be null Examples:

Debit=null;
Credit=34;

Debit=45;
Credit=null;

On the other hand, it should not be possible to set both or none of them null. Is it possible to handle this issue with data annotations or should I solve it with a workaround?

Best regards!

Answer 1

You could always do the validation on the database side with a constraint,

ALTER TABLE TableName 
    ADD CONSTRAINT OneColumnNull CHECK 
    ((Debit IS NULL AND Credit IS NOT NULL) OR 
     (Debit IS NOT NULL AND Credit IS NULL)
    )

does exactly what you are looking for. You could just put that query as part of one of your database migration scripts.

Answer 2

I don't see how you can do this with the available Code First Data Annotations.

You could override the ValidateEntity method of the DbContext class and add validation logic for that type of entity.

Or you could add argument validation logic to you properties.

private decimal? _debit;
[Column(TypeName = "Money")]
public decimal? Debit
{
    get { return _debit; }
    set
    {
        // logic to check _credit against value 
        _debit = value;
    }
}
private decimal? _credit;
[Column(TypeName = "Money")]
public decimal? Credit
{
    get { return _credit; }
    set
    {
        // logic to check _debit against value 
        _credit = value;
    }
}

Answer 3

In SQL Server, a database field is either nullable or not nullable. Your design requires columns to be null sometimes which means both must be nullable

alternatives include Use zero instead of null.

Or have one column Amount and another column IsDebit

You could also write properties for your class such as

[NonMapped]
public int? Credit
{ get 
 { if ( dataCredit == 0 ) return null; }
}