Linq is returning too many results when joined

Posted by KallDrexx on Stack Overflow See other posts from Stack Overflow or by KallDrexx
Published on 2010-05-09T23:48:05Z Indexed on 2010/05/09 23:58 UTC
Read the original article Hit count: 314

Filed under:
|
|

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 :)

© Stack Overflow or respective owner

Related posts about linq-to-sql

Related posts about c#