Linq returns too many results when joining

Question

In my schema, I have two database tables. relationships and relationships. I am trying to get all the records from a relationship table that has a specific member, so I need to join it using 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 general repository using code based on the code indicated here .

The problem is that I have 3 entries in the relationship table and 3 entries in the membership table, with each membership being associated with a different relationship. The 2 membership entries are obj_id 2 and the other is 3. I am trying to get a list of all the relationships associated with object # 2.

When this linq is working, it _context.Repository<DBMappings.relationships>()returns the correct 3 records, and _context.Repository<DBMappings.relationship_memberships>()returns 3 records. However, when result.ToList () is executed, the resulting list has 2 problems:

1) The resulting list contains 6 entries, all of type DBMappings.relationships (). Upon further verification, there are 2 relations for each real record, both of which are exact copies of each other.

2) All relations are returned, even if m.obj_id == 3, although the variable objId is correctly passed as 2.

Can anyone understand what is happening because I spent 2 days on this code and I cannot understand what is wrong. I am joining other linq queries that seem to work just fine, and my unit tests show that they still work, so I have to do something wrong with this. I seem to need an extra pair of eyes on this :)

: , , , unit test, unit test , ( ).

, , , .

Answer 1

public IList<DBMappings.relationships> GetRelationshipsByObjectId(int objId) 
{ 
    var results = (from m in _context.Repository<DBMappings.relationship_memberships>() 
                  where m.rel_id==objID
                  select m.relationships).ToList();
    return results.ToList<DBMappings.relationships>(); 
} 

Answer 2

_context.Log = Console.Out SQL-? (, - , console.out, ).

Pz, TaskConnect

Answer 3

, , . , , ?

public IList<DBMappings.relationships> GetRelationshipsByObjectId(int objId)
{
    var mems = _context.Repository<DBMappings.relationship_memberships>();
    var results = mems.Where(m => m.obj_id == objId).Select(m => m.relationships);
    return results.ToList<DBMappings.relationships>();
}

( ):

public IList<DBMappings.relationships> GetRelationshipsByObjectId(int objId)
{
    var mems = _context.Repository<DBMappings.relationship_memberships>();
    var results = mems.Where(m => m.obj_id == objId).SelectMany(m => m.relationships);
    return results.ToList<DBMappings.relationships>();
}

, , .