DevForce Community Forum : Joins

Joins : Sorry, Michael, I'm not familiar...

Tue, 29 Dec 2009 12:15:11 -700

Author: GregD
Subject: 1591
Posted: 29-Dec-2009 at 12:15pm

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.

Joins : Thanks Greg for that nice explanation...

Tue, 29 Dec 2009 04:38:42 -700

Author: mbevins1
Subject: 1591
Posted: 29-Dec-2009 at 4:38am

Thanks Greg for that nice explanation however I am still having issues with the in clause part. My "helper" function is the BuildContainsExpression function found on microsofts website (found in one of the above links). I have not been able to figure out how to get it to work with the "Any" part?

List tempOperationList = new List();
tempOperationList.Add(1);
tempOperationList.Add(4);
tempOperationList.Add(7);

IEntityQuery search = _mgr.CustomerLocations
.Where(cl=>cl.Territory.OperationsTerritories.Any(

BuildContainsExpression(e => e.Operation.OperationID, tempOperationList)

));

Joins : Your issue is a basic one, so...

Mon, 28 Dec 2009 11:52:04 -700

Author: GregD
Subject: 1591
Posted: 28-Dec-2009 at 11:52am

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:

It's the only database that we (and others who will read this post) have in common.
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 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 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 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' 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 search = _mgr.CustomerLocations
        .Where(cl=>cl.Territory.OperationsTerritories.Any(ot=>ot.Operation.OperationID == opID));

Joins : Greg, sorry but I am still having...

Mon, 28 Dec 2009 04:53:20 -700

Author: mbevins1
Subject: 1591
Posted: 28-Dec-2009 at 4:53am

Greg, sorry but I am still having problems with this. In your examle, you go from Product to Supplier to city. I can not do that in my query. When I get to operation territories, is ask for another where clause since it is a related entity list. I am guessing you can only have one supplier where I can have multiple operation territories.

In the second where clause, I tried adding the build contains expression (in clause) and I get an error:

IEntityQuery search = EntityManager.CustomerLocations.Where(
cl => cl.Territory.OperationTerritories.Where(
Helper.BuildContainsExpression
(ot => ot.Operation.OperationID, CommonUser.CurrentUserOperationIDs)));

The type arguments for method 'System.Linq.Enumerable.Where(System.Collections.Generic.IEnumerable, System.Func)' cannot be inferred from the usage. Try specifying the type arguments explicitly.

I even tried it without the BuildContainsExpression but I can not figure it out because it is looking for an = somewhere but i cannot seem to figure out how to add it.

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

I am still do not understand how I can do this using "method syntax"? Using my tables, can you help me build what you beleive the syntax should be?

Joins : Greg, I think I finally understand...

Sat, 26 Dec 2009 05:28:02 -700

Author: mbevins1
Subject: 1591
Posted: 26-Dec-2009 at 5:28am

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

Joins : Mike, my sample queries access...

Thu, 24 Dec 2009 12:41:46 -700

Author: GregD
Subject: 1591
Posted: 24-Dec-2009 at 12:41pm

Mike, my sample queries access two tables: OrderDetails and Product. The join is implicit. I could easily make it three; e.g.,

IEntityQuery search3 =
_mgr.OrderDetails.Where(od => od.Product.Supplier.City == "Vancouver");

which would get me all the OrderDetails that refer to Products whose Suppliers are in Vancouver. If I had something else related to Supplier I could extend to four tables, and on out to however far the relationships reach.

You're doing the same thing, unless I'm completely out to lunch (which I will not say has never happened). You want customers subject to a condition on a related entity. It doesn't matter if the related entity is one step away or 16, as long as there exists a chain of relationships. You can walk the property path.

In any event, it's not DevForce that's at issue here, it's LINQ to Entities. Since joins are supported, you should be able to write a query with joins if you want to, and still use the PredicateBuilder to create the part that compensates for the non-support of In in LINQ to Entities. I just never find the need for joins myself, and in fact rarely use query syntax (as opposed to method syntax) for my queries, so I'm just not very good at doing them that way.

Happy holidays!

Joins : Actually, I was wrong, there are...

Wed, 23 Dec 2009 15:53:15 -700

Author: mbevins1
Subject: 1591
Posted: 23-Dec-2009 at 3:53pm

Actually, I was wrong, there are 4 tables.

CustomerLocation-->Territory-->OperationTerritory-->Operation

I need to get all customers that are part of an operation.

A customer is part of a territory (customerlocation.territoryid) then the operationterritory contains which territories an operation is part of. In order to query this, the query needs to create joins. It appears that deforce supports joins (.Join<>) however I can not figure it out.

Joins : Please read my original post,...

Wed, 23 Dec 2009 15:14:47 -700

Author: mbevins1
Subject: 1591
Posted: 23-Dec-2009 at 3:14pm

Please read my original post, these are nothing like my query. My query has 3 tables that must be joined. Customerlocation, operationterritory and operation. A customer is part of a territory, we need to pull all customers that are part of an operation so the must be joined.

Joins : You don't need a join. The...

Wed, 23 Dec 2009 14:44:33 -700

Author: GregD
Subject: 1591
Posted: 23-Dec-2009 at 2:44pm

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

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

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

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

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

Joins : Yes, I have seen that thread but...

Wed, 23 Dec 2009 10:49:59 -700

Author: mbevins1
Subject: 1591
Posted: 23-Dec-2009 at 10:49am

Yes, I have seen that thread but how do you use it with the query above that has a join statement?

Joins : This thread describes a work-around...

Wed, 23 Dec 2009 10:45:07 -700

Author: GregD
Subject: 1591
Posted: 23-Dec-2009 at 10:45am

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.

Joins : Do you have an example on how...

Fri, 18 Dec 2009 04:35:10 -700

Author: mbevins1
Subject: 1591
Posted: 18-Dec-2009 at 4:35am

Do you have an example on how to use Joins. For example:

IEntityQuery search = EntityManager.CustomerLocations;
search = search.Join ... This is where I am stuck.

My Linq would look something like this but it will not work because of the in statement

IEntityQuery search = (from customerLocation in EntityManager.CustomerLocations
join operationTerritory in EntityManager.OperationTerritories on customerLocation.Territory.TerritoryID equals operationTerritory.Territory.TerritoryID
where operationTerritory.Operation.OperationID IN (0,1,2)
select customerLocation);