Many-to-many relationship with Entity Framework and MVC with ordering

I am using Code First with POCO with Entity Framework 4 to create a many-to-many relationship. The JOIN table is correctly created to link the t开发者_如何学运维wo entities. However, I need an additional column in the JOIN table to create an ordering in the relationship. Any way to archive this? How do the POCO objects have to be modified to include the additional index column in the JOIN table to allow the ordering? Is this even possible with Code First? If not, how should I proceed?

public class Product
{
    public Guid ID { get; set; }
    public virtual IList<Navigation> Navigations { get; set; }
}

public class Navigation
{
    public Guid ID { get; set; }
    public virtual IList<Product> Products { get; set; }
}

A table with the following columns is automatically created, there I want an additional column for the ordering:

NavigationProducts
    Navigation_ID
    Product_ID

I could manually create the additional column, but this would break the automatic creation of the tables when the schema changes.

---

I've just had to do something similar. It's the first solution I thought of, and I ran a quick project to prototype it. It works, although I have barely spent any time on it, so something tells me the whole thing could be made more efficient, given more thought. It is quite flexible however.

I've used the two terms Webpage and Widget in this solution, and to clarify, I'm expecting the following behaviour:

• A Webpage can have many Widgets assigned to it, and these widgets must be ordered/order-able.
• At the same time a Widget can be assigned to many different Webpages.
• The Widgets must maintain different orderings for each different Webpage that they are attached to.

---

Background

---

I've used 4 Entities in this example, although this solution hinges around using 1 Entity as the crux. This Entity gets mapped to it's own table and provides 2 join tables coming off it -- and these go to the tables for the 2 Entities that I originally wanted the Many-To-Many relationship for.

The Entities are as follows:

IndexedWebpageWidget: This is the important Entity. Both Concrete Entities below have a Many-To-One relationship with this Entity.

BaseUnit: Abstract base class for Webpage and Widget - (contains Id, Title and Content properties)

Webpage: Concrete BaseUnit subclass, containing one extra property (a List of IndexedWebpageWidget).

Widget: Same as webpage, also containing one extra property (also a List of IndexedWebpageWidget).

---

Entities

---

BaseUnit:

public abstract class BaseUnit
    {
        [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        [Required, MaxLength(300)]
        public string Title { get; set; }

        [Required, AllowHtml]
        public string Content { get; set; }
    }

Widget & Webpage:

public class Widget : BaseUnit
{
    [InverseProperty("Widget")]
    public virtual List<IndexedWebpageWidget> Webpages { get; set; }
}

public class Webpage : BaseUnit
{
    [InverseProperty("Webpage")]
    public virtual List<IndexedWebpageWidget> Widgets { get; set; }
}

IndexedWebpageWidget:

public class IndexedWebpageWidget
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [Required]
    public int Order { get; set; }

    [Required]
    [InverseProperty("Widgets")]
    public virtual Webpage Webpage { get; set; }

    [Required]
    [InverseProperty("Webpages")]
    public virtual Widget Widget { get; set; }
}

The [InverseProperty] attributes here are to tell Entity Framework which ends are which. This is probably not needed as knows which end of a One-To-Many is the principal. the primary keys are the foreign keys.

---

Testing

---

I created a quick DbContext, and a DBInitialiser, and used this to populate the DB. These look like this:

