mvc database first issues with edmx/relationships and view showing id field

Question

my task is very simple, i need to create a library mvc web app that allows crud operations, the only constraints are that 1 book can have multiple authors and can also have multiple publishers.

to accomplish this, this is my database model:

and the respective sql code for the books->books_authors->authors table relationships, the publishers relationship is defined in the same way

CREATE TABLE [dbo].[BOOKS](
[ID] [int] NOT NULL,
[TITLE] [nvarchar](max) NOT NULL,
[PAGES] [int] NOT NULL,
[SYNOPSIS] [nvarchar](max) NOT NULL,
[PUBLISHDATE] [date] NOT NULL,
[ISBN] [nvarchar](max) NOT NULL,
[SUBJECT] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_BOOKS] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO


CREATE TABLE [dbo].[BOOKS_AUTHORS](
[BOOKID] [int] NOT NULL,
[AUTHORID] [int] NOT NULL,


CONSTRAINT [PK_BOOKS_AUTHORS] PRIMARY KEY CLUSTERED 
(
    [BOOKID] ASC,
    [AUTHORID] 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].[BOOKS_AUTHORS]  WITH CHECK ADD  CONSTRAINT [FK_BOOKS_AUTHORS_AUTHORS] FOREIGN KEY([AUTHORID])
REFERENCES [dbo].[AUTHORS] ([ID])
GO
ALTER TABLE [dbo].[BOOKS_AUTHORS] CHECK CONSTRAINT [FK_BOOKS_AUTHORS_AUTHORS]
GO
ALTER TABLE [dbo].[BOOKS_AUTHORS]  WITH CHECK ADD  CONSTRAINT [FK_BOOKS_AUTHORS_BOOKS1] FOREIGN KEY([BOOKID])
REFERENCES [dbo].[BOOKS] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[BOOKS_AUTHORS] CHECK CONSTRAINT [FK_BOOKS_AUTHORS_BOOKS1]
GO




CREATE TABLE [dbo].[AUTHORS](
    [ID] [int] NOT NULL,
    [FIRSTNAME] [nvarchar](max) NOT NULL,
    [LASTNAME] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_AUTHORS] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

now onto my issues, when i add a ado.net model to the mvc web app, the edmx looks like this:

i thought i defined a 1 to many relationship between BOOKS and BOOKS_AUTHORS/BOOKS_PUBLISHERS, why is it now showing up on the edmx?

my other issue is that given the above edmx model, when i create a scaffolded controller with views, the create action lists the ID field from BOOKS as an input instead of hiding it while on the index action the same ID field is hidden, like so:

i have done it on the primary tables but i'm still struggling with the relationship tables i.e BOOKS_AUTHORS, on this table both columns have to be marked as primary key, correct? do i need to do anything else to the relationship tables? — user2983177, Jan 19 '16 at 11:23

Arijit Mukherjee · Accepted Answer · 2016-01-19T11:11:54.517

1

Entity Framework edmx, do not show the Reference Relationship Table. that is why Its showing Many to Many relationship between book and authors

According to MSDN it is pure join table

2nd Issue is because, you have not defined your primary key as Identity (Auto Generated)

edited Jan 19 '16 at 11:11

answered Jan 19 '16 at 11:05

Arijit Mukherjee

3,817
2
31
51

i'm correcting it now on all the main tables excluding the relationship tables. are the composite keys defined correctly and should they also be defined as Identity? – user2983177 Jan 19 '16 at 11:15
yes, cos then only edmx will understand that user won't have to enter it, as primary key can not be null – Arijit Mukherjee Jan 19 '16 at 11:16
but on the BOOKS_AUTHORS table i can only choose one of the columns to be the identity column, i know that both BOOKID and AUTHORID have to be a composite primary key – user2983177 Jan 19 '16 at 11:21
1

None of them will be identity on that table as its just a pure join table which we say as referential table as well – Arijit Mukherjee Jan 19 '16 at 11:28
glad it helped @user2983177 – Arijit Mukherjee Jan 19 '16 at 14:28

score 1 · Answer 2 · answered Jan 19 '16 at 11:19

1

In EDMX file that's not Relationship, that shows Navigation property , it allows you to navigate from one entity to a "connected" entity

while using Book table yo can navigate to author table like

var a=(from d in _db.Book.Include(AUTHOR)...) it will include author table to use in same Linq

for second query make your Id field auto Generated.

answered Jan 19 '16 at 11:19

Viplock

3,259
1
23
32

does that mean i have to add the fields "first name", "last name" and "publisher name" manually to the index view? – user2983177 Jan 19 '16 at 12:06
2

by scaffolding ,index page will be generated according to the class generated by EF .So if something is not there in your Book class and you want it , you have to create a new model Class which will have all the data you required on Index page. – Viplock Jan 19 '16 at 12:11
@user2983177 yes i think so – Arijit Mukherjee Jan 19 '16 at 12:13
i have several questions about the viewmodel approach so i'll create a new question and link people to this one – user2983177 Jan 19 '16 at 15:47
@Viplock could you check my other question: http://stackoverflow.com/questions/34882018/mvc-use-viewmodel-to-display-data-from-multiple-tables-and-perform-crud-operatio – user2983177 Jan 19 '16 at 17:30

mvc database first issues with edmx/relationships and view showing id field

2 Answers2

Linked