I'm trying to group a set of data using linq and entity framework 3.1. This is the query I'm trying to accomplish:
var query = dbset
.Join(vehicles,
i => i.VehicleId,
v => v.Id,
(i, v) => new{i, v})
.Where(o=>o.v.EnterpriseId == enterpriseId && o.i.ReportDate>=startDate && o.i.ReportDate<=endDate);
dateDataQuery = query.GroupBy(o =>
new
{
Week=(int)(o.i.ReportDate.DayOfYear / 7),
o.i.ReportDate.Year
})
.OrderBy(o=> o.Key.Year).ThenBy(o=>o.Key.Week)
.Select(g => new TransferDateData()
{
Week = g.Key.Week,
Year = g.Key.Year,
Value = g.Sum(o=>o.i.Cost),
});
As you can see I am grouping by Week and Year. The problem is that DateTime doesn't have a Week property to get the week of the year that could be translatable to a database procedure. As we can do with .Month or .Day. So, the week must be calculated.
This query outputs different results with same Week and Year. For example I can have several results like:
{ Week = 20, Year=2020, Value = 20}, {Week=20, Year=2020, Value=30} ...
As you can see they should have been grouped but they don't. If I use a hardcoded number for the week in the grouping, the gropuing works and all values are aggregated and only a result returned.
So, I guess there is a problem with the division and the float result although Week is a int.
Here are the two models I'm using and their properties:
public class GenericDateData<T>
{
public T Value { get; set; }
public int Month { get; set; }
public int Year { get; set; }
public int Day { get; set; }
public int Week { get; set; }
}
public class TransferDateData : GenericDateData<float>
{
public float TotalKm { get; set; }
}
Could you guess what is happening? Seems a bug to me.
Your query does work. As mentioned by @PanagiotisKanavos in the comments, it is inaccurate, but it does translate into valid SQL:
using System;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
namespace IssueConsoleTemplate
{
public class Incident
{
public int Id { get; set; }
public int VehicleId { get; set; }
public DateTime ReportDate { get; set; }
public float Cost { get; set; }
}
public class Vehicle
{
public int Id { get; set; }
public int EnterpriseId { get; set; }
}
public class Enterprise
{
public int Id { get; set; }
}
public class GenericDateData<T>
{
public T Value { get; set; }
public int Month { get; set; }
public int Year { get; set; }
public int Day { get; set; }
public int Week { get; set; }
}
public class TransferDateData : GenericDateData<float>
{
public float TotalKm { get; set; }
}
public class Context : DbContext
{
public DbSet<Incident> Incidents { get; set; }
public DbSet<Vehicle> Vehicles { get; set; }
public DbSet<Enterprise> Enterprises { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseSqlServer(
@"Data Source=.\MSSQL14;Integrated Security=SSPI;Initial Catalog=So63118907")
.UseLoggerFactory(
LoggerFactory.Create(
b => b
.AddConsole()
.AddFilter(level => level >= LogLevel.Information)))
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Incident>().HasData(
new Incident {Id = 1, VehicleId = 1, ReportDate = new DateTime(2020, 1, 1), Cost = 42.00f},
new Incident {Id = 2, VehicleId = 2, ReportDate = new DateTime(2020, 2, 1), Cost = 21.00f});
modelBuilder.Entity<Vehicle>().HasData(
new Vehicle {Id = 1, EnterpriseId = 1},
new Vehicle {Id = 2, EnterpriseId = 1});
modelBuilder.Entity<Enterprise>().HasData(
new Enterprise {Id = 1});
}
}
internal static class Program
{
private static void Main()
{
using var context = new Context();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
var enterpriseId = 1;
var startDate = new DateTime(2020, 1, 1);
var endDate = new DateTime(2021, 12, 31);
var costPerWeek = context.Incidents
.Join(
context.Vehicles,
i => i.VehicleId,
v => v.Id,
(i, v) => new {i, v})
.Where(
o => o.v.EnterpriseId == enterpriseId &&
o.i.ReportDate >= startDate &&
o.i.ReportDate <= endDate)
.GroupBy(
o =>
new
{
Week = (int) (o.i.ReportDate.DayOfYear / 7),
o.i.ReportDate.Year
})
.OrderBy(o => o.Key.Year)
.ThenBy(o => o.Key.Week)
.Select(
g => new TransferDateData()
{
Week = g.Key.Week,
Year = g.Key.Year,
Value = g.Sum(o => o.i.Cost),
})
.ToList();
Debug.Assert(costPerWeek.Count == 2);
Debug.Assert(costPerWeek[0].Week == 0);
Debug.Assert(costPerWeek[0].Value == 42.00f);
Debug.Assert(costPerWeek[1].Week == 4);
Debug.Assert(costPerWeek[1].Value == 21.00f);
}
}
}
The query gets translated to the following SQL:
SELECT DATEPART(dayofyear, [i].[ReportDate]) / 7 AS [Week], DATEPART(year, [i].[ReportDate]) AS [Year], CAST(SUM([i].[Cost]) AS real) AS [Value]
FROM [Incidents] AS [i]
INNER JOIN [Vehicles] AS [v] ON [i].[VehicleId] = [v].[Id]
WHERE (([v].[EnterpriseId] = @__enterpriseId_0) AND ([i].[ReportDate] >= @__startDate_1)) AND ([i].[ReportDate] <= @__endDate_2)
GROUP BY DATEPART(dayofyear, [i].[ReportDate]) / 7, DATEPART(year, [i].[ReportDate])
ORDER BY DATEPART(year, [i].[ReportDate]), DATEPART(dayofyear, [i].[ReportDate]) / 7
It currently starts counting the weeks from 0 though, and despite that, the date can be off (depending on how the calendar week is calculated in the culture this app is being used) by a couple of days.
A simpler and more accurate solution might be to use the DATEPART(week, @date) T-SQL function. Unfortunately, there is currently no .NET Core method that gets translated to this SQL function.
You can however define a UDF (user defined function) that redirects the call to DATEPART.
The following example does not only define a UDF and uses it, but also shows how to use navigation properties instead of explicit joins:
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
namespace IssueConsoleTemplate
{
public class Incident
{
public int Id { get; set; }
public int VehicleId { get; set; }
public DateTime ReportDate { get; set; }
public float Cost { get; set; }
public Vehicle Vehicle { get; set; }
public Enterprise Enterprise { get; set; }
}
public class Vehicle
{
public int Id { get; set; }
public int EnterpriseId { get; set; }
public virtual ICollection<Incident> Incidents { get; set; } = new HashSet<Incident>();
}
public class Enterprise
{
public int Id { get; set; }
}
public class GenericDateData<T>
{
public T Value { get; set; }
public int Month { get; set; }
public int Year { get; set; }
public int Day { get; set; }
public int Week { get; set; }
}
public class TransferDateData : GenericDateData<float>
{
public float TotalKm { get; set; }
}
public class Context : DbContext
{
public DbSet<Incident> Incidents { get; set; }
public DbSet<Vehicle> Vehicles { get; set; }
public DbSet<Enterprise> Enterprises { get; set; }
// Define your UDF:
[DbFunction("GetCalendarWeek")]
public static int GetCalendarWeek(DateTime date)
=> throw new InvalidOperationException("Should never be called but only translated to SQL.");
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseSqlServer(
@"Data Source=.\MSSQL14;Integrated Security=SSPI;Initial Catalog=So63118907_01")
.UseLoggerFactory(
LoggerFactory.Create(
b => b
.AddConsole()
.AddFilter(level => level >= LogLevel.Information)))
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Incident>().HasData(
new Incident {Id = 1, VehicleId = 1, ReportDate = new DateTime(2020, 1, 1), Cost = 42.00f},
new Incident {Id = 2, VehicleId = 2, ReportDate = new DateTime(2020, 2, 1), Cost = 21.00f});
modelBuilder.Entity<Vehicle>().HasData(
new Vehicle {Id = 1, EnterpriseId = 1},
new Vehicle {Id = 2, EnterpriseId = 1});
modelBuilder.Entity<Enterprise>().HasData(
new Enterprise {Id = 1});
}
}
internal static class Program
{
private static void Main()
{
using var context = new Context();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
// Since this example recreates the database on each run,
// lets create our UDF here as well:
context.Database.ExecuteSqlRaw(@"
CREATE FUNCTION [GetCalendarWeek] (@date DATETIME)
RETURNS INT AS
BEGIN
RETURN DATEPART(week, @date);
END");
var enterpriseId = 1;
var startDate = new DateTime(2020, 1, 1);
var endDate = new DateTime(2021, 12, 31);
var costPerWeek = context.Incidents
.Include(i => i.Vehicle)
.Include(i => i.Enterprise)
.Where(
i => i.Vehicle.EnterpriseId == enterpriseId &&
i.ReportDate >= startDate &&
i.ReportDate <= endDate)
.GroupBy(
i =>
new
{
// Let's call our UDF here:
Week = Context.GetCalendarWeek(i.ReportDate),
i.ReportDate.Year
})
.OrderBy(o => o.Key.Year)
.ThenBy(o => o.Key.Week)
.Select(
g => new TransferDateData()
{
Week = g.Key.Week,
Year = g.Key.Year,
Value = g.Sum(i => i.Cost),
})
.ToList();
Debug.Assert(costPerWeek.Count == 2);
Debug.Assert(costPerWeek[0].Week == 1);
Debug.Assert(costPerWeek[0].Value == 42.00f);
Debug.Assert(costPerWeek[1].Week == 5);
Debug.Assert(costPerWeek[1].Value == 21.00f);
}
}
}
The week numbers are correct (depending on your culture) now.
The generated SQL is petty simple now:
CREATE FUNCTION [GetCalendarWeek] (@date DATETIME)
RETURNS INT AS
BEGIN
RETURN DATEPART(week, @date);
END
SELECT [dbo].[GetCalendarWeek]([i].[ReportDate]) AS [Week], DATEPART(year, [i].[ReportDate]) AS [Year], CAST(SUM([i].[Cost]) AS real) AS [Value]
FROM [Incidents] AS [i]
INNER JOIN [Vehicles] AS [v] ON [i].[VehicleId] = [v].[Id]