1

I am using MVC4, EF5 and SQL Server 2012 Express LocalDB‎. The Customer domain model has Phone and Email fields. Either can be null, but, at least one must be populated.

I make sure of this using FluentValidation on both the client and server sides.

But, when I need to persist the model, I run into a limitation with EF5. If I make both required, then the persist code fails, but if I make them both optional:

...
this.Property(q => q.Phone).IsOptional();
this.Property(q => q.Email).IsOptional();

then this doesn't meet my requirements.

Q1: How do I model this - is there a simple way, or must I use a check constraint? Is this check constraint okay:

ALTER TABLE dbo.[Customer]
  ADD CONSTRAINT constraintAtLeastOneContactDetailRequired
    CHECK ( ([Phone] IS NOT NULL) OR ([Email] IS NOT NULL) )
  --CHECK ( NOT(COALESCE([Phone], [Email]) IS NULL) )    <--or this?
;

Q2: I have a UNIQUE constraint on both fields, so if I allow them to be null and add the check constraint, will I have problems since there can be multiple nulls which are by definition not unique?

Bobby B
  • 2,287
  • 2
  • 24
  • 47
  • Why do you need to add constraints on the database as well as in your ORM? Is there another client of the database? – Steve Mayne Jan 05 '13 at 12:05
  • @SteveMayne: I want data integrity at all times. I can't have two parts of the same system thinking about the data in different ways. And when someone makes changes, things tend to get messy, so it's better to make this consistent at the outset. – Bobby B Jan 05 '13 at 12:07
  • 1
    That's all well and good, but I'm sure there are complex rules in your model that you're not reflecting in the database. How far do you go? For example: Do you validate each Email field in the database using a reg-ex? I would er on the side of saying that your more advanced constraint logic belongs in your model, not duplicated into the database as well. – Steve Mayne Jan 05 '13 at 12:11
  • Prefer to have the model and backend in sync, avoids problems in the long run. And yes, a lot of stuff is in sync already because of EF. This is a simple problem, so I don't see a reason to avoid it. – Bobby B Jan 05 '13 at 12:17
  • Have you considered modelling the "contact" fields as separate entities and then have a collection of them on the customer entity? – MarkG Jan 05 '13 at 12:27
  • @MarkG: might be overkill for plain string fields... – Bobby B Jan 05 '13 at 12:33
  • @MarkG this is really your answer! – Peter Smith Jan 05 '13 at 13:08

2 Answers2

1

Looks like EF does not handle constraints, so I need to resort to SQL. This is what I am doing, but there might be a better way:

-- enforce the either-or rule:
ALTER TABLE Customer ADD CONSTRAINT chk1 CHECK ((Phone IS NOT NULL) OR (Email IS NOT NULL));

-- columns cannot be declared as unique (because of multiple nulls), so use workaround:
CREATE UNIQUE NONCLUSTERED INDEX idx1 ON Customer(Phone) WHERE Phone IS NOT NULL;
CREATE UNIQUE NONCLUSTERED INDEX idx2 ON Contact(Email) WHERE Email IS NOT NULL;

The uniqueness problem is discussed here.

Community
  • 1
  • 1
Bobby B
  • 2,287
  • 2
  • 24
  • 47
0

I think MarkG above has the solution. Create a separate table/entity CustomerContectDetails:

Field           Type
CustomerID      int (Foreign key link to cusotomer tables)
ContactType     int (enumerator - initially either phone or email)
ContactDetails  nvarchar(255) to hold either the email or phone number

Then a simple count

if (CustomerContectDetails.Count(x => x.CustomerID == localCustomerID) > 0)
{ // positive actions}
else
{ // negative actions}

The solution can also be extended in the future to additional contacts and additional rules can be added based on the contact type. And no database constraints required and the business rules stay in one place.

Hope that helps.

Peter Smith
  • 5,528
  • 8
  • 51
  • 77
  • How does this stop someone inserting a Customer without any CustomerContactDetail records into the database (using another program or a direct database query, for example)? This is the constraint the the OP wishes to introduce into the database. – Steve Mayne Jan 05 '13 at 13:30
  • This is an interesting idea, but a constraint seems much simpler and more maintainable. – Bobby B Jan 05 '13 at 13:41
  • Sometimes an orthoganal solution opens new approaches. An objective of EF, as I undertand it is to move much of the maintenance into the application. Also, I don't know enough about contraints, but could a contraint be written across more than one table to cover this. – Peter Smith Jan 05 '13 at 18:20
  • Not sure, but I believe constraints cannot span multiple tables? Nonetheless, the fields are within the same table so it works. – Bobby B Jan 05 '13 at 22:43