This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hi, Based on this post (amongst others) http://www.mindscapehq.com/forums/thread/2313 I'm trying to optimise a few queries so they aren't fetching the entire EntityCollection just to perform an .Any(), .Sum() etc. However, if the entities are associated via a composite key, I'm getting exceptions like this: No coercion operator is defined between types 'Model.CategoryId' and 'System.Nullable`1[System.Guid]'. Where Model.CategoryId in this case is a composite key comprising two Guids. This is enough to produce the exception - there's nothing to trigger its execution:
This is in this morning's nightly build of LS4. Any thoughts? Cheers, -- John |
|
|
Do you have a custom resolver between Category and Line? You won't be able to do this if there's a custom resolver, basically because the custom resolver infrastructure doesn't provide a way for us to compose queries onto it. What you'll need to do instead is to build the children query explicitly e.g.
Otherwise, if Category and Line are simply linked by a foreign key of composite type (i.e. Line has a member CategoryId of type CategoryId), I do see an error but it's not the same as yours. If there's no custom resolver, could you provide us with the EntityHolder, EntityCollection and FK field declarations for the Category-Line association please? Thanks! |
|
|
Thanks Ivan, Yes - there is a custom resolver involved. I've also tried the alternative approach you suggested: This almost works. However, (and this may be something for a new thread), something odd happens once the Where clause involves a DateTime: This works:
This doesn't:
The only difference between the two is the operator used on l.TransactionDate (Which is a nullable DateTime). Less than works, greater than generates the following: Operation is not valid due to the current state of the object. The top of the stack trace is:
|
|
|
This doesn't relate to a date-time issue: it relates to whether there are any results or not. Your less-than query returns results, so the sum is meaningful. Your greater-than query returns no results, so SQL Server returns NULL from the SUM() expression. But LINQ Sum() is defined to return int, not nullable int, so LightSpeed can't convert the SQL NULL into an int. (Yes, we should improve the exception message.) So far the only workaround I have found for this is to explicitly cast the summand to a nullable type -- this makes the return type of Sum() nullable so the SQL NULL can be returned into it:
I tested the same thing with LINQ to SQL and it behaves in the same way, so this is kind of expected behaviour in a rather annoying kind of way. It would be nice to have a less intrusive way of handling it though. |
|
|
Nice one - thanks for that. I never noticed it was actually hitting the database and having trouble with the results. I was barking up the wrong tree thinking it happened while working out the expression. Makes perfect sense. Cheers, -- John |
|
|
(Talking about the second issue in the thread (Operation is not valid due to the current state of the object. )) Not trying to hijack the thread but want to provide extra context. I am so glad this post exists, I had the same issue today! The exception message is confusing but I see that this was already noted! I have one question on this though: Working LINQ query:
Working SQL result: SELECT SUM(t0.TotalAmount) FROM [Invoices] [t0] INNER JOIN [Reservations] [t1] ON [t0].[ReservationId] = [t1].[Id] WHERE EXISTS ( SELECT [t1].* FROM [Reservations] [t1] WHERE [t1].[Id] = [t0].[ReservationId] AND ([t1].[ArrivalDate] >= '12/1/2011 12:00:00 AM' AND [t1].[ArrivalDate] <= '12/31/2011 12:00:00 AM') ) Failing LINQ query: realizedIncome += (from r in invoicesWithinMonth select r.TotalAmount).Sum(l => (decimal?)l); Stacktrace: A first chance exception of type 'System.NotSupportedException' occurred in Mindscape.LightSpeed.Linq.DLL
at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore(LinqQueryPlanExpression plan, Expression expression)
at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(LinqQueryPlanExpression plan, Expression expression)
at Mindscape.LightSpeed.Linq.Sqo.Aggregate.ExtractTargetAttribute(Expression expression, LinqQueryPlanExpression plan)
at Mindscape.LightSpeed.Linq.Sqo.Aggregate.ExtractTargetAttribute(Expression expression, LinqQueryPlanExpression plan)
at Mindscape.LightSpeed.Linq.Sqo.Aggregate.ExtractTargetAttribute(Expression expression, LinqQueryPlanExpression plan)
at Mindscape.LightSpeed.Linq.Sqo.Aggregate.ExtractTargetAttribute(Expression expression, LinqQueryPlanExpression plan)
at Mindscape.LightSpeed.Linq.Sqo.Aggregate.Evaluate(ExpressionVisitor visitor, MethodCallExpression expression)
at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitMethodCall(MethodCallExpression exp)
at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Visit(Expression exp)
at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Build(Expression translation, LinqQueryProvider provider)
at Mindscape.LightSpeed.Linq.LinqQueryProvider.GetExecutionPlan(Expression expression)
at Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute(Expression expression)
at Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Execute[S](Expression expression)
at System.Linq.Queryable.Sum[TSource](IQueryable I agree that the first query is better anyway, but just wondering why the second fails. |
|
|
The reason is entirely internal. Within the LINQ provider, we're trying to figure out what attribute (column) to perform the SUM operation on. In a query of the form:
the Sum contains an expression telling us which column to calculate on, so we just copy that into our 'column to calculate on' slot. Conversely, in a query of the form:
we don't have an expression inside the Sum. But in this case we do have .TotalAmount recorded as a projection. So when we see a Sum with no expression in it, we look for the 'column to calculate on' expression in our stored 'projection to apply' slot instead. But in a query of the form:
we have a Sum expression, which fools us into thinking we're going to get the column to calculate on from the Sum expression. But actually the Sum expression doesn't contain the column to calculate on -- it just contains a cast. The column to calculate on is tucked away in the 'projection to apply' slot, and our current code doesn't think to look there when it's already convinced that everything it needs to know is going to be inside the Sum expression. And that, my liege, is how we know the earth to be banana-shaped. (Except in reality it is rather more complicated.) Anyway, it will be fixed in the next nightly build. Thanks for drawing our attention to it! |
|
|
I bow to Thee. |
|