EF4, Npgsql, M:M relation, "The method or operation is not implemented." :/

Question

Hello

I have a problem with many-to-many relation on EF4 and npgsql provider.

I have 3 tables: Order, OrderStatus and OrderStatusDict (dictionary). OrderStatus is some kind of changeLog - every order's status change is added to the OrderStatus table with its actual date and new status. I need to get all orders, which status.OrderStatusDict.OrderStatusName == "Pending", so im doing this this way:

var lst = (from o in db.Order
           where o.OrderStatus
                       .OrderByDescending(s => s.Date)
                       .FirstOrDefault()
                       .OrdereStatusDict.OrderStatusName == "Pending"          
           select o).ToList();

And I get an exception:

An error occurred while preparing the command definition. See the inner exception for details. Data:{System.Collections.ListDictionaryInternal} Inner exception: {"The method or operation is not implemented."}

And it looks that OrderByDescending kills my query, cause if I comment

.OrderByDescending(s => s.Date)

Everything works fine, but I get the oldest OrderStatus from db :/

Can u help me? Is this a provider's cause or EF problem? Do you have any ideas I could gain this other way?

Answer 1

I wouldn't write the query that way.

Try:

var lst = (from o in db.Order
           let maxDate = o.OrderStatus.Max(s => s.Date)
           where o.OrderStatus.Any(s => s.Date == MaxDate 
                                        && s.OrdereStatusDict.OrderStatusName.Equals("Pending", StringComparison.OrdinalIgnoreCase))
           select o).ToList();

I'm guessing the status comparison should be case-insensitive.

Note that my query behaves differently than yours if the max date is not unique. I'm guessing it's either unique or that my way is right.