How to use method-based queries with lambda expressions

Question

I want to join two tables with lambda expressions: Order and OrderLogistics.

Order looks like this:

public class Order{
    public int ID { get; set; }
}

And OrderLogistics looks like this:

public class OrderLogistics{
    public int ID { get; set; }
    public int OrderID { get; set; }
}

I want to join them with Order.ID and OrderLogistics.OrderID.

I do not understand how the queries in this question will be used. Lambda Expression for join

The only way of writing a query I know is:

IQueryable<Order> listOfRecentOrders = Orders
    .Where(x => x.OrderLogistics.DepartureDate == date
    && x.TypeID != 4
    && x.TypeID != 5
    && x.StatusID != 8
    && x.StatusID != 9
    && x.StatusID != 10
    && x.Customer.ID != null
    && x.IsDeleted == false
    && x.OrderLogistics.DepartureTime >= 100
    && x.OrderLogistics.DepartureTime <= 2400)
    .OrderBy(x => x.OrderLogistics.DepartureTime)
    .Take(8));

I do not get what they are doing with a 'var query'. How do I use that in MVC? The above method is inside my repository and the result will be passed on to the controller.

So what I want is a query where I can use properties of Order AND properties of OrderLogistics. My own query above doesn't work because the relationship "x.OrderLogistics" does not exists.

EDIT: And I have:

IQueryable<Order> listOfRecentOrders = 

And:

var joined2 = from p in Order
    join pType in OrderLogistics
    on p.ID equals pType.OrderID
    select (x => x.Order);

How do I assign joined2 to listOfRecentOrders? I don't see this ANYwhere in all query questions. This is the MOST important part.

PROBLEM: I need to do a query where I JOIN two tables. This I want to put inside a list and pass this list to the controller so I can use the data in my view.

Answer 1

I made the solution myself. Thanks for all the input! Instead of using the select like NetMage told I am putting it in a model.

Why? I want to make my controller as small as possible, so all the querying I am doing in the repository. Then I pass it back to the controller. The controller and/or view do need a model to be able to read the IQueryable. Without a model it would be a anonymous type.

public IQueryable<OrderOrderLogisticsModel> getOrdersOfOneDate(long date)
{
    IQueryable<OrderOrderLogisticsModel> recentOrders = Orders.Join(OrderLogisticss,
        order => order.ID,
        orderlogistics => orderlogistics.OrderID,
        (order, orderlogistics) => new OrderOrderLogisticsModel { Order = order, OrderLogistics = orderlogistics })

        .Where(x => x.OrderLogistics.DepartureDate == date
        && x.OrderLogistics.DepartureTime >= 100
        && x.OrderLogistics.DepartureTime <= 2400
        && x.Order.TypeID != 4
        && x.Order.TypeID != 5
        && x.Order.StatusID != 8
        && x.Order.StatusID != 9
        && x.Order.StatusID != 10
        && x.Order.IsDeleted == false
        && x.Order.Customer.ID != null
        && x.Order.NumberOfCarts > 0)
        .Take(8);

    return recentOrders;
}

Model:

public class OrderOrderLogisticsModel
{
    private Order order;

    public Order Order
    {
        get { return order; }
        set { order = value; }
    }

    private OrderLogistics orderLogistics;

    public OrderLogistics OrderLogistics
    {
        get { return orderLogistics; }
        set { orderLogistics = value; }
    }
}

I like lambda, but if you have other tips for my query I would like to hear them.

Answer 2

IQueryable<Order> listOfRecentOrders = from p in Order
                                       join pType in OrderLogistics on p.ID equals pType.OrderID
                                       select p.Order;

Note that I prefer this given what you are trying to return:

IQueryable<Order> listOfRecentOrders = from p in Order
                                       where OrderLogistics.Select(ol => ol.OrderID).Contains(p.ID)
                                       select p.Order;

The second query using Contains translates to SQL EXISTS and automatically removes duplicates. If you want the duplicates use the first query.