Joins

This thread describes a work-around for LINQ to Entities' non-support of IN:

http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/thread/095745fe-dcf0-4142-b684-b7e4a1ab59f0/

*** BUT *** ...before you get too absorbed in that, see this thread:

http://www.ideablade.com/forum/forum_posts.asp?TID=1462&PID=5980#5980

and the section "The DevForce Predicate Builder" in the Business Object Persistence topic document in the Learning Resources.

You don't need a join. The following three queries return the same results:

      IEntityQuery<OrderDetail> search =
        from anOrderDetail in _mgr.OrderDetails
        join aProduct in _mgr.Products
        on anOrderDetail.ProductID equals aProduct.ProductID
        where aProduct.UnitPrice > 50
        select anOrderDetail;

      IEntityQuery<OrderDetail> search2 =
        from anOrderDetail in _mgr.OrderDetails
        where anOrderDetail.Product.UnitPrice > 50
        select anOrderDetail;

      IEntityQuery<OrderDetail> search3 =
        _mgr.OrderDetails.Where(od => od.Product.UnitPrice > 50);



Edited by GregD - 23-Dec-2009 at 2:47pm

Actually, I was wrong, there are 4 tables.

Greg, I think I finally understand now.  Not to make excusses for my mindlessness but I have a one month old at home and haven't been getting a whole lot of sleep.  Now that I have gotten a little sleep it makes since now why I wouldn't need to use joins.  That is how I got it to work using normal sql and was on a one track mind thinking that was the only way.  Thanks for pointing me in the right direction.

Happy Holidays to you too.

Mike

Your issue is a basic one, so let's keep Helper, etc., out of it for now.

I try to use the NorthwindIB database when experimenting with and communicating about queries, for the following reasons:

1. It's the only database that we (and others who will read this post) have in common. 
2. It's a database that I actually have, and so can build an actual model against it, and so can do actual queries against, instead of just guessing or trying to reason out what will work.

Once I've worked out a query, I can try to translate it to your database, but will have to guess as the associations between your tables until and if you give me a detailed account (which I'd prefer you didn't unless absolutely necessary).

Associations in NorthwindIB are as follows:
------------------------------------------------------
Customer     -----o  Order
Employee     -----o  Order
Order           -----o  OrderDetail
OrderDetail  o-----  Product
Product        o-----  Supplier

A Customer places many Orders: an Employee writes many Orders. An Order has many OrderDetails (line items). An OrderDetail references a single Product, but one Product can of course be represented in many OrderDetails. A Product has a single Supplier, but a Supplier may supply many Products.

Those are the six tables I use for most examples; however, I need to use an additional one in order to have a set of associations similar to what I think yours are:

Employee     -----o  EmployeeTerritories


Let's consider your query:

   IEntityQuery<CustomerLocation> search = EntityManager.CustomerLocations
        .Where(cl => cl.Territory.OperationTerritories
        .Where(ot => ot.Operation.OperationID == 1));

If I guess the associations from the plurality of the navigation properties, I can state this query in English as follows:

"Show me all CustomerLocations for which any related OperationTerritory's Operation is the one identified by ID=1."  I also guess the following associations:

CustomerLocation   o-----  Territory
Territory                  -----o  OperationTerritories
OperationTerritory  o-----  Operation

Again, if I've guessed the details of the associations correctly, this is equivalent to the following query against NorthwindIB:

   IEntityQuery<EmployeeTerritory> search = EntityManager.EmployeeTerritories
        .Where(et => et.Employee.Orders    
        .Where(o => o.Customer.CustomerID == 1));

"Show me all EmployeeTerritories for which any related Order's Customer is the one identified by ID=1."

I'm going to use a variable, custID to hold the ID value that I need to match:

   IEntityQuery<EmployeeTerritory> search = EntityManager.EmployeeTerritories
        .Where(et => et.Employee.Orders    
        .Where(o => o.Customer.CustomerID == custID));

(I need to do that because, in NorthwindIB, Customer.CustomerID is a Guid. But that's just an ancillary detail.)


On the above query I get the following compile time error (last part of query highlighted by Visual Studio):

     Cannot implicitly convert type '' to System.Collections.Generic.IEnumerable<DomainModel.Order>' to 'bool'.

If I've translated your query correctly, you should be getting that same error (with equivalent types).


So what is the problem?

Well, LINQ expects a Where expression to evaluate to a boolean; so it doesn't know what to do with the expression
    
     et => et.Employee.Orders.Where(o => o.Customer.CustomerID == custID)

which returns a collection of Orders.

It literally doesn't know what you're asking it to retrieve.


On the other hand, the following query works:

      var empTerritoriesQuery = _mgr.EmployeeTerritories
        .Where(et=>et.Employee.Orders.Any(o=>o.Customer.CustomerID == custID));

That's because the expression

     et=>et.Employee.Orders.Any(o=>o.Customer.CustomerID == custID)

does evaluate to a boolean.

So I think that's what you need. Your equivalent would be

      IEntityQuery<CustomerLocation> search = _mgr.CustomerLocations
        .Where(cl=>cl.Territory.OperationsTerritories.Any(ot=>ot.Operation.OperationID == opID));

Sorry, Michael, I'm not familiar with the BuildContainsExpression() function. I believe the DevForce PredicateBuilder will do what you need, however, and it is well documented.