Hi
I've found a bug when doing a group by, I've reduced my faulty query to a really small query, and can still reproduce the problem. This is the query:
@foreach (var tmp in (
from t in Query.All<Line>()
group t by t
into t3
select new
{
Test = t3.Key,
Test2 = t3.Key.ItemVersionsUntyped.Count()
}
))
{
<li>
Entity: @tmp.Test.Id<br />
EntitySet (SQL): @tmp.Test2<br /> <!--This will return 0, which is wrong -->
EntitySet (LINQ): @tmp.Test.ItemVersionsUntyped.Count() <!-- This will return 1, which is correct -->
</li>
}
The model is quite large, in code, but it's like this, more or less (omitting [Field] etc. The special ItemItemVersionsEntitySet is just to enforce some special constraints when adding to the entityset):
class ItemItemVersionsEntitySet : EntitySet<IItemVersionBase>
abstract class ItemBase : Entity, IItemBase {}
abstract class ItemVersionBase : Entity, IItemVersionBase {}
abstract class Item<TOwner, TChild> : ItemBase
{
[Association(PairTo = "ItemUntyped", OnOwnerRemove = OnRemoveAction.Cascade, OnTargetRemove = OnRemoveAction.Clear)]
ItemItemVersionsEntitySet ItemVersionsUntyped {get; private set;}
}
abstract class ItemVersion<TOwner, TChild> : ItemVersionBase
{
IItemBase ItemUntyped { get; set; }
}
class Line : Item<Line, LineVersion>
{
//Some fields
}
class LineVersion : ItemVersion<Line, LineVersion>
{
//Some fields
}
All entities share this:
Guid Id {get; private set;}
[Field] on everything
Line and LineVersion are HierarchyRoot, with SingleTable and IncludeTypeId
In DB they are represented as only two tables, Line and LineVersion.
There are some other fields in the models. and other classes, but they are not relevant. The generated SQL looks like this, after some beautifying. Ignore the fact that there are a big list of unions, it's irrelevant, normally I wouldn't query ItemVersionsUntyped, I would use ItemVersionsTyped which is a LinqRewriter to get only LineVersion and not every IItemVersionBase. However, to keep the query as simple as possible I avoided the helpers.
SELECT [a].[id],
[a].[typeid],
[b].[stations.id] AS [#a.Stations.Id],
[b].[stations.typeid] AS [#a.Stations.TypeId],
(SELECT Count_big(*)
FROM ((SELECT [c].[id],
[c].[typeid],
[c].[editversion.id],
[c].[editversion.typeid],
[c].[itemisdeleted],
[c].[itemuntyped.id],
[c].[itemuntyped.typeid],
[c].[timestampfromeditversion],
[c].[islivefromeditversion]
FROM (SELECT [d].[id],
[d].[typeid],
[d].[editversion.id],
[d].[editversion.typeid],
[d].[itemisdeleted],
[d].[itemuntyped.id],
[d].[itemuntyped.typeid],
[d].[timestampfromeditversion],
[d].[islivefromeditversion]
FROM [dbo].[bodml.lineversion] [d]) [c])
UNION
(SELECT [e].[id],
[e].[typeid],
[e].[editversion.id],
[e].[editversion.typeid],
[e].[itemisdeleted],
[e].[itemuntyped.id],
[e].[itemuntyped.typeid],
[e].[timestampfromeditversion],
[e].[islivefromeditversion]
FROM (SELECT [f].[id],
[f].[typeid],
[f].[editversion.id],
[f].[editversion.typeid],
[f].[itemisdeleted],
[f].[itemuntyped.id],
[f].[itemuntyped.typeid],
[f].[timestampfromeditversion],
[f].[islivefromeditversion]
FROM [dbo].[bodml.linestationversion] [f]) [e])
UNION
(SELECT [g].[id],
[g].[typeid],
[g].[editversion.id],
[g].[editversion.typeid],
[g].[itemisdeleted],
[g].[itemuntyped.id],
[g].[itemuntyped.typeid],
[g].[timestampfromeditversion],
[g].[islivefromeditversion]
FROM (SELECT [h].[id],
[h].[typeid],
[h].[editversion.id],
[h].[editversion.typeid],
[h].[itemisdeleted],
[h].[itemuntyped.id],
[h].[itemuntyped.typeid],
[h].[timestampfromeditversion],
[h].[islivefromeditversion]
FROM [dbo].[bodml.stationversion] [h]) [g])
UNION
(SELECT [i].[id],
[i].[typeid],
[i].[editversion.id],
[i].[editversion.typeid],
[i].[itemisdeleted],
[i].[itemuntyped.id],
[i].[itemuntyped.typeid],
[i].[timestampfromeditversion],
[i].[islivefromeditversion]
FROM (SELECT [j].[id],
[j].[typeid],
[j].[editversion.id],
[j].[editversion.typeid],
[j].[itemisdeleted],
[j].[itemuntyped.id],
[j].[itemuntyped.typeid],
[j].[timestampfromeditversion],
[j].[islivefromeditversion]
FROM [dbo].[bodms.userversion] [j]) [i])
UNION
(SELECT [k].[id],
[k].[typeid],
[k].[editversion.id],
[k].[editversion.typeid],
[k].[itemisdeleted],
[k].[itemuntyped.id],
[k].[itemuntyped.typeid],
[k].[timestampfromeditversion],
[k].[islivefromeditversion]
FROM (SELECT [l].[id],
[l].[typeid],
[l].[editversion.id],
[l].[editversion.typeid],
[l].[itemisdeleted],
[l].[itemuntyped.id],
[l].[itemuntyped.typeid],
[l].[timestampfromeditversion],
[l].[islivefromeditversion]
FROM [dbo].[bodms.passwordversion] [l]) [k])
UNION
(SELECT [m].[id],
[m].[typeid],
[m].[editversion.id],
[m].[editversion.typeid],
[m].[itemisdeleted],
[m].[itemuntyped.id],
[m].[itemuntyped.typeid],
[m].[timestampfromeditversion],
[m].[islivefromeditversion]
FROM (SELECT [n].[id],
[n].[typeid],
[n].[editversion.id],
[n].[editversion.typeid],
[n].[itemisdeleted],
[n].[itemuntyped.id],
[n].[itemuntyped.typeid],
[n].[timestampfromeditversion],
[n].[islivefromeditversion]
FROM [dbo].[itemsetitemversion(bodms.user-bodms.baserole)] [n]) [m])
UNION
(SELECT [o].[id],
[o].[typeid],
[o].[editversion.id],
[o].[editversion.typeid],
[o].[itemisdeleted],
[o].[itemuntyped.id],
[o].[itemuntyped.typeid],
[o].[timestampfromeditversion],
[o].[islivefromeditversion]
FROM (SELECT [p].[id],
[p].[typeid],
[p].[editversion.id],
[p].[editversion.typeid],
[p].[itemisdeleted],
[p].[itemuntyped.id],
[p].[itemuntyped.typeid],
[p].[timestampfromeditversion],
[p].[islivefromeditversion]
FROM [dbo].[itemsetitemversion(bodml.line-bodml.linestation)] [p]) [o])) [q]
WHERE ( ( [q].[itemuntyped.id] = [q].[id] )
AND ( [q].[itemuntyped.typeid] = [q].[typeid] ) )) AS [c01umn]
FROM (SELECT [r].[id],
[r].[typeid]
FROM [dbo].[bodml.line] [r]
GROUP BY [r].[id],
[r].[typeid]) [a]
INNER JOIN [dbo].[bodml.line] [b]
ON ( ( [a].[id] = [b].[id] )
AND ( [a].[typeid] = [b].[typeid] ) )
As you can see it compares the wrong tables here:
WHERE ( ( [q].[itemuntyped.id] = [q].[id] )
AND ( [q].[itemuntyped.typeid] = [q].[typeid] ) )) AS [c01umn]
I think it should be like this instead:
WHERE ( ( [q].[itemuntyped.id] = [a].[id] )
AND ( [q].[itemuntyped.typeid] = [a].[typeid] ) )) AS [c01umn]
When executing the original query in sql server management I get this result, which I didn't expect:
D8296E94-FA01-437D-9EBD-77B4F3DB02D5 100 1E60CE0C-7ED4-4626-A3DA-0FBFDB71A9CD 111 0
346ED63A-D8E7-4EA4-A9B5-A505E343371C 100 D9E936B6-1CCD-496F-B42F-9142F2003FC8 111 0
However, when executing my modified sql I get this result, which I expected:
D8296E94-FA01-437D-9EBD-77B4F3DB02D5 100 1E60CE0C-7ED4-4626-A3DA-0FBFDB71A9CD 111 1
346ED63A-D8E7-4EA4-A9B5-A505E343371C 100 D9E936B6-1CCD-496F-B42F-9142F2003FC8 111 1
I tried different queries, and they all seem to share the problem that setting a property to .Key in the grouping works, but using .Key.Anything() won't work.
If I try this query instead (with .Count instead of .Count()) I get an error message instead:
from t in Query.All<Line>()
group t by t
into t3
select new
{
Test = t3.Key,
Test2 = t3.Key.ItemVersionsUntyped.Count
}
)
Error:
[IndexOutOfRangeException: Index was outside the bounds of the array.]
Xtensive.Orm.Linq.Rewriters.EntitySetAccessRewriter.VisitMemberAccess(MemberExpression m) +283
Xtensive.Linq.ExpressionVisitor`1.Visit(Expression e) +658
Xtensive.Linq.ExpressionVisitor.VisitExpressionList(ReadOnlyCollection`1 expressions) +170
Xtensive.Linq.ExpressionVisitor.VisitNew(NewExpression n) +81
Xtensive.Linq.ExpressionVisitor`1.Visit(Expression e) +891
Xtensive.Linq.ExpressionVisitor.VisitLambda(LambdaExpression l) +81
Xtensive.Linq.ExpressionVisitor`1.Visit(Expression e) +814
Xtensive.Linq.ExpressionVisitor.VisitUnary(UnaryExpression u) +81
Xtensive.Linq.ExpressionVisitor`1.Visit(Expression e) +227
Xtensive.Linq.ExpressionVisitor.VisitExpressionList(ReadOnlyCollection`1 expressions) +170
Xtensive.Linq.ExpressionVisitor.VisitMethodCall(MethodCallExpression mc) +128
Xtensive.Orm.Linq.Rewriters.EntitySetAccessRewriter.VisitMethodCall(MethodCallExpression mc) +101
Xtensive.Linq.ExpressionVisitor`1.Visit(Expression e) +736
Xtensive.Orm.Linq.Rewriters.EntitySetAccessRewriter.Rewrite(Expression e) +66
Xtensive.Orm.Linq.TranslatorContext..ctor(Session session, CompilerConfiguration rseCompilerConfiguration, Expression query) +417
Xtensive.Orm.Linq.QueryProvider.Translate(Expression expression, CompilerConfiguration compilerConfiguration) +104
[QueryTranslationException: Unable to translate 'Query.All().GroupBy(t => t).Select(t3 => new @<Test, Test2>(
t3.Key,
t3.Key.ItemVersionsUntyped.Count
))' expression. See inner exception for details.]
Xtensive.Orm.Linq.QueryProvider.Translate(Expression expression, CompilerConfiguration compilerConfiguration) +274
Xtensive.Orm.Linq.QueryProvider.Translate(Expression expression) +113
Xtensive.Orm.Linq.QueryProvider.Execute(Expression expression) +147
Xtensive.Orm.Linq.Queryable`1.GetEnumerator() +124
(The need to use the Key is simply to limit the amount of SQL queries, prefetch etc. For performance, simply)
I hope you will be able to reproduce this easily, if not, I will try to make a smaller model to reproduce this on :)
DO.NET 4.6.2 (From NuGet)
SQL Server 2005 (If relevant)
Thanks for the report, we'll check it very soon.