Invalid query generated for simple group by

[NitinNotebook]

Describe what is not working as expected.
Simple group by generating invalid query and also warning that Sum getting evaluated in local

List<int> lstShiftIds;
var efQuery = from shiftDetail in ShiftDetailSet
                          where lstShiftIds.Contains(shiftDetail.ShiftId) 
                          group shiftDetail by new { shiftDetail.ShiftId, shiftDetail.TpId } into g                          
                          select new ShiftNetAmount
                          {
                              ShiftId = g.Key.ShiftId,
                              TpId = g.Key.TpId,
                              NetAmount = g.Sum(s=> s.Jul) 
                          };

Exception message:
Issue 1
Above LINQ expression generates Invalid Query:
SELECT "shiftDetail"."ShiftId", "shiftDetail"."TpId", "shiftDetail"."Jul" AS "NetAmount"
FROM "ShiftDetail" AS "shiftDetail"
WHERE "shiftDetail"."ShiftId" IN (1000000, 1000100, 1000200)
GROUP BY "shiftDetail"."ShiftId", "shiftDetail"."TpId"

Column: "shiftDetail"."Jul" is not in Group By but in select

Issue 2
Sum should be evaluated on DB Server, EF core throws following warning

Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'Sum()' could not be translated and will be evaluated locally.

Steps to reproduce

EF query provided above, let me know if more information is required.

Further technical details

EF Core version: 2.2.0
Database Provider: Microsoft.EntityFrameworkCore.SqlLite 2.2.0
Operating system: Win 10
IDE: Visual Studio 2017 15.9.4

[smitpatel]

I am not able to reproduce the issue based on above data. The exact same query is working fine for me.
Please submit a detailed repro code which demonstrate the issue you are seeing
Repro Code:

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace EFSampleApp
{
    public class Program
    {
        public static void Main(string[] args)
        {
            using (var db = new MyContext())
            {
                // Recreate database
                db.Database.EnsureDeleted();
                db.Database.EnsureCreated();

                // Seed database


                db.SaveChanges();
            }

            using (var db = new MyContext())
            {
                // Run queries
                var ids = new List<int> { 1, 2, 3 };
                var efQuery = from blog in db.Blogs
                              where ids.Contains(blog.Id)
                              group blog by new { blog.Id, blog.TpId } into g
                              select new NetAmountDto
                              {
                                  Id = g.Key.Id,
                                  TpId = g.Key.TpId,
                                  NetAmount = g.Sum(s => s.Jul)
                              };

                efQuery.ToList();
            }

            Console.WriteLine("Program finished.");
        }
    }


    public class MyContext : DbContext
    {
        private static ILoggerFactory LoggerFactory => new LoggerFactory().AddConsole(LogLevel.Trace);

        // Declare DBSets
        public DbSet<Blog> Blogs { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            // Select 1 provider
            optionsBuilder
                .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=_ModelApp;Trusted_Connection=True;Connect Timeout=5;ConnectRetryCount=0")
                //.UseSqlite("filename=_modelApp.db")
                //.UseInMemoryDatabase(databaseName: "_modelApp")
                .EnableSensitiveDataLogging()
                .UseLoggerFactory(LoggerFactory);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Configure model
        }
    }

    public class Blog
    {
        public int Id { get; set; }
        public int TpId { get; set; }
        public int Jul { get; set; }
    }

    public class NetAmountDto
    {
        public int Id { get; set; }
        public int TpId { get; set; }
        public int NetAmount { get; set; }
    }
}

Generated SQL:

      SELECT [blog].[Id], [blog].[TpId], SUM([blog].[Jul]) AS [NetAmount]
      FROM [Blogs] AS [blog]
      WHERE [blog].[Id] IN (1, 2, 3)
      GROUP BY [blog].[Id], [blog].[TpId]

[NitinNotebook]

EfCoreSample.zip

Thank you for quick response. I have attached only relevant version of the code in solution where I was able to repro. The Query printed in (debug) output window is as follows:
SELECT "sd"."ShiftId", "sd"."TpId", "sd"."Jan", "sd"."Feb", "sd"."Mar"
FROM "ShiftDetail" AS "sd"
WHERE "sd"."ShiftId" IN (1, 2)
GROUP BY "sd"."ShiftId", "sd"."TpId"

#Update: I tried putting your code in SqlLite it still does not work in my sample
Just checked with UseSqlServer, it works fine as you have suggested. So the problem is only with SqlLite.

Another observation:
Below code leads to evaluation of query on client
NetAmount = g.Sum(s => s.Jan + s.Feb + s.Mar)

And below generates a proper query with server side aggregation:
NetAmount = g.Sum(s => s.Jan) + g.Sum(s => s.Feb) + g.Sum(s => s.Mar)

[ajcvickers]

@smitpatel to follow up.

[smitpatel]

Duplicate of #14083

[smitpatel]

Duplicate of #12089