EF 4 CTP 5 Complex query

Question

I have a model like the following:

public class Customer
{
    public int Id { get; set; }

    public string Name { get; set; }

    public ICollection<Order> Orders { get; set; }
}

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

    public DateTime DateTime { get; set; }

    public Customer Customer { get; set; }

    public ICollection<OrderLine> OrderLines { get; set; }
}

public class OrderLine
{
    public int Id { get; set; }

    public Product Product { get; set; }

    public int Price { get; set; }

    public int Quantity { get; set; }
}

public class Product
{
    public int Id { get; set; }

    public string Name { get; set; }

    public Category Category { get; set; }
}

public class Category
{
    public int Id { get; set; }

    public string Name { get; set; }
}

I am using this infrastructure.

My aggregate roots are Customer, Order, Product. I did not include the mappings here as they are straight forward.

var customers = unitOfWork.Customers.FindAll();
var orders = unitOfWork.Orders.FindAll();
var products = unitOfWork.Products.FindAll();

var query = ......

Using LINQ, how would you select all customers that have orders for products in the "Beverages" category?

All samples I have seen on the web are very basic queries nothing advanced.

Your product does not have category information. – Oleg Rudckivsky Feb 17 '11 at 11:21 — Oleg Rudckivsky, Feb 17 '11 at 11:21
Where is the reference from Product to Category? – EgorBo Feb 17 '11 at 11:22 — EgorBo, Feb 17 '11 at 11:22

Sergey Vedernikov · Answer 1 · 2011-02-17T11:28:48.227

1

i found http://msdn.microsoft.com/en-us/vbasic/bb737909

May be your query should look like:

from c in unitOfWork.Customers
join o in unitOfWork.Orders on o.Customer = c
join ol in unitOfWork.OrderLines on ol.Order = o
where ol.Product.Category.Name == "Beverages"
select c

And it is necessary to add all parent-object-properties

edited Feb 17 '11 at 11:28

answered Feb 17 '11 at 11:20

Sergey Vedernikov

7,609
2
25
27

will this not generate Cross joins ? Yet to try though. . – Sumanta Feb 17 '11 at 11:23
Orderline donlt have Order property so this "join ol in unitOfWork.OrderLines on ol.Order = o" will not work – Sumanta Feb 17 '11 at 11:24
its needed to add all parent-object-properties – Sergey Vedernikov Feb 17 '11 at 11:25
is there any way to do it without the parent properties? – Cosmin Onea Feb 17 '11 at 11:59
without parent properties you can`t use join. in that case you must create separate queries. its uncomfortably and more code – Sergey Vedernikov Feb 17 '11 at 12:02

score 0 · Answer 2 · answered Feb 17 '11 at 11:28

0

This might work or not:

from customer in customers
where customer.Orders.Any(
      o => o.OrderLines.Any(l => l.Product.Category.Name == "Beverages")
select customer

(I'm assuming you forgot the relationship between Product and Category)

answered Feb 17 '11 at 11:28

Diego Mijelshon

52,548
16
116
154

EF 4 CTP 5 Complex query

2 Answers2