unable to groupby item.Date.Month

[kennywangjin]

I have a query to group records by one column "Date" of type DateTimeOffset.
I want to group via GroupBy(item=>item.Date.Month).
But it won't work!
Does ef7 support this kind of query?
Thanks in advance.

Details please see the kestrel logs:
C:\Users\XXXX\Documents\Visual Studio 2015\Projects\OfficeAutomation\src\WebApi>dnx web
Hosting environment: Production
Now listening on: http://localhost:5000
Application started. Press Ctrl+C to shut down.
info: Microsoft.AspNet.Hosting.Internal.HostingEngine[1]
Request starting HTTP/1.1 GET http://localhost:5000/api/cartridges/costreports/2016
info: Microsoft.AspNet.Mvc.Controllers.ControllerActionInvoker[1]
Executing action method WebApi.Controllers.CartridgesController.GetAnnualCostReports with arguments (2016, Purchase, True) - ModelState is Valid'
info: Microsoft.AspNet.Hosting.Internal.HostingEngine[3]
Request finished in 0.1813ms 500
fail: Microsoft.AspNet.Server.Kestrel[13]
An unhandled exception was thrown by the application.
System.ArgumentException: Expression of type 'System.Func2[Microsoft.Data.Entity.Query.EntityQueryModelVisitor+TransparentIdentifier2[DataModels.CartridgeTransaction,Microsoft.Data.Entity.Storage.ValueBuffer],System.Int32]' cannot be used for parameter of type 'System.Func2[DataModels.CartridgeTransaction,System.Int32]' of method 'System.Collections.Generic.IAsyncEnumerable1[System.Linq.IAsyncGrouping2[System.Int32,DataModels.CartridgeTransaction]] _GroupBy[CartridgeTransaction,Int32,CartridgeTransaction](System.Collections.Generic.IAsyncEnumerable1[DataModels.CartridgeTransaction], System.Func2[DataModels.CartridgeTransaction,System.Int32], System.Func2[DataModels.CartridgeTransaction,DataModels.CartridgeTransaction])'
at System.Dynamic.Utils.ExpressionUtils.ValidateOneArgument(MethodBase method, ExpressionType nodeKind, Expression arg, ParameterInfo pi)
at System.Linq.Expressions.Expression.Call(MethodInfo method, Expression arg0, Expression arg1, Expression arg2)
at Microsoft.Data.Entity.Query.ResultOperatorHandler.HandleGroup(EntityQueryModelVisitor entityQueryModelVisitor, GroupResultOperator groupResultOperator, QueryModel queryModel)
at Microsoft.Data.Entity.Query.ResultOperatorHandler.HandleResultOperator(EntityQueryModelVisitor entityQueryModelVisitor, ResultOperatorBase resultOperator, QueryModel queryModel)
at Microsoft.Data.Entity.Query.Internal.RelationalResultOperatorHandler.HandleResultOperator(EntityQueryModelVisitor entityQueryModelVisitor, ResultOperatorBase resultOperator, QueryModel queryModel)
at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.VisitResultOperator(ResultOperatorBase resultOperator, QueryModel queryModel, Int32 index)
at Microsoft.Data.Entity.Query.RelationalQueryModelVisitor.VisitResultOperator(ResultOperatorBase resultOperator, QueryModel queryModel, Int32 index)
at Remotion.Linq.QueryModelVisitorBase.VisitResultOperators(ObservableCollection1 resultOperators, QueryModel queryModel) at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel) at Microsoft.Data.Entity.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel) at Microsoft.Data.Entity.Query.Internal.SqlServerQueryModelVisitor.VisitQueryModel(QueryModel queryModel) at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.CreateAsyncQueryExecutor[TResult](QueryModel queryModel) at Microsoft.Data.Entity.Query.Internal.CompiledQueryCache.GetOrAddAsyncQuery[TResult](Object cacheKey, Func1 compiler)
at Microsoft.Data.Entity.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query)
at Microsoft.Data.Entity.Query.Internal.EntityQueryable1.System.Collections.Generic.IAsyncEnumerable<TResult>.GetEnumerator() at System.Linq.AsyncEnumerable.Aggregate[TSource,TAccumulate,TResult](IAsyncEnumerable1 source, TAccumulate seed, Func3 accumulator, Func2 resultSelector, CancellationToken cancellationToken)
at System.Linq.AsyncEnumerable.Aggregate[TSource,TAccumulate](IAsyncEnumerable1 source, TAccumulate seed, Func3 accumulator, CancellationToken cancellationToken)
at System.Linq.AsyncEnumerable.ToDictionary[TSource,TKey,TElement](IAsyncEnumerable1 source, Func2 keySelector, Func2 elementSelector, IEqualityComparer1 comparer, CancellationToken cancellationToken)
at System.Linq.AsyncEnumerable.ToDictionary[TSource,TKey,TElement](IAsyncEnumerable1 source, Func2 keySelector, Func2 elementSelector, CancellationToken cancellationToken) at Microsoft.Data.Entity.EntityFrameworkQueryableExtensions.ToDictionaryAsync[TSource,TKey,TElement](IQueryable1 source, Func2 keySelector, Func2 elementSelector, CancellationToken cancellationToken)
at WebApi.Controllers.CartridgesController.d__6.MoveNext() in C:\Users\kenny\Documents\Visual Studio 2015\Projects\OfficeAutomation\src\WebApi\Controllers\CartridgesController.cs:line 120
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.AspNet.Mvc.Controllers.ControllerActionExecutor.d__8`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.AspNet.Mvc.Controllers.ControllerActionInvoker.d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.AspNet.Mvc.Controllers.FilterActionInvoker.d__53.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.AspNet.Mvc.Controllers.FilterActionInvoker.d__44.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.AspNet.Mvc.Infrastructure.MvcRouteHandler.d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.AspNet.Mvc.Routing.InnerAttributeRoute.d__10.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.AspNet.Routing.RouteCollection.d__9.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.AspNet.Builder.RouterMiddleware.d__4.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.AspNet.IISPlatformHandler.IISPlatformHandlerMiddleware.d__8.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.AspNet.Hosting.Internal.RequestServicesContainerMiddleware.d__3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.AspNet.Hosting.Internal.HostingEngine.<>c__DisplayClass32_0.<b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.AspNet.Server.Kestrel.Http.Frame.d__79.MoveNext()

[rowanmiller]

@mikary can you confirm if this is fixed

[mikary]

Ran the following sample against Microsoft.EntityFrameworkCore.SqlServer 1.0.0-rc2-final and it produced the expected output without issue (GroupBy is performed on the client, as expected).

class Program
{
    static void Main(string[] args)
    {
        using (var context = new MyContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            var now = DateTimeOffset.UtcNow;

            context.Add(new MyPoco { TimeStamp = now });
            context.Add(new MyPoco { TimeStamp = now });
            context.Add(new MyPoco { TimeStamp = now + TimeSpan.FromDays(90) });
            context.SaveChanges();
        }

        using (var context = new MyContext())
        {
            var result = context.MyPocos.GroupBy(p => p.TimeStamp.Month).ToList();

            foreach (var grouping in result)
            {
                Console.WriteLine("Key: {0}", grouping.Key);
                Console.WriteLine("Values: {0}", string.Join(", ", grouping.Select(p => p.Id)));
            }
        }
    }
}

public class MyPoco
{
    public int Id { get; set; }

    public DateTimeOffset TimeStamp { get; set; }
}

public class MyContext : DbContext
{
    public DbSet<MyPoco> MyPocos { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=GroupByDateTimeOffsetMonth;Integrated Security=True");
    }
}

Generated SQL

SELECT [p].[Id], [p].[TimeStamp]
FROM [MyPocos] AS [p]

[mikary]

Attempted to add test coverage to QueryTestBase:

[ConditionalFact]
public virtual void GroupBy_DateTimeOffset_Property()
{
    AssertQuery<Order>(os =>
        os.Where(o => o.OrderDate.HasValue)
            .GroupBy(o => o.OrderDate.Value.Month),
        asserter: (l2oResults, efResults) =>
            {
                var efGroupings = efResults.Cast<IGrouping<int, Order>>().ToList();

                foreach (IGrouping<int, Order> l2oGrouping in l2oResults)
                {
                    var efGrouping = efGroupings.Single(efg => efg.Key == l2oGrouping.Key);

                    Assert.Equal(l2oGrouping.OrderBy(o => o.OrderID), efGrouping.OrderBy(o => o.OrderID));
                }
            },
        entryCount: 830);
}

This test passes with the InMemory and SqlServer provider, but returns the incorrect number of results on Sqlite.

SqlServer generated SQL:

SELECT [o].[OrderID], [o].[CustomerID], [o].[EmployeeID], [o].[OrderDate]
FROM [Orders] AS [o]
WHERE [o].[OrderDate] IS NOT NULL
ORDER BY DATEPART(month, [o].[OrderDate])

Sqlite generated SQL:

PRAGMA foreign_keys=OFF;

SELECT "o"."OrderID", "o"."CustomerID", "o"."EmployeeID", "o"."OrderDate"
FROM "Orders" AS "o"
WHERE "o"."OrderDate" IS NOT NULL

The Sqlite results contain 23 groupings instead of 12, possibly resulting from the results not being in the expected order for materialization

[mikary]

QueryPlan for Sqlite:

TRACKED: False
(QueryContext queryContext) => IEnumerable<IGrouping<int, Order>> _TrackGroupedEntities(
    groupings: IEnumerable<IGrouping<int, Order>> _GroupBy(
        source: IEnumerable<Order> _ShapedQuery(
            queryContext: queryContext, 
            shaperCommandContext: SelectExpression: 
                SELECT "o"."OrderID", "o"."CustomerID", "o"."EmployeeID", "o"."OrderDate"
                FROM "Orders" AS "o"
                WHERE "o"."OrderDate" IS NOT NULL
            , 
            shaper: UnbufferedEntityShaper<Order>
        )
        , 
        keySelector: (Order o) => (DateTime) o.OrderDate.Month, 
        elementSelector: (Order o) => o
    )
    , 
    queryContext: queryContext, 
    entityTrackingInfos: { itemType: Order }, 
    entityAccessors: List<Func<Order, object>> 
    { 
        System.Func`2[Specification.Tests.TestModels.Northwind.Order,Specification.Tests.TestModels.Northwind.Order], 
    }
)

