Entity Framework Database Update First Multiplicity conflicts

Question

Most of the other questions I've seen on these multiplicity conflicts have been code first, where-as I'm going DB first. I've made some schema changes and I'm getting the following error when I refresh my model from database:

Multiplicity conflicts with the referential constraint in Role 'Bet' in relationship 'FK_MarketSelectionWager_Bet'. Because all of the properties in the Dependent Role are non-nullable, multiplicity of the Principal Role must be '1'.

Any pointers on what I need to do to fix in either SQL or my model would be great.

Here are the SQL tables involved:

MarketSelectionWager /* Object: Table [dbo].[MarketSelectionWager] Script Date: 31/01/2014 12:08:34 */ SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[MarketSelectionWager](
    [MarketSelectionWagerIdentifier] [varchar](255) NOT NULL,
    [MarketIdentifier] [varchar](255) NULL,
    [SelectionName] [varchar](255) NULL,
    [SelectionIdentifier] [varchar](255) NULL,
    [WagerIdentifier] [varchar](255) NULL,
    [PriceType] [varchar](5) NULL,
    [PriceFrac] [varchar](50) NULL,
    [WagerStakeWin] [money] NULL,
    [WagerStakePlace] [money] NULL,
    [RunningStakeWin] [money] NULL,
    [RunningStakePlace] [money] NULL,
    [LiabilityWin] [money] NULL,
    [LiabilityPlace] [money] NULL,
    [BetIdentifier] [varchar](255) NULL,
    [BetCombinationIndex] [int] NULL,
    [WagerStatus] [int] NULL,
    [NumberRunningAfter] [int] NULL,
    [NumberRunningSameTime] [int] NULL,
    [TimeToNextRemainingRunner] [int] NULL,
    [HasOtherSelectionsRunningSameTime] [bit] NULL,
    [HasOtherSelectionsRunningAfter] [bit] NULL,
    [BetId] [int] NOT NULL,
 CONSTRAINT [PK_MarketLiabilty] PRIMARY KEY CLUSTERED 
(
    [MarketSelectionWagerIdentifier] ASC,
    [BetId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[MarketSelectionWager]  WITH CHECK ADD  CONSTRAINT [FK_MarketSelectionWager_Bet] FOREIGN KEY([BetId])
REFERENCES [dbo].[Bet] ([BetID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[MarketSelectionWager] CHECK CONSTRAINT [FK_MarketSelectionWager_Bet]
GO

Bet

/****** Object:  Table [dbo].[Bet]    Script Date: 31/01/2014 12:09:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Bet](
    [BetID] [int] IDENTITY(1,1) NOT NULL,
    [BetSlipID] [int] NOT NULL,
    [BetPayout] [money] NULL,
    [BetPotentialReturn] [money] NULL,
    [BetStake] [money] NULL,
    [BetRefund] [money] NULL,
    [DateBetSettled] [datetime] NULL,
    [SettleStatusID] [int] NULL,
    [SportID] [int] NULL,
    [tsTimestamp] [timestamp] NULL,
    [SportMixID] [int] NULL,
    [WagerTypeMixID] [int] NULL,
    [EventMixID] [int] NULL,
    [MarketMixID] [int] NULL,
    [SettleTypeID] [int] NULL,
    [MaxOffDate] [datetime] NULL,
 CONSTRAINT [PK_Bet] PRIMARY KEY CLUSTERED 
(
    [BetID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Bet] ADD  CONSTRAINT [DF_BS_Bet_BetPayout]  DEFAULT ((0)) FOR [BetPayout]
GO

ALTER TABLE [dbo].[Bet] ADD  CONSTRAINT [DF_BS_Bet_BetPotentialReturn]  DEFAULT ((0)) FOR [BetPotentialReturn]
GO

ALTER TABLE [dbo].[Bet] ADD  CONSTRAINT [DF_BS_Bet_BetStake]  DEFAULT ((0)) FOR [BetStake]
GO

ALTER TABLE [dbo].[Bet] ADD  CONSTRAINT [DF_BS_Bet_BetRefund]  DEFAULT ((0)) FOR [BetRefund]
GO

ALTER TABLE [dbo].[Bet]  WITH CHECK ADD  CONSTRAINT [FK_Bet_BetSlip] FOREIGN KEY([BetSlipID])
REFERENCES [dbo].[BetSlip] ([BetSlipId])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Bet] CHECK CONSTRAINT [FK_Bet_BetSlip]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet', @level2type=N'COLUMN',@level2name=N'BetID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet', @level2type=N'COLUMN',@level2name=N'BetID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet', @level2type=N'COLUMN',@level2name=N'BetID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet', @level2type=N'COLUMN',@level2name=N'BetSlipID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet', @level2type=N'COLUMN',@level2name=N'BetSlipID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet', @level2type=N'COLUMN',@level2name=N'BetSlipID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet', @level2type=N'COLUMN',@level2name=N'BetPayout'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet', @level2type=N'COLUMN',@level2name=N'BetPayout'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet', @level2type=N'COLUMN',@level2name=N'BetPayout'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet', @level2type=N'COLUMN',@level2name=N'BetPotentialReturn'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet', @level2type=N'COLUMN',@level2name=N'BetPotentialReturn'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=2250 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet', @level2type=N'COLUMN',@level2name=N'BetPotentialReturn'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet', @level2type=N'COLUMN',@level2name=N'BetStake'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet', @level2type=N'COLUMN',@level2name=N'BetStake'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet', @level2type=N'COLUMN',@level2name=N'BetStake'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet', @level2type=N'COLUMN',@level2name=N'BetRefund'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet', @level2type=N'COLUMN',@level2name=N'BetRefund'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet', @level2type=N'COLUMN',@level2name=N'BetRefund'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet', @level2type=N'COLUMN',@level2name=N'DateBetSettled'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet', @level2type=N'COLUMN',@level2name=N'DateBetSettled'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet', @level2type=N'COLUMN',@level2name=N'DateBetSettled'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DefaultView', @value=0x02 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Filter', @value=NULL , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_LinkChildFields', @value=N'BetID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_LinkMasterFields', @value=N'BetID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_OrderBy', @value=NULL , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_OrderByOn', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Orientation', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SubdatasheetName', @value=N'dbo.BS_Selection' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_TableMaxRecords', @value=10000 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Bet'
GO

@GertArnold but I don't want nulls to be allowed in there on the marketselectionwager table (can the EF model not handle that) The reason being it is part of a composite primary key? — DermFrench, Jan 31 '14 at 12:17
You don't have anything to fix in sql, problem is in your edmx or fluent mapping as the case may be. — Mat J, Jan 31 '14 at 12:18
@Mathew, I'm just using the wizard to refresh my edmx model. It doesn't seem to be able to cope, do you know what I would fix in there? — DermFrench, Jan 31 '14 at 12:22
Sure, I only asked whether that was one of your changes, because I've noticed myself that EF doesn't change multiplicity in its conceptual model (class model) when it collects such updates. You can change the multiplicity manually in the properties of the association. — Gert Arnold, Jan 31 '14 at 12:28
@GertArnold I'll remove the tables from my model then refresh it? See if it picks them up correctly then, I'll let you know how I get on — DermFrench, Jan 31 '14 at 12:29
@GertArnold - that worked removing the tables from my model and then refreshing, if you want to post that a suggestion (saying you think I've added BetId non null and the EF doesn't change multiplicity in its conceptual model, I'll accept as answer). — DermFrench, Jan 31 '14 at 12:34

Gert Arnold · Accepted Answer · 2014-02-01T11:58:42.743

When Entity framework updates a model from the database there are things it does and doesn't do. It adds new objects, fields and associations and more (I think only the EF team can produce an exhaustive list). I've experienced that it doesn't update multiplicity constraints in the conceptual model.

So when you add a NOT NULL constraint to a column in the database (BetId in your case), the store model will be updated, but the conceptual model (class model) will hang on to the initial nullable association (0..1 - n). You'll notice that the model doesn't pass its validation anymore and that looks pretty scary (after all, you only updated), but the fix is easy: in the edmx designer, manually change the multiplicity of the "1" side of the association so it becomes 1-n.

Entity Framework Database Update First Multiplicity conflicts

1 Answers1

Linked