CODEWITHSUNDEEP
sqlasp.net-mvcentity-frameworkdatabase-design
Sergey Sypalo
Sergey Sypalo

Reputation: 1339

MVC EF Database first and SQL Many to many relationship

Source:

DictionaryTable:

DictID     int
RUWordID   int   -> FK to RUWordsTable.RUWordID
ENWordID   int   -> FK to ENWordsTable.ENWordID

RUWordTable:

RUWordID   int
Word       string

ENWordTable:

ENWordID   int
Word       string

I want be able read/write data to table that uses following structure:

RUWord   ENWord
Привет   Hello
...

What the best option in terms of speed and easy data access from MVC views to do what i want. As per my understanding there is options to create SQL view and use it (but not sure about possible INSERTS of data). Or just create the same but using Entity Framework in Visual Studio designer. Basically one word could be transalted differently (have several entries in other table). My goal to find a way how to add words to dictionary with automatic inserts (when source or translation not found add it to reference table, if found - just use existing entry)

Upvotes: 0

Views: 801

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109079

It depends much on the amount of data you need to read in one go. If you want to look up translations of one or a few words an Entity Framework model will do a perfect job. If you need to fetch massive amounts of data a view or stored procedure will be better. But I don't expect the latter to happen. In an MVC view you'll probably use paging.

As for inserting data EF will be a viable choice. You already have a junction table defined (DictionaryTable). You could set up EF in a way that the junction table is transparent:

class RuWord
{
    public int Id { get; set; }
    public string Word { get; set; }
    public ICollection<EnWord> EnWords { get; set; }
}

class EnWord
{
    public int Id { get; set; }
    public string Word { get; set; }
    public ICollection<RuWord> RuWords { get; set; }
}

(supposing you'd work code-first)

In data entry you can add a new Russian word to the RussianWords DbSet of the context and add new or existing English words to the word's EnWords collection and call SaveChanges(). EF will insert the words and the appropriate records in the junction table (having both foreign keys as its composite primary key).

But... In real life I hardly ever see a pure junction table. I bring this in because in your case I can hardly imagine that just registering the associations between Russian and English words will be sufficient. Are you not (at least) going to need some degree of preference? Like there are more translations of the word "date", but the preferred one would be the calendar thing (unless you're working for a dating site, but even then...). Anyway, if there is something you want to record about the association you need to map the junction table explicitly and create the association records will all their details in code.

I hope this gives some directions.

Edit (after your comment)

If you use an explicit junction entity (i.e. a class that corresponds with the junction table). This entity can have the two properties EnWord and RuWord as references to the two tables mentioned above. Data entry would imply creating a new DictionaryTable instance and setting its properties (which can be new or existing words).

Upvotes: 1

Related Questions