c# - query - Agrupar por semanas en LINQ a entidades
query data entity framework (13)
Tengo una aplicación que les permite a los usuarios ingresar el tiempo que pasan trabajando, y estoy tratando de generar algunos buenos informes para esto que aprovechen LINQ para Entidades. Debido a que cada TrackedTime tiene un TargetDate que es solo la parte de "Fecha" de DateTime , es relativamente simple agrupar los tiempos por usuario y fecha (estoy dejando afuera las cláusulas "where" para simplificar):
var userTimes = from t in context.TrackedTimes
group t by new {t.User.UserName, t.TargetDate} into ut
select new
{
UserName = ut.Key.UserName,
TargetDate = ut.Key.TargetDate,
Minutes = ut.Sum(t => t.Minutes)
};
Gracias a la propiedad DateTime.Month , la agrupación por usuario y Month es solo un poco más complicada:
var userTimes = from t in context.TrackedTimes
group t by new {t.User.UserName, t.TargetDate.Month} into ut
select new
{
UserName = ut.Key.UserName,
MonthNumber = ut.Key.Month,
Minutes = ut.Sum(t => t.Minutes)
};
Ahora viene la parte difícil. ¿Hay una manera confiable de agrupar por semana? Intenté lo siguiente en función de esta respuesta a una pregunta similar de LINQ to SQL:
DateTime firstDay = GetFirstDayOfFirstWeekOfYear();
var userTimes = from t in context.TrackedTimes
group t by new {t.User.UserName, WeekNumber = (t.TargetDate - firstDay).Days / 7} into ut
select new
{
UserName = ut.Key.UserName,
WeekNumber = ut.Key.WeekNumber,
Minutes = ut.Sum(t => t.Minutes)
};
Pero LINQ to Entities no parece admitir operaciones aritméticas en objetos DateTime, por lo que no sabe cómo hacerlo (t.TargetDate - firstDay).Days / 7 .
Consideré crear una vista en la base de datos que simplemente hace corresponder los días con las semanas, y luego agregar esa vista al contexto de mi Entidad Marco y unirme a ella en mi consulta LINQ, pero parece mucho trabajo para algo como esto. ¿Hay una buena solución al enfoque aritmético? Algo que funciona con Linq to Entities, que simplemente puedo incorporar a la declaración LINQ sin tener que tocar la base de datos. ¿Alguna manera de decirle a Linq a las entidades cómo restar una fecha de otra?
Resumen
Me gustaría agradecer a todos por sus respuestas reflexivas y creativas. Después de todo esto de ida y vuelta, parece que la verdadera respuesta a esta pregunta es "espere hasta .NET 4.0". Voy a darle la recompensa a Noldorin por dar la respuesta más práctica que todavía aprovecha LINQ, con mención especial a Jacob Proffitt por haber presentado una respuesta que utiliza el Entity Framework sin la necesidad de modificaciones en el lado de la base de datos. Hubo otras respuestas excelentes, también, y si está viendo la pregunta por primera vez, recomiendo leer todas las que se votaron al alza, así como sus comentarios. Esto realmente ha sido un excelente ejemplo del poder de StackOverflow. ¡Gracias a todos!
¿cuenta lambda? Empecé con la sintaxis de linq, pero creo que en C # 3.0 expresiones lambda cuando se trata de programación declarativa.
public static void TEST()
{
// creating list of some random dates
Random rnd = new Random();
List<DateTime> lstDte = new List<DateTime>();
while (lstDte.Count < 100)
{
lstDte.Add(DateTime.Now.AddDays((int)(Math.Round((rnd.NextDouble() - 0.5) * 100))));
}
// grouping
var weeksgrouped = lstDte.GroupBy(x => (DateTime.MaxValue - x).Days / 7);
}
Bueno, esto es posible, pero es un gran PITA y evita la parte LINQ del Entity Framework. Tomó un poco de investigación, pero puede lograr lo que quiera con la función específica del proveedor SqlServer SqlServer.DateDiff () y "Entity SQL". Esto significa que ha vuelto a las consultas de cadenas y a la creación manual de objetos personalizados, pero hace lo que desea. Es posible que pueda refinar esto un poco utilizando un ciclo Select en lugar de un foreach y tal, pero de hecho lo hice funcionar en un conjunto de entidades generadas por el diseñador de YourEntities.
void getTimes()
{
YourEntities context = new YourEntities();
DateTime firstDay = GetFirstDayOfFirstWeekOfYear();
var weeks = context.CreateQuery<System.Data.Common.DbDataRecord>(
"SELECT t.User.UserName, SqlServer.DateDiff(''DAY'', @beginDate, t.TargetDate)/7 AS WeekNumber " +
"FROM YourEntities.TrackedTimes AS t " +
"GROUP BY SqlServer.DateDiff(''DAY'', @beginDate, t.TargetDate)/7, t.User.UserName", new System.Data.Objects.ObjectParameter("beginDate", firstDay));
List<customTimes> times = new List<customTimes>();
foreach (System.Data.Common.DbDataRecord rec in weeks)
{
customTimes time = new customTimes()
{
UserName = rec.GetString(0),
WeekNumber = rec.GetInt32(1)
};
times.Add(time);
}
}
class customTimes
{
public string UserName{ get; set; }
public int WeekNumber { get; set; }
}
Bueno, podría ser más trabajo dependiendo de cómo se indexa la base de datos, pero se puede ordenar por día, luego hacer la asignación día -> semana en el lado del cliente.
Creo que el único problema que tienes es operaciones aritméticas en DateTime Type, prueba el ejemplo a continuación. Mantiene la operación en el DB como una función escalar, solo devuelve los resultados agrupados que desee. La función .Days no funcionará en su extremo porque la implementación de TimeSpan es limitada y (en su mayoría) solo está disponible en SQL 2008 y .Net 3.5 SP1, notas adicionales aquí sobre eso .
var userTimes = from t in context.TrackedTimes
group t by new
{
t.User.UserName,
WeekNumber = context.WeekOfYear(t.TargetDate)
} into ut
select new
{
UserName = ut.Key.UserName,
WeekNumber = ut.Key.WeekNumber,
Minutes = ut.Sum(t => t.Minutes)
};
Función en la base de datos agregada a su contexto (como context.WeekOfYear):
CREATE FUNCTION WeekOfYear(@Date DateTime) RETURNS Int AS
BEGIN
RETURN (CAST(DATEPART(wk, @Date) AS INT))
END
Para referencia adicional: Aquí están los métodos compatibles con DateTime que se traducen correctamente en un escenario LINQ to SQL
Debería poder obligar a la consulta a usar LINQ to Objects en lugar de LINQ a Entities para la agrupación, utilizando una llamada al método de extensión AsEnumerable .
Pruebe lo siguiente:
DateTime firstDay = GetFirstDayOfFirstWeekOfYear();
var userTimes =
from t in context.TrackedTimes.Where(myPredicateHere).AsEnumerable()
group t by new {t.User.UserName, WeekNumber = (t.TargetDate - firstDay).Days / 7} into ut
select new
{
UserName = ut.Key.UserName,
WeekNumber = ut.Key.WeekNumber,
Minutes = ut.Sum(t => t.Minutes)
};
Esto al menos significaría que la cláusula where es ejecutada por LINQ a Entidades, pero la cláusula group , que es demasiado compleja para que las Entidades lo manejen, es realizada por LINQ a Objetos.
Avísame si tienes algo de suerte con eso.
Actualizar
Aquí hay otra sugerencia, que podría permitirle usar LINQ a entidades para todo.
(t.TargetDate.Days - firstDay.Days) / 7
Esto simplemente expande la operación para que solo se realice la resta entera en lugar de la resta DateTime .
Actualmente no se ha probado, por lo que puede funcionar o no ...
Es extraño que nadie haya publicado el método GetWeekOfYear de .NET Framework todavía.
simplemente haga su primera selección, agregue una propiedad dummy weeknumber, luego recorra sobre todos ellos y use este método para establecer el número de semana correcto en su tipo, debería ser lo suficientemente rápido como para hacer jhust; un bucle entre 2 selecciona ¿no es así ?:
public static int GetISOWeek(DateTime day)
{
return System.Globalization.CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(day, System.Globalization.CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);
}
No uso Linq para Entidades, pero si admite .Year, .Day. Semana así como división y módulo enteros, entonces debería ser posible calcular el número de semana usando el algoritmo / congruencia de Zellers.
Para más detalles, consulte http://en.wikipedia.org/wiki/Zeller ''s_congruence
Si vale la pena el esfuerzo, lo dejo a usted / a los demás.
Editar:
O bien wikipedia está mal, o la forumla que tengo no es de Zeller, no sé si tiene un nombre, pero aquí está
weekno = (( 1461 * ( t.TransactionDate.Year + 4800
+ ( t.TransactionDate.Month - 14 ) / 12 ) ) / 4
+ ( 367 * ( t.TransactionDate.Month - 2 - 12 *
( ( t.TransactionDate.Month - 14 ) / 12 ) ) ) / 12
- ( 3 * ( ( t.TransactionDate.Year + 4900
+ ( t.TransactionDate.Month - 14 ) / 12 ) / 100 ) ) / 4
+ t.TransactionDate.Day - 32075) / 7
Por lo tanto, debería ser posible utilizar esto en un grupo por (Esto puede necesitar modificarse según el día en que comience su semana).
Condición. Esta es una fórmula que nunca he usado ''de verdad''. Escribí a mano (no lo recuerdo, pero puede que lo haya copiado de un libro), lo que significa que, aunque me tripliqué, revisé si mis corchetes eran correctos, es posible que la fuente estuviera equivocada. Por lo tanto, debe verificar que funcione antes de usar.
Personalmente, a menos que la cantidad de datos sea masiva, preferiría devolver siete veces los datos y filtrar localmente en lugar de usar algo como esto, (lo que tal vez explique por qué nunca he usado el forumla)
Otra solución puede ser; registros de grupos diarios y luego iterar a través de ellos. Cuando vea la semana de inicio, haga la operación de suma o recuento.
Aquí hay un código de muestra que calcula las estadísticas de ingresos por semana.
El método GetDailyIncomeStatisticsData recupera datos de la base de datos en bases diarias.
private async Task<List<IncomeStastistic>> GetWeeklyIncomeStatisticsData(DateTime startDate, DateTime endDate)
{
var dailyRecords = await GetDailyIncomeStatisticsData(startDate, endDate);
var firstDayOfWeek = DateTimeFormatInfo.CurrentInfo == null
? DayOfWeek.Sunday
: DateTimeFormatInfo.CurrentInfo.FirstDayOfWeek;
var incomeStastistics = new List<IncomeStastistic>();
decimal weeklyAmount = 0;
var weekStart = dailyRecords.First().Date;
var isFirstWeek = weekStart.DayOfWeek == firstDayOfWeek;
dailyRecords.ForEach(dailyRecord =>
{
if (dailyRecord.Date.DayOfWeek == firstDayOfWeek)
{
if (!isFirstWeek)
{
incomeStastistics.Add(new IncomeStastistic(weekStart, weeklyAmount));
}
isFirstWeek = false;
weekStart = dailyRecord.Date;
weeklyAmount = 0;
}
weeklyAmount += dailyRecord.Amount;
});
incomeStastistics.Add(new IncomeStastistic(weekStart, weeklyAmount));
return incomeStastistics;
}
Puede intentar agrupar por año y semanaNúmero obteniéndolo de la propiedad DayOfYear
var userTimes = from t in context.TrackedTimes
group t by new {t.User.UserName, Year = t.TargetDate.Year, WeekNumber = t.TargetDate.DayOfYear/7} into ut
select new
{
UserName = ut.Key.UserName,
WeekNumber = ut.Key.WeekNumber,
Minutes = ut.Sum(t => t.Minutes)
};
Puede probar un método de extensión para hacer la conversión:
static int WeekNumber( this DateTime dateTime, DateTime firstDay) {
return (dateTime - firstDay).Days / 7;
}
Entonces tendrías:
DateTime firstDay = GetFirstDayOfFirstWeekOfYear();
var userTimes = from t in context.TrackedTimes
group t by new {t.User.UserName, t.TargetDate.WeekNumber( firstDay)} into ut
select new
{
UserName = ut.Key.UserName,
WeekNumber = ut.Key.WeekNumber,
Minutes = ut.Sum(t => t.Minutes)
};
Puede usar funciones SQL también en el LINQ siempre que esté usando SQLServer:
using System.Data.Objects.SqlClient;
var codes = (from c in _twsEntities.PromoHistory
where (c.UserId == userId)
group c by SqlFunctions.DatePart("wk", c.DateAdded) into g
let MaxDate = g.Max(c => SqlFunctions.DatePart("wk",c.DateAdded))
let Count = g.Count()
orderby MaxDate
select new { g.Key, MaxDate, Count }).ToList();
Esta muestra fue adaptada de MVP Zeeshan Hirani en este hilo: un MSDN
Tenía exactamente este problema en mi aplicación de seguimiento de tiempo que necesita informar tareas por semana. Probé el enfoque aritmético pero no conseguí nada razonable. Terminé simplemente agregando una nueva columna a la base de datos con el número de semana y calculando esto para cada nueva fila. De repente, todo el dolor desapareció. Odiaba hacer esto ya que soy un gran creyente en DRY pero necesitaba avanzar. No es la respuesta que desea sino el punto de vista de otros usuarios. Estaré mirando esto para una respuesta decente.
Here está la lista de métodos soportados por LINQ to Entities.
La única opción que veo es recuperar el número de semana de DateTime.Month y DateTime.Day. Algo como esto:
int yearToQueryIn = ...;
int firstWeekAdjustment = (int)GetDayOfWeekOfFirstDayOfFirstWeekOfYear(yearToQueryIn);
from t in ...
where t.TargetDate.Year == yearToQueryIn
let month = t.TargetDate.Month
let precedingMonthsLength =
month == 1 ? 0 :
month == 2 ? 31 :
month == 3 ? 31+28 :
month == 4 ? 31+28+31 :
...
let dayOfYear = precedingMonthsLength + t.TargetDate.Day
let week = (dayOfYear + firstWeekAdjustment) / 7
group by ...