How to query (LINQ) multiple table association link ?

Question

Hi there

I have tables association such as (CaseClient is a bridge table):

• Cases has many CaseClients
• Client has many CaseClients
• ClientType has many CaseClient

The easiest way just use the view in database but I heard that with linq you can join this somehow? Or should I just created view in the database and linq query agains that view?

I am appreciated your comment

Answer 1

I think you want to use the Join method, from your bridging table and resolving each of your relationships. E.g.

// Where CaseId and TypeId are your members of CaseClient
var x = caseClients.Join( cases, cc => cc.CaseId, c => c.Id)
                                .Join( types, cc => cc.TypeId, t => t.Id)
                                .Select();

Above code untested (so far) and from memory. You may need to put a Select between the two joins.

Answer 2

Heres an adaptation of what I did for a very similar situation. Only the names have been changed to protect the innocent.

IEnumerable<Case> getCaseByClient(int client_id)
{
    var ret = from c in Cases
              join cc in CasesClients
                on c.Id equals cc.ClientId
              join cl in Clients
                on cc.ClientId equals client_id
              select c;
    return ret;
}

of course this assumes your client_id field is an int, but thats easy enough to modify.