Linq is returning too many results when joined

Question

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

Edit: Ok so it seems like the whole issue was the way I designed my unit test, since the unit test didn't actually assign ID values to the records since it wasn't hitting sql (for unit testing).

Marking the answer below as the answer though as I like the way he joins it all together better.

Answer 1

How about to set _context.Log = Console.Out just to see the generated SQL query? Share the output with us (maybe use some streamwriter instead of console.out so that you can copy that easily and without mistakes).

Pz, the TaskConnect developer

Answer 2

I might have this backwards, but I don't think you need a join here. If you've setup your foreign keys correctly, this should work, right?

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>();
}

Here's the alternative (if I've reversed the mapping in my brain):

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>();
}

Let me know if I'm way off with this, and I can take another stab at it.

Answer 3

Just try like this

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>(); 
}