0

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.

Nash Carp
  • 169
  • 12
  • Just replace `People` with `Order`, `PeopleTypes` with `OrderLogistics`, `PersonType` with `ID` and `TypeID` with `OrderID`... and in the end add `Select( x => x.Order )` before `Take(8)`. – Haukinger Oct 09 '17 at 10:26
  • Possible duplicate of [Lambda Expression for join](https://stackoverflow.com/questions/5038288/lambda-expression-for-join) – Haukinger Oct 09 '17 at 10:26
  • I am trying this now Haukinger. But it is not a duplicate. I am also asking how to implement this in MVC I kind of get the query but how do I use joined or joined2? – Nash Carp Oct 09 '17 at 10:48
  • `How do I assign joined2 to listOfRecentOrders?` - either using `=` if types match or not at all (if they don't match). After the join you have an `IEnumerable` and with the select you extract the orders from that, so you get an `IEnumerable`. What exactly is the problem? – Haukinger Oct 09 '17 at 11:00
  • Querying the database is not really MVC specific, please don't mix to many topics together. Also it would help if you present a specific (and short) problem to be solved instead of writing some question code without clearly explaining your goal. (Actually, I think you have some specific question, but it needs some highlighting so everyone can distinguish your question from your solution attempts) – grek40 Oct 09 '17 at 11:02
  • Can you please specify the desired result type of your query? Also, in case you use entity framework with navigation properties, you probably won't need to write a manual join at all. – grek40 Oct 09 '17 at 12:11
  • I am using navigation properties. But in this case it are two tables with a one to one relation (yes that isn't possible, so it becomes a one to zero-or-one relation). This means that I could make one table of them. But I am programming for a system with a very old and big database. So I do need manual joins in this case. – Nash Carp Oct 10 '17 at 09:54

2 Answers2

1
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.

NetMage
  • 26,163
  • 3
  • 34
  • 55
0

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.

Nash Carp
  • 169
  • 12