First some introduction about how to design table relations in entity framework. Then back to your question
Table Relations in Entity Framework
Apparently there is a relation between your Requests and your Orders. This seems to be a one-to-many relation: every Order has zero or more Requests, every Requests belongs to exactly one Order using foreign key OrderId.
In entity framework the columns of your tables are represented using non-virtual properties; The relations between the tables are represented using virtual properties (virtual, because they are no real columns in your tables).
If you designed your one-to-many relationship according to the Entity Framework Code First Conventions you would have classes like:
class Order
{
public int Id {get; set;}
// every order has zero or more requests (one-to-many)
public virtual ICollection<Request> Requests {get; set;}
public string Name {get; set;}
...
}
class Request
{
public int Id {get; set;}
// Every Request belongs to exactly one Order using foreign key
public int OrderId {get; set;}
public virtual Order Order {get; set;}
public string Name {get; set;}
...
}
This is enough for entity framework to detect your one-to-many relationship. Your column names or table names might differ, but you get the gist. If you want different column / table names in your classes than your tables, use fluent API or attributes. See the link above.
Did you notice that the foreign key is not declared virtual: it is a real column in your table. The two virtual properties are not existing values, they describe the relation between the tables.
For every one-to-one / one-to-many / many-to-many relation the one side is described as follows:
// Every MyClass object belongs to exactly one YourClass object using foreign key:
public int YourClassId {get; set;}
public virtual YourClass YourClass {get; set;}
The many side is described as:
// Every MyClass has zero or more YourClass objects
public virtual ICollection<YourClass> YourClasses {get; set;}
So a many-to-many has a virtual ICollection<...> on both involved classes.
Back to your Question
Having designed your table relations properly, your query will be easy. You don't have to use a join. Use the virtual relations:
Give me all (or some) Orders with all (or some) of their Requests
var result = dbContext.Orders
.Where(order => ...) // give me only the orders that ...
.Select(order => new // from every remaining order make one new object
{ // select only the properties you plan to use
Id = order.Id,
Name = order.Name,
...
Requests = order.Requests
.Where(request => ...) // give me only the requests that ...
.Select(request => new
{ // again: select only the properties you plan to use
Id = request.Id,
Name = request.Name,
...
// not needed: OrderId = request.OrderId, you already know the value!
})
.ToList(),
});
Entity framework is smart enough to know that a (group)join is needed.
The other way round:
Give me all (or some) Requests with their Orders
var result = dbContext.Requests
.Where(request => ...) // take only the requests that ...
.Select(request => new // from every remaining request make one new object
{ // with only the properties you plan to use
Id = request.Id,
Name = request.Name,
Order = new // from the one and only Order of this Request
{ // take only the properties you plan to use
Name = request.Order.Name,
...
}
});
Entity Framework is smart enough to know that a join is needed
But I really like my Joins!
Well, if you really want to do design your joins yourself, you can do the following:
var result = dbContext.Orders
.Join(dbContext.Requests, // Join Orders with Requests
order => order.Id, // from every Order take the Id
request => request.OrderId, // from every Request take the OrderId
(order, request) => new // when they match, take the order and the request
{ // to make a new object with properties
OrderId = order.Id,
OrderName = order.Name,
RequestName = request.Name,
});
Consider a more readable solution by splitting the Order and Request properties:
(order, request) => new
{
Order = new
{
Id = order.Id,
Name = order.Name,
},
Request = new
{
Id = request.Id,
Name = request.Name,
},
})
If you want an Order with its Requests, use a GroupJoin instead of a Join
var result = dbContext.Orders
.GroupJoin(dbContext.Requests, // GroupJoin Orders with Requests
order => order.Id, // from every Order take the Id
request => request.OrderId, // from every Request take the OrderId
(order, requests) => new // when they match, take the order and
{ // all matching requests to make one new object
...
})