public class WebWidgetTestContext : DbContext
    {
        public DbSet<Webpage> Webpages { get; set; }
        public DbSet<Widget> Widgets { get; set; }
        public DbSet<IndexedWebpageWidget> WebpageWidgets { get; set; }
    }

    public class WebWidgetInitialiser : DropCreateDatabaseIfModelChanges<WebWidgetTestContext>
    {
        protected override void Seed(WebWidgetTestContext context)
        {
            //
            // create 3 pages
            //

            var webpageA = new Webpage
            {
                Title = "Webpage A",
                Content = "Content for Webpage A",
                Widgets = new List<IndexedWebpageWidget>()
            };

            var webpageB = new Webpage
            {
                Title = "Webpage B",
                Content = "Content for Webpage B",
                Widgets = new List<IndexedWebpageWidget>()
            };

            var webpageC = new Webpage
            {
                Title = "Webpage C",
                Content = "Content for Webpage C",
                Widgets = new List<IndexedWebpageWidget>()
            };


            //
            // create 3 widgets
            //

            var widget1 = new Widget
            {
                Title = "Widget 1",
                Content = "Content for Widget 1",
                Webpages = new List<IndexedWebpageWidget>()
            };

            var widget2 = new Widget
            {
                Title = "Widget 2",
                Content = "Content for Widget 2",
                Webpages = new List<IndexedWebpageWidget>()
            };

            var widget3 = new Widget
            {
                Title = "Widget 3",
                Content = "Content for Widget 3",
                Webpages = new List<IndexedWebpageWidget>()
            };


            // now match them up

            var map1 = new IndexedWebpageWidget
            {
                Webpage = webpageA,
                Widget = widget1,
                Order = 1
            };

            var map2 = new IndexedWebpageWidget
            {
                Webpage = webpageA,
                Widget = widget2,
                Order = 2
            };

            var map3 = new IndexedWebpageWidget
            {
                Webpage = webpageB,
                Widget = widget1,
                Order = 1
            };

            var map4 = new IndexedWebpageWidget
            {
                Webpage = webpageB,
                Widget = widget3,
                Order = 3
            };

            var map5 = new IndexedWebpageWidget
            {
                Webpage = webpageC,
                Widget = widget2,
                Order = 2
            };

            var map6 = new IndexedWebpageWidget
            {
                Webpage = webpageC,
                Widget = widget3,
                Order = 1
            };

            // add
            context.WebpageWidgets.Add(map1);
            context.WebpageWidgets.Add(map2);
            context.WebpageWidgets.Add(map3);
            context.WebpageWidgets.Add(map4);
            context.WebpageWidgets.Add(map5);
            context.WebpageWidgets.Add(map6);

            // save
            context.SaveChanges();

        }
    }

_

You can see that I've added 3 DBSets to the context. I did this so it was easy for me to seed the database in the Initialiser.

Note that map5 and map6 assign different ordering to the widgets for Webpage C...

It's also worth noting that Entity Framework creates only 3 tables - dbo.IndexedWebpageWidgets, dbo.Webpages, and dbo.Widgets. The table for IndexedWebpageWidgets acts exactly like a single join table, with an added order field.

_

Finally, to have a quick viewing I modified my home controller:

public class HomeController : Controller
{
    private WebWidgetTestContext db = new WebWidgetTestContext();

    public ActionResult Index()
    {
        var model = db.Webpages.ToList();

        return View(model);
    }
}

And in my View:

@model List<WebpageWidgetTest.Models.Webpage>
@{
    ViewBag.Title = "Home Page";
}
<h2>@ViewBag.Message</h2>
<div>
    @{
        foreach (var webpage in Model)
        {
        <div>
            <h4>@webpage.Title</h4>
            <p>@webpage.Content</p>
            <p>Applicable Widgets:</p>
            @foreach (var widget in webpage.Widgets.OrderBy(w => w.Order))
            {
                <div>
                    <h5>@widget.Widget.Title (@widget.Order)</h5>
                    <p>@widget.Widget.Content</p>
                </div>
            }
        </div><hr /><br /><br />
        }
    }
</div>

In the Razor code above the ViewModel is a list of all the webpages. I loop through them and write out the scalar properties. Then for each IndexedWebpageWidget in the Widgets property, I loop and write out the widgets, in order. Swapping the value of the order property in the db will cause them to appear in different orders.

-

This basically yields the following HTML:

Webpage A

Content for Webpage A

Applicable Widgets:

Widget 1 (1)

Content for Widget 1

Widget 2 (2)

Content for Widget 2



Webpage B

Content for Webpage B

Applicable Widgets:

Widget 1 (1)

Content for Widget 1

Widget 3 (3)

Content for Widget 3



Webpage C

Content for Webpage C

Applicable Widgets:

Widget 3 (1)

Content for Widget 3

Widget 2 (2)

Content for Widget 2

-

You can see in Webpage C that Widget 3 comes before widget 2, based on it's order property (which is shown next to each widget's title in brackets)... Swapping the order for one widget against a webpage will not affect it's ordering for another webpage. Therefore all requirements have been achieved.

The only real downside of this approach is that you have one extra entity. This actually comes in handy though for traversing between the 2 objects it ties together. I'm not sure of another way that this could be done. Each side of a relationship needs to be mapped, so I could just add all the navigation properties into a the BaseUnit class.

_

Anyway, I hope this helps.

Cheers,

Ben

---

I think this is where T4 templates would come in handy. Also check out Customizing Entity Classes in VS 2010.