Single Review assigned to multiple Genres

sophia · December 3rd, 2011, 03:03 PM

Greetings!!

Although book shows that we can assign a single Review to single Genre only. But is it possible to assign single Review to multiple Genres? If yes, then where the code should be changed?

Thanks...

Imar · December 4th, 2011, 06:13 AM

Hi there,

You would create a new table with the following two columns:

ReviewId int
GenreId int

Then you need to link the two related tables (Review and Genre) to their respective columns in this new table. If you now update or recreate the EDMX you get a many to many relationship, so you can do stuff like this:

myReview.Genres.Add(genre1);
myReview.Genres.Add(genre2);

Where the genre variables point to valid instances of the Genre class.

Hope this helps,

Imar

sophia · December 6th, 2011, 01:39 PM

Thanks Imar, I will try this and update here...

sophia · December 7th, 2011, 01:35 PM

I am just gonna implement it, but one thing I would ask that isn't there any other way to implement many to many or one to many relationship instead of creating and maintaining separate table?

Thanks

Imar · December 7th, 2011, 02:32 PM

One to many: yes, there is, as is shown with the Reviews and Genre (and Album and Picture tables). For many to many you need a separate table to link multiple records to each other.

Cheers,

Imar

sophia · January 15th, 2012, 01:33 PM

Back to topic,
I have slightly changed your hand code example on page no 555.
while inserting many Genries for single Review in third table, there is no any need to take much care but I am stuck with updating those many Genre Ids for a single Review in third table.

I want that Genre Ids should be updated only when they are actually changed by user.

I used checkboxlist instead of ddl and bound it similarly as you did.
Code behind is

Code:

protected void SaveButton_Click(object sender, EventArgs e)
{
using (PlanetWroxEntities myEntities = new PlanetWroxEntities())
{
Review myReview;
if (_id == -1) // Insert new item
{
myReview = new Review();
myReview.CreateDateTime = DateTime.Now;
myReview.UpdateDateTime = myReview.CreateDateTime;
myEntities.AddToReviews(myReview);
}
else // update existing item
{
myReview = (from r in myEntities.Reviews
                         where r.Id == _id
select r).Single();
myReview.UpdateDateTime = DateTime.Now;
}
myReview.Title = TitleText.Text;
... 
...  
myEntities.SaveChanges();

foreach (ListItem li in GenreList.Items)
            {
                if (li.Selected)
                {
                    myGenRev = new GenresReview();
                    myGenRev.GenreId = Convert.ToInt32(li.Value);
                    myCatRel.ReviewId = myReview.Id;
                    myEntities.AddToGenresReview(myGenRev);
                    myEntities.SaveChanges();
                }
            }

}

So,
1.) how I enhance the foreach loop so that it updates the record in third table (GenresReview) when they have been changed by user.
2.) I also know that it will require the deletion of existing records for that single Review as user may choose less Genres than the previous.

Kindly suggest the solution keeping the above 2 point in mind.

Imar · January 15th, 2012, 04:22 PM

If your relation object contains no other data (e.g. you just store the two foreign keys), here's what would work:

Call Load on the collection so you get all relation records from the database
Call Clear
Add the selected items by instantiating new relation objects.

You may want to consider getting the Entity Framework book by Julia Lerman; it shows you how to do this, and much more, with EF.

Imar

sophia · January 16th, 2012, 02:25 AM

Thanks Imar for points,
but can you please give small code snippets of all the three points you suggested.
According second point will this clear only those records which have actually been changed by user?

buying a book for single topic does not seems to be in my budget.

Thanks...

Imar · January 16th, 2012, 03:17 AM

Load and Clear are methods on your collection that you can call directly so you already have examples. And you already have the code for the third item.

This is a bit too off-topic for my book to discuss in full detail here. Google has lots of examples on this if you need more information.

If you're working with EF, that book is money well-spent.

Imar

sophia · January 16th, 2012, 05:40 AM

HI Imar,
thanks I will give it try, but last question (which was more important)
Would it update only those records which have been really changed by user???