CODEWITHSUNDEEP
c#joinef-code-firstlinq-to-entities
Masoud
Masoud

Reputation: 8171

Right outer join in linq to entities query

Each Order make in some RoutingSteps, each step contains an Operation and each operation do in a Workshop.

public class Order
{
   public int Id {get; set;}
   public string OrderNumber {get; set;}
   public virtual collection<Routing> Routings{get; set;}
}
public class Routing
{
   public int Id {get; set;}
   public virtual Order Order {get; set;}
   public virtual Operation Operation{get; set;}
}
public class Operation
{
   public int Id {get; set;}
   public virtual Workshop Workshop{get; set;}
}
public class Workshop
{
   public int Id {get; set;}
   public string Title {get; set;}
}

I want to write a linq to entity query(method syntax) to know each order pass which Workshops and doesn't pass which Workshops. In the other word if I have 4 Workshops totally and O1 contains 3 Routing steps, I want following result for O1:

------------------------------
| Order  |  Workshop | status |
------------------------------
|   O1   |   W1      |  pass  |
------------------------------
|   O1   |   W2      |  pass  |
------------------------------
|   O1   |   W3      |not pass|
------------------------------
|   O1   |   W4      |  pass  |
-------------------------------

I write following code 

var query = db.Orders
    .SelectMany(order=> order.Routings
    .Select(g => new
    {
        Number = g.Order.OrderNumber,
        Workshop = g.Operation.Workshop.Title,
        Status = g.????
    }).ToList();

Which code should I use instead "???" ?

Upvotes: 0

Views: 651

Answers (1)

devuxer
devuxer

Reputation: 42354

var query = db.Orders
    .SelectMany(x => db.Workshops, (x, y) => new { Order = x, Workshop = y }) // all combinations of orders with workshops
    .GroupJoin( // left join to determine whether each combination actually exists
        db.Routings,
        x => x,  // proposed order-workshop pair
        y => new { Order = y.Order, Workshop = y.Operation.Workshop }, // existing order-workshop pair
        (x, ys) => new
        {
            x.Order.OrderNumber,
            x.Workshop.Title,
            Status = ys.Any() ? "pass" : "not pass"
        });

Upvotes: 1

Related Questions