2

Here's the code I'm having trouble with. I'm using an EDMX modeled from an existing database. 

// All orders completely shipped Grouped by RefId

                var RefIdsWithAllShippedOrders = mydbcontext.OrderDetails
                    .Where(s => s.Application.CustomerID == "MSFT")
                    .GroupBy(o => o.RefId)
                    .Where(t => t.All(i => i.Status.Description.ToUpper() == "SHIPPED"))
                    .Select(g => g.Key);

   // Iterate through the RefIds
    foreach (var refid in RefIdsWithAllShippedOrders)
                {

               // Gather all the orders that have the same RefIds
               var OrdersForThisRefid = (from o in mydbcontext.OrderDetails
                                        where o.RefId == refid
                                        select o).AsEnumerable();

 //gather all the orders with at least one Canadian recipient
 var orderswithcandianrecipients  = from o in OrdersForThisRefId
                                   where o.OrderRecipients.All( w=> w.Country.Trim().ToUpper() == "CANADA") // ****                                            
                                   select o;
             // Print RefIds of the orders that have at least one Canadian recipient
             foreach (var eachorder in orderswithcandianrecipients)
             {
                  Console.WriteLine(eachorder.RefId);
             }

      }

Here's the schema I have:

ORDERDETAILS

 RefId      OrderId (PK)
 ABC001     00001
 ABC001     00002
 ABC001     00003
 ABC002     00004
 ABC002     12355

ORDER RECIPIENTS

 PK     OrderID (FK)    NAME         COUNTRY
 1      00001           LINCOLN      USA
 2      00001           JEFFERSON    USA
 3      00001           WASHINGTON   CANADA
 4      00001           FRANKLIN     USA
 5      00002           GRANT        USA
 6      00002           WILSON       USA
 7      12355           FORD         CANADA
 8      12355           JOHNSON      USA

The result I'm hoping to get is a var type that contains orders that have at least one Canadian recipient. In the above example, that would be Orders with OrderID = 00001 and 12355

The code, it seems to not respect the Where and All filter that I have marked with *. It returns all orders. Please help me understand what am I doing wrong. Thank you so much.

FMFF
  • 1,652
  • 4
  • 32
  • 62

2 Answers2

4

I think you want Any not All. By using All you are saying all recipients on the order must be Canadian. Any will give you orders that have at least one Canadian recipient.

Another warning about All. It does not look for all items to pass the condition, it looks for the first item that fail the condition. Therefore, if you have zero items, you do not have any that fail the condition, and All will always return true

cadrell0
  • 17,109
  • 5
  • 51
  • 69
  • Thank you @cadrell0. But why does it return all orders, even those that don't have any Canadian recipients? – FMFF Jan 19 '12 at 16:26
  • 1
    I've updated my answer. I can't explain Order 00002, but it should explain the others. – cadrell0 Jan 19 '12 at 16:28
  • You are correct. This is QA/Development database, and I don't even have recipients populated for all the orders in my OrderDetails table. This means it always returns True. Thank you for educating me @caldrell0. So Any should work in such a case? – FMFF Jan 19 '12 at 16:45
  • Yes, `Any` always returns false for empty collections. – cadrell0 Jan 19 '12 at 17:45
2

I would combine the two queries inside of the loop into one and then use Any as explained in cadrell0's answer:

var orderswithcandianrecipients =
    from o in mydbcontext.OrderDetails
    where o.RefId == refid && o.OrderRecipients.Any(
        w => w.Country.Trim().ToUpper() == "CANADA")          
    select o;

The problem seems to be that you use AsEnumerable() in the first query which causes the data to be loaded into memory. But because you don't include the OrderRecipients in the query this collection will be empty. The second query is executed in memory (LINQ to Objects). So, All will return every order because it's always true for an empty collection. And replacing it by Any would probably return no order at all because Any is always false for an empty collection.

By combining the two queries the whole query is executed in the database ansd should return the correct result.

Slauma
  • 175,098
  • 59
  • 401
  • 420
  • Thank you @Slauma. I need AsEnumerable() for the first query because I had to do this elsewhere in my code: `TotalStatementCount = OrdersForThisRefid.Sum(w => { int result; Int32.TryParse(w.UserDef1, out result); return result; });` Is this possible if I don't use AsEnumerable()? – FMFF Jan 19 '12 at 16:52
  • 1
    @FMFF: No, EF won't be able to translate this into SQL and throw an exception. Perhaps it is possible to rewrite it so that it works (make better a new question if you want to clarify this). You could of course in your first query write `from o in mydbcontext.OrderDetails.Include("OrderRecipients")` but this is potentially expensive as it loads *all* unfiltered OrderRecipients collections into memory. – Slauma Jan 19 '12 at 16:59
  • Yes, I asked that here - http://stackoverflow.com/questions/8917303/linq-summing-numbers-stored-as-string Loading unfiltered OrderRecipients into memory is out of question. There are more than two million of them. :-) Many thanks again for clarifying. – FMFF Jan 19 '12 at 17:04
  • 1
    @FMFF: I see. The solutions provided to your answer are all LINQ to Objects, so they don't run in the database. (I believe the only working true LINQ to Entities way is in the link below your question.) Consider two queries: the one I have provided and a second like your first query, but a bit cheaper: Only select the `UserDef1` column: `...select o.UserDef1).AsEnumerable()`. That's the only field you need to build your sum. – Slauma Jan 19 '12 at 17:15
  • 1
    @FMFF: With "*all*" in my first comment above I meant "unfiltered" by the country. But it will of course only load the `OrderRecipients` collections for the `OrderDetails` you filter by `RefId`. If you only expect a few `OrderDetails` for such a `RefId` and the `OrderRecipients` collections are not extremely long, using `Include` might not be a performance problem. – Slauma Jan 19 '12 at 17:24