Help on a Many to Many relationship with a PayLoad

Question

Dear all,

I am trying to define a many to many relationship against an SQLs erver database. I manage to define assoaciation as follow :

Doing so, by compiling my EF project I get an error message :

"Error 3027:No mapping specified fro the following EntitySet/AssociationSet - AccessLevelRoles,AccessLevelUsers."

The mapping I have for that table is as follow :

 

I am quite new to this, so hoping you could help me solving this error.

thnaks

serge

---

Your knowledge is enhanced by that of others.

Answer 1

Hi,

Just delete the "Maps to Users" and "Maps to Roles" and I think that it should fix itself. If not please post the errors you get after this!

---

--Rune

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 2

initially iut was without Maps to USers and Map to Roles and I get same errors so I try with adding it but same thing

any idea?

---

Your knowledge is enhanced by that of others.

Answer 3

Hi,

I don't think you get the exact same error. Could you try to remove it and post the error and the mapping definition where your error occur?

---

--Rune

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 4

Hi Serge,

Welcome!

I'm not sure what's the kind of your project: Model-Frist or Database First.

As @Rune says, you don't need to set mapping by yourself. In Model-Frist, just add the entities and associations. In Database First, just retrieve the datatable from database.

You can open a new project to test, thanks.

Have a nice day.

---

Alan Chen[MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 5

Hello all,

Here is the error without the previous mapping

As you can see I ahve same error.

And here is the maaping

I get the Entities directly from a created database and add the association manually as the designer is not capable to find them automatically based on the database I have

---

Your knowledge is enhanced by that of others.

Answer 6

Hi again,

Is this EF model a Database-First or Model-First model? (Database-First is that you update the model from an existing database, Model-First is that you design the database in the EF modelling tool and create the database using "Generate Database from Model..." context menu)

The error message you are seeing is not about your table AccessLevel, but the association and that it isn't mapped to any foreign key definition in your store.

However, if no matter if you are using Database-First or Model-First the relation mapping should be defined automatically for you.

If it is a database-first, can you delete these three entities and recreate them? If it is Model-First, you could try to use the "Generate Database from Model..." again  to see if that helps.

An additional comment.

A  many-to-many relationship table in EF is recommended to only contain the foreign keys to the different table (and these as primary keys).

If the the model is based on Database-First, your AccessLevel table should not contain the Id column. EF will then hide this table for you and you will in the Users and Roles entities have a navigation property directly to Roles (in Users) and Users (in Roles).

If the model is based on Model-First I recommend you to instead of creating the many-to-many table yourself just create a new association between Users and Roles where both ends are defined as Many. This will internally create the link table for you.

---

--Rune

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 7

Thnaks for your reply.

So I am in DataBase first situation. The database I receive I get it from one customer so I need to be able to use it as it is. Of course I can delete the ID for testing but when deploying my solution at customer I will not be able to change its structure

---

Your knowledge is enhanced by that of others.

Answer 8

ok, then you are a bit limited, but it should still work and you should not get these errors.

Have you tried to delete the three entities and recreate them from the database?

The references between AccessLevel and Roles/Users does exist in the database as foreign keys? Remember that the foreign keys must exist in the tables that are related to the reference.

If you still has problems, could you post a link with the edmx file for these tables, so I can take a look at it?

---

--Rune

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 9

1. By recreating the table as there were initialy return same error after creating the association from Model designer.
2. By removing the Primary key ID from AccessLevel and define the two other column as FK, when updating the model from my database I have a many to Many relationship between Role and User and the AccessLevel table is not shown
   
   Compliing goes ok
3. If I keep the original table of my customer as shown above, there is no foreign key define. SO i have create 2 foreign key just for test and update the model from database. At that time the model shows the 3 tables with the association and no compiling error

 

Cannot be more clear now for me. I need to absolutly get the foreign key defined.
Is there a risk in my customer data or querry if I add those foreign keys to its database ?

Database is in production

thnaks

 

---

Your knowledge is enhanced by that of others.

Answer 10

Hi again,

You answered a lot there :)

Well, if the database doesn't contain the foreign keys I'm afraid you cant do create associations in the EF Model :( All associations in EF needs a Store foreign key to map against..

About adding foreign keys in the database.. If the data is correct, there shouldn't be any risk adding the foreign keys in the database. It's actually good to have them there to keep consistent data in the AccessLevel table.

However, since the AccessLevel have been without foreign keys it may contain values that doesn't exist in the related tables (since there hasn't been any foreign key constraint earlier). You will then get an error when you try to create the foreign keys that states this. I would recommend you to get a copy of the database and test locally first :)

 

---

--Rune

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".