Linq and Repository pattern -- not getting how to apply Join method

I know this is pretty simple, but I'm just not able to wrap my mind around this. Given a query similar to:

SELECT * FROM Client
LEFT JOIN DriverClient ON DriverClient.ClientID = Client.ClientID
WHERE Client.FullName LIKE '%Tooley%' 
AND (DriverClient.DriverID IS NULL OR DriverClient.DriverID <> 1)

and a repository pattern, how would I implement something in Linq that would approximate this?

I have the LIKE figured out, it's the filter on DriverClient.DriverID I can't fathom. This would apparently involve IEnumerable<T>.Join, but the syntax for this escapes me and I can't google myself up a good example, everyone seems to use the Linq Query syntax, or an example with开发者_StackOverflow社区 pre-filled collection, not an IQueryable.

---

While there is a join construct in LINQ you should not normally need it. The relations that are queried with JOINs in relational databases are presented with properties (either of single object or of collections) in OO programming. As it seems you should have DriverClient.Clients or something similar in your mapping and use this instead of a join.

---

this link could be useful http://msdn.microsoft.com/en-us/vcsharp/ee908647#leftouterjoin, it describes how to make left join with pre-filled collections, but with IQueryable it is the same http://msdn.microsoft.com/en-us/vcsharp/aa336746 - here a lot of usefull examples

---

Try it:

var Client = new[] { new { ClientID = 1, FullName = "Name1" }, new { ClientID = 2, FullName = "Name2_Tooley_3242343" } };
var DriverClient = new[] { new { ClientID = 2, DriverID = 20 }, new { ClientID = 3, DriverID = 30 } };

var result =
    from client in Client
    join driver in DriverClient on client.ClientID equals driver.ClientID into ClientDriver
    from clientDriver in ClientDriver.DefaultIfEmpty()
    where client.FullName.Contains("Tooley") && (clientDriver == null || clientDriver.DriverID != 1)
    select client;

UPDATE

var result2 = Client
  .GroupJoin(DriverClient, client => client.ClientID, driver => driver.ClientID, (client, drivers) => new { client, drivers })
  .Where(clientDrivers => clientDrivers.client.FullName.Contains("Tooley") && clientDrivers.drivers.Count(driver => driver.DriverID != 1) > 0);

---

Without knowing your database structure, some of this is guesswork.

It looks like DriverClient is a simple link table, right? Columns DriverID and ClientID, just used for linking drivers to clients? Assuming that means you've got a *-to-many relationship (one driver has many clients; seems reasonable) you're probably looking for something like this:

var result = DB.Clients
    .Where(c => c.FullName.Contains("Tooley")
        && (c.DriverID == null || c.DriverId != 1));

In general, LINQ tries to hide the detail of joins and things. Instead you get things like navigation properties.

---

The key here is how you're handling the context. If each "Client" manages it's own context in your repository, you won't be able to join across contexts. However, if all repositories use the same underlying context (through DI) then you should be able to join them using either query or lambda syntax as long as you are exposing the objects from your repository as IQueryable rather than IEnumerable/IList/etc.