How to: Manage Many-to-Many Associations

Suppose, you have the following model (it is based on the Northwind database). The model includes two entities related with a many-to-many association. The Customer entity has many instance of CustomerDemographic. You can use the Customer.CustomerDemographic property to access the CustomerDemographic instances that are associated with an instance of the Customer entity. The CustomerDemographic entity has many instances of Customer. You can use the CustomerDemographic.Customer property to access the Customer instances that are associated with an instance of the CustomerDemographic entity.

In this scenario, you need to mark the two navigation properties as managed in order to be able to consume the association:

customerConfiguration.HasAssociation(x => x.CustomerDemographics).
    HasFieldName("_customerDemographics").WithOpposite(x => x.Customers).
    IsManaged().WithDataAccessKind(DataAccessKind.ReadWrite).
    MapJoinTable("CustomerCustomerDemo", 
                 (x, y) => new{CustomerID = x.CustomerID, 
                               CustomerTypeID = y.CustomerTypeID}).
    CreatePrimaryKeyFromForeignKeys();
customerDemographicsConfiguration.HasAssociation(x => x.Customers).
    HasFieldName("_customers").WithOpposite(x => x.CustomerDemographics).
    IsManaged().WithDataAccessKind(DataAccessKind.ReadWrite);

customerConfiguration.HasAssociation(x => x.CustomerDemographics).
    HasFieldName("_customerDemographics").WithOpposite(x => x.Customers).
    IsManaged().WithDataAccessKind(DataAccessKind.ReadWrite).
    MapJoinTable("CustomerCustomerDemo", 
                 (x, y) => new{CustomerID = x.CustomerID, 
                               CustomerTypeID = y.CustomerTypeID}).
    CreatePrimaryKeyFromForeignKeys()
customerDemographicsConfiguration.HasAssociation(x => x.Customers).
    HasFieldName("_customers").WithOpposite(x => x.CustomerDemographics).
    IsManaged().WithDataAccessKind(DataAccessKind.ReadWrite)

Below you can find how to insert and retrieve objects from domain entities related through a many-to-many association:

Insert Related Objects
Read Related Objects

In case you need to insert a new customer with the related demographic data, you can do it like this:

Set Customer.CustomerDemographic and CustomerDemographic.Customer as managed in their mapping configuration.

Here is a complete code snippet showing you how to set a new relation by using the collection property. Note that it is recommended to add the new objects to the context first, and then set the relation through the collection property. The final step is to invoke the SaveChanges() method.

using (FluentModel dbContext = new FluentModel())
{
    Customer newCustomer = new Customer()
    {
        CustomerID = "RDSTT",
        CompanyName = "Alfreds Futterkiste",
        ContactName = "Maria Anders",
        ContactTitle = "Sales Representative",
        Address = "Obere Str. 57",
        City = "Berlin",
        PostalCode = "12209",
        Country = "Germany",
        Phone = "030-0074321",
        Fax = "030-0076545"
    };
    CustomerDemographic newCustomerDemographic = new CustomerDemographic()
    {
        CustomerTypeID = "1",
        CustomerDesc = "Regular Customer"
    };
    dbContext.Add(newCustomer);
    newCustomer.CustomerDemographics.Add(newCustomerDemographic);
    dbContext.SaveChanges();
}

Using dbContext As New FluentModel()
    Dim newCustomer As New Customer() With
    {
        .CustomerID = "RDSTT",
        .CompanyName = "Alfreds Futterkiste",
        .ContactName = "Maria Anders",
        .ContactTitle = "Sales Representative",
        .Address = "Obere Str. 57",
        .City = "Berlin",
        .PostalCode = "12209",
        .Country = "Germany",
        .Phone = "030-0074321",
        .Fax = "030-0076545"
    }
    Dim newCustomerDemographic As New CustomerDemographic() With
    {
        .CustomerTypeID = "1",
        .CustomerDesc = "Regular Customer"
    }
    dbContext.Add(newCustomer)
    newCustomer.CustomerDemographics.Add(newCustomerDemographic)
    dbContext.SaveChanges()
End Using

If you don't set IsManaged, you may persist two new records in the database, respectively in the Customers and CustomerDemographics tables without relating them.

After the call to the SaveChanges() method both objects are saved in the database and a new row is added in the CustomerCustomerDemo table.

Note that in this case only the newCustomer is added to the context and newCustomerDemographic is added to the navigation property. This is called persistence by reachability, which means that if you build a graph of related objects, adding the top most one to the context will cause the context to track the rest too.

Since the value of IsManaged is synchronized between the two navigational properties of the association, you can achieve the same result by adding to the context the newCustomerDemographic object and adding newCustomer to the CustomerDemographic.Customer collection.

For general information about inserting objects you can check the How to: Insert Objects article.

The next example demonstrates how to retrieve all CustomerDemographic objects related to a given Customer object.

using (FluentModel dbContext = new FluentModel())
{
    Customer someCustomer = dbContext.Customers.
        FirstOrDefault(c => c.CustomerID == "SFTRT");
    if (someCustomer != null)
    {
        IList<CustomerDemographic> demographics =  
            someCustomer.CustomerDemographics;
    }
}

Using dbContext As New FluentModel()
    Dim someCustomer As Customer = dbContext.Customers. _
        FirstOrDefault(Function(c) c.CustomerID = "SFTRT")
    If someCustomer IsNot Nothing Then
        Dim demographics As IList(Of CustomerDemographic) =  _
            someCustomer.CustomerDemographics
    End If
End Using

For general information about retrieving objects you can check the How to: Query Data article.

How to: Manage Many-to-Many Associations

Insert Related Objects

Read Related Objects