EF4, Npgsql, M:M relation, "The method or operation is not implemented." :/
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?
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.
精彩评论