Simple.Data Many-to-many Issues

Question

So I'm working through a simplified example of my hoped-for database that has the following tables:

Contractors: Id, ContractorName
Types: Id, TypeName
CoverageZips: ContractorId, Zip
TypesForContractors: ContractorId, TypeId

where contractors can have many zips and types and types and zips can have many contractors (many-to-many).

I'm trying to:

1. do a search for contractors in a certain zip code
2. then load the types for those contractors.

The SQL for the first part would probably look like:

SELECT * FROM dbo.Contractors WHERE Id IN
(SELECT ContractorId FROM dbo.CoverageZips WHERE Zip = 12345)

Here's what I have for the first part in Simple.Data. It's working, but I feel like I'm missing some of the beauty of Simple.Data...

List<int> contractorIds = new List<int>();
foreach(var coverage in _db.CoverageZips.FindAllByZip(zip)) {
    contractorIds.Add((int)coverage.ContractorId);
}
var contractors = new List<dynamic>();
if (contractorIds.Count > 0) {
    contractors = _db.Contractors.FindAllById(contractorIds).ToList<dynamic>();
}
return contractors;

That's working ok until I try part 2:

public dynamic GetAllForZip(int zip) {
    List<int> contractorIds = new List<int>();
    foreach(var coverage in _db.CoverageZips.FindAllByZip(zip)) {
        contractorIds.Add((int)coverage.ContractorId);
    }
    var contractors = new List<dynamic>();
    if (contractorIds.Count > 0) {
        contractors = _db.Contractors.FindAllById(contractorIds).ToList<dynamic>();
    }
    foreach (var contractor in contractors) {
        // Exception occurs here on second iteration
        // even though the second contractor was originally in the contractors variable
        contractor.types = GetTypesForContractor((int)contractor.Id);
    }
    return contractors;
}

public dynamic GetTypesForContractor(int id) {
    var types = new List<dynamic>();
    if (id > 0) {
        List<int> typeIds = new List<int>();
        foreach (var typeForContractor in _db.TypesForContractor.FindAllByContractorId(id)) {
            typeIds.Add((int)typeForContractor.TypeId);
        }

        if (typeIds.Count > 0) {
            types = _db.ContractorTypes.FindAllById(typeIds).ToList<dynamic>();
        }
    }
    return types;
}

I set a breakpoint and everything works ok for the first iteration showing , but is failing on the second with the following exception:

Index was out of range. Must be non-negative and less than the size of the collection.

tl;dr

I'm not sure how to properly use many-to-many relationships with Simple.Data and something weird is happening when I try my method more than once

Answer 1

I don't know what's happening with that exception and will investigate today.

You are missing some beauty, though. Assuming you have referential integrity configured on your database (which of course you do ;)), your methods can be written thus:

public dynamic GetAllForZip(int zip) {
    var contractors = _db.Contractors
        .FindAll(_db.Contractors.ContractorZips.Zip == zip)
        .ToList();

    foreach (var contractor in contractors) {
        contractor.Types = GetTypesForContractor((int)contractor.Id);
    }
    return contractors;
}

public dynamic GetTypesForContractor(int id) {
    return _db.ContractorTypes
        .FindAll(_db.ContractorTypes.TypesForContractor.ContractorId == id)
        .ToList();
}

Update!

As of 1.0.0-beta3, eager-loading across many-to-many joins is supported, so now you can do this:

public dynamic GetAllForZip(int zip) {
    return _db.Contractors
        .FindAll(_db.Contractors.ContractorZips.Zip == zip)
        .With(_db.Contractors.TypesForContractor.ContractorTypes.As("Types"))
        .ToList();
}

And that executes as a single SQL select to make your DBA happy like rainbow kittens.