Entity Framework Database Update First Multiplicity conflicts
c#, entity-framework, sql
Solution
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`.
Problem
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 ```