[mikary]

This also reproduces on SqlServer when there is a GroupBy that requires client eval for the key selector

AssertQuery<Customer>(cs =>
    cs.GroupBy(c => c.IsLondon),
    asserter: (l2oResults, efResults) =>
        {
            var efGroupings = efResults.Cast<IGrouping<bool, Customer>>().ToList();

            foreach (IGrouping<bool, Customer> l2oGrouping in l2oResults)
            {
                var efGrouping = efGroupings.Single(efg => efg.Key == l2oGrouping.Key);

                Assert.Equal(l2oGrouping.OrderBy(o => o.CustomerID), efGrouping.OrderBy(o => o.CustomerID));
            }
        });

QueryPlan:

TRACKED: False
(QueryContext queryContext) => IEnumerable<IGrouping<bool, Customer>> _TrackGroupedEntities(
    groupings: IEnumerable<IGrouping<bool, Customer>> _GroupBy(
        source: IEnumerable<Customer> _ShapedQuery(
            queryContext: queryContext, 
            shaperCommandContext: SelectExpression: 
                SELECT [c].[CustomerID], [c].[Address], [c].[City], [c].[CompanyName], [c].[ContactName], [c].[ContactTitle], [c].[Country], [c].[Fax], [c].[Phone], [c].[PostalCode], [c].[Region]
                FROM [Customers] AS [c]
            , 
            shaper: UnbufferedEntityShaper<Customer>
        )
        , 
        keySelector: (Customer c) => c.IsLondon, 
        elementSelector: (Customer c) => c
    )
    , 
    queryContext: queryContext, 
    entityTrackingInfos: { itemType: Customer }, 
    entityAccessors: List<Func<Customer, object>> 
    { 
        System.Func`2[Specification.Tests.TestModels.Northwind.Customer,Specification.Tests.TestModels.Northwind.Customer], 
    }
)

[smitpatel]

Our in-memory group by operator assumes that the entries are coming in the order of the grouping. Hence when we encounter different key we return groupings collected so far. This fails when the key for 2 rows are same but they is at least one row in between which has different key.