Selecting & Updating Many-To-Many in Entity Framework 4

Question

I am relatively new to the EF and have the following entity model above which consists of an Asset and a Country. An Asset can belong in many countries and thus has a many-to-many relationship with country (has a join table in the database with two fields both as primary keys).

I want to be able to do the following:

Firstly when I retrieve an asset (or assets) from the model I want to get the respective countries that its associated with. I would then like to be able to bind the countries list to an IEnumerable. Retrieving the countries in this way provides me with an EntityCollection of country objects which has extension method for ToList(). Therefore not sure If I am going down the right avenue with this one. Here is my GetAll method:

public IEnumerable<Asset> GetAll()
{
    using (var context = CreateAssetContext())
    {
        var assetEntities = context.Assets.Include("Countries").ToList();
        return AssetMapper.FromEntityObjects(assetEntities);
    }
}

Secondly I want to be able to select a list of countries where the AssetId == some value.

Finally I want to be able to update the list of countries for a given Asset.

Many thanks.

Answer 1

Firstly when I retrieve an asset (or assets) from the model I want to get the respective countries that its associated with. I would then like to be able to bind the countries list to an IEnumerable.

Not sure if I understand that correctly, but EntityCollection<T> implements IEnumerable<T>, so you don't have to do anything special, you just can use Asset.Countries after you have loaded the assets including the countries.

Secondly I want to be able to select a list of countries where the AssetId == some value.

using (var context = CreateAssetContext())
{
    var countries = context.Countries
        .Where(c => c.Assets.Any(a => a.AssetId == givenAssetId))
        .ToList();
}

Or:

using (var context = CreateAssetContext())
{
    var countries = context.Assets
        .Where(a => a.AssetId == givenAssetId)
        .Select(a => a.Countries)
        .SingleOrDefault();
}

The second option is OK (not sure if it's better than the first from SQL viewpoint) because AssetId is the primary key, so there can be only one asset. For querying by other criteria - for example Asset.Name == "XYZ" - where you could expect more than one asset I would prefer the first option. For the second you had to replace Select by SelectMany and SingleOrDefault by ToList and use Distinct to filter out possible duplicated countries. The SQL would probably be more complex.

Finally I want to be able to update the list of countries for a given Asset.

This is more tricky because you need to deal with the cases: 1) Country has been added to asset, 2) Country has been deleted from asset, 3) Country already related to asset.

Say you have a list of country Ids ( IEnumerable<int> countryIds ) and you want to relate those countries to the given asset:

using (var context = CreateAssetContext())
{
    var asset = context.Assets.Include("Countries")
        .Where(a => a.AssetId == givenAssetId)
        .SingleOrDefault();

    if (asset != null)
    {
        foreach (var country in asset.Countries.ToList())
        {
            // Check if existing country is one of the countries in id list:
            if (!countryIds.Contains(country.Id))
            {
                // Relationship to Country has been deleted
                // Remove from asset's country collection
                asset.Countries.Remove(country);
            }
        }
        foreach (var id in countryIds)
        {
            // Check if country with id is already assigned to asset:
            if (!asset.Countries.Any(c => c.CountryId == id))
            {
                // No:
                // Then create "stub" object with id and attach to context
                var country = new Country { CountryId = id };
                context.Countries.Attach(country);
                // Then add to the asset's country collection
                asset.Countries.Add(country);
            }
            // Yes: Do nothing
        }
        context.SaveChanges();
    }
}

Edit

For the price of a second roundtrip to the database you can probably use this simpler code:

using (var context = CreateAssetContext())
{
    var asset = context.Assets.Include("Countries")
        .Where(a => a.AssetId == givenAssetId)
        .SingleOrDefault();

    if (asset != null)
    {
        // second DB roundtrip
        var countries = context.Countries
            .Where(c => countryIds.Contains(c.CountryId))
            .ToList();

        asset.Countries = countries;

        context.SaveChanges();
    }
}

EF's change detection should recognize which countries have been added or deleted from the asset's country list. I am not 100% sure though if the latter code will work correctly.

Answer 2

Whats the specific question here? Are you not being able to do that?

do you want to select the countries in an asset or the countries that have a certain asset?

to update its simple, just change stuff and then context.SaveChanges() will commit to the database.