In my schema I have two database tables. relationships and relationship_memberships. I am attempting to retrieve all the entries from the relationship table that have a specific member in it, thus having to join it with the relationship_memberships table. I have the following method in my business object:
public IList<DBMappings.relationships> GetRelationshipsByObjectId(int objId)
{
var results = from r in _context.Repository<DBMappings.relationships>()
join m in _context.Repository<DBMappings.relationship_memberships>()
on r.rel_id equals m.rel_id
where m.obj_id == objId
select r;
return results.ToList<DBMappings.relationships>();
}
_Context is my generic repository using code based on the code outlined here.
The problem is I have 3 records in the relationships table, and 3 records in the memberships table, each membership tied to a different relationship. 2 membership records have an obj_id value of 2 and the other is 3. I am trying to retrieve a list of all relationships related to object #2.
When this linq runs, _context.Repository<DBMappings.relationships>() returns the correct 3 records and _context.Repository<DBMappings.relationship_memberships>() returns 3 records. However, when the results.ToList() executes, the resulting list has 2 issues:
1) The resulting list contains 6 records, all of type DBMappings.relationships(). Upon further inspection there are 2 for each real relationship record, both are an exact copy of each other.
2) All relationships are returned, even if m.obj_id == 3, even though objId variable is correctly passed in as 2.
Can anyone see what's going on because I've spent 2 days looking at this code and I am unable to understand what is wrong. I have joins in other linq queries that seem to be working great, and my unit tests show that they are still working, so I must be doing something wrong with this. It seems like I need an extra pair of eyes on this one :)
