Вопрос по linq, entity-framework, c#, linq-to-entities – Группировка по неделям в LINQ to Entities

39

У меня есть приложение, которое позволяет пользователям вводить время, которое они проводят за работой, и я пытаюсь создать для этого хорошую отчетность, которая использует LINQ to Entities. Потому что каждыйTrackedTime имеетTargetDate которая является просто «датой» частьDateTimeотносительно просто сгруппировать время по пользователю и дате (я опускаю предложения "где" для простоты):

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)
                };

БлагодаряDateTime.Month Свойство, группировка по пользователю и месяцу лишь немного сложнее:

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)
                };

Теперь самое сложное. Есть ли надежный способ группировки по неделям? Я попробовал следующее на основеэтот ответ на аналогичный вопрос 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)
                };

Но LINQ to Entities, по-видимому, не поддерживает арифметические операции над объектами DateTime, поэтому он не знает, как это сделать.(t.TargetDate - firstDay).Days / 7.

Я рассмотрел создание представления в базе данных, которое просто отображает дни в недели, а затем добавляет это представление в мой контекст Entity Framework и присоединяется к нему в моем запросе LINQ, но для такой работы кажется, что это большая работа. Есть ли хороший обходной путь для арифметического подхода? Что-то, что работает с Linq to Entities, что я могу просто включить в оператор LINQ, не касаясь базы данных? Какой-нибудь способ рассказать Linq Entities, как вычесть одну дату из другой?

Summary

Я хотел бы поблагодарить всех за их вдумчивые и творческие ответы. После всего этого взад и вперед кажется, что реальный ответ на этот вопрос - «подождать до .NET 4.0». Я собираюсь отдать награду Нолдорину за то, что он дал самый практичный ответ, который все еще использует LINQ, со специальным упоминанием Якоба Проффитта за то, что он придумал ответ, который использует Entity Framework без необходимости изменений на стороне базы данных. Были и другие отличные ответы, и если вы рассматриваете вопрос впервые, я настоятельно рекомендую прочитать все те, за которые проголосовали, а также их комментарии. Это действительно был превосходный пример силы StackOverflow. Спасибо вам всем!

Не могли бы вы уточнить требования, касающиеся межгодовых запросов? От виденияGetFirstDayOfFirstWeekOfYear в вашем примере я предположил, что вы в основном запрашиваете только записи, для которых год можно считать одинаковым. Вы на самом деле смотрите на запрос произвольных дат, считая недели от произвольно указанной точки (которая может быть или не быть первым днем года)? Или это все еще только один год, и он просто корректируется с учетом «короткой недели»? в начале года будет достаточно? Pavel Minaev
Да, прости. СGetFirstDayOfFirstWeekOfYear Я пытался указать, что была какая-то дата, из которой я мог бы вычесть все остальные даты, чтобы выяснить, в какой неделе они находятся. В конечном счете, я хотел бы представлять недели как диапазоны дат (7/12/09 - 7 / 18/09), но я подумал, что это выходит за рамки этого вопроса. StriplingWarrior

Ваш Ответ

13   ответов
2

никто еще не опубликовал метод GetWeekOfYear .NET Framework.

просто сделайте первый выбор, добавьте фиктивное свойство weeknumber, затем зациклите их все и используйте этот метод, чтобы установить правильный номер недели для вашего типа, он должен быть достаточно быстрым, чтобы выполнить только один цикл из двух выборок, не так ли ?:

public static int GetISOWeek(DateTime day)
{
  return System.Globalization.CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(day, System.Globalization.CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);
}
Хорошо знать, что метод существует, но, поскольку он не поддерживается Entity Framework, он не отвечает на вопрос. StriplingWarrior
-1

но я думаю, что в лямбда-выражениях C # 3.0 речь идет о декларативном программировании.

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);
}
Трудность, с которой я сталкиваюсь, заключается не в создании синтаксиса, который переводит дни в недели. Проблема заключается в том, что Entity Framework не может оценить & quot; DateTime.MaxValue - x & quot; так что это не сработает, как только вы попытаетесь действительно оценить запрос. StriplingWarrior
2

Вот список методов, поддерживаемых LINQ to Entities.

Единственный вариант, который я вижу, - это получить номер недели из DateTime.Month и DateTime.Day. Что-то вроде этого:

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 ... 
Если я правильно понимаю ваш пример, вы просто находите день года и делите его на 7. Это было бы правильно, если бы я считал недели, начинающиеся в первый день года. Вместо этого я стараюсь, чтобы недели всегда начинались в определенный день (например, в воскресенье). Кроме того, было бы хорошо, чтобы запрос работал по годам (например, между декабрем 09 и январем 10). +1 для списка поддерживаемых методов, хотя! StriplingWarrior
2

но если он поддерживает .Year, .Day .Week, а также целочисленное деление и по модулю, то должна быть возможность определить номер недели с использованием алгоритма / конгруэнции Целлера.

Для более подробной информации смотритеhttp://en.wikipedia.org/wiki/Zeller& APOS; s_congruence

Стоит ли это усилий, я оставлю до вас / других.

Редактировать:

Либо Википедия не так, либо форум, который у меня есть, не является целлером, я не знаю, есть ли у него имя, но вот оно

 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 

Так что должно быть возможно использовать это в группе (Это может потребоваться настроить в зависимости от того, в какой день начинается ваша неделя).

Proviso. Это формула, которую я никогда не использовал «для реального». Я набрал текст вручную (не помню, но, возможно, я скопировал его из книги), что означает, что, хотя я проверял трижды, у меня были правильные скобки, возможно, источник был неверным. Итак, вы должны убедиться, что он работает, прежде чем использовать.

Лично, если объем данных не огромен, я бы предпочел возвращать данные в семь раз и фильтровать их локально, а не использовать что-то подобное (что, возможно, объясняет, почему я никогда не использовал форум)

Я согласен с вашей оценкой: лучше потерять производительность и улучшить читаемость и т. Д. Я понимаю, что я требовал больше, чем действительно "практично". как ответ на этот вопрос, но я думаю, что это стоило того, чтобы поощрить творческие ответы. +1 за сумасшедший ответ, который мог бы действительно работать. StriplingWarrior
-2

это может быть больше работы в зависимости от того, как БД индексируется, но вы можете просто отсортировать по дням, а затем сделать день - & gt; недельное картирование на стороне клиента.

извиняюсь. Я только что прочитал напоминание о вашем вопросе, которое фактически предлагает ту же технику.
Если проблема связана с подключением к db-приложению, я предлагаю вам создать представление с дополнительным вычисляемым столбцом, который рассчитывает неделю года в сочетании с номером года, например 012000..542000. И возврат сгруппирован по этому вычисленному столбцу.
Соединение между базой данных и приложением является одним из самых больших узких мест в таком приложении. То, что вы предлагаете, было бы достаточно легко закодировать, но это привело бы к 7-кратному переносу данных из БД, плюс добавленное время для приложения, чтобы суммировать все возвращаемые данные - операция, при которой базы данных оптимизируются. за. Это может сработать, но это не совсем то, что я ищу. StriplingWarrior
-1

получив их из свойства 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)
                };
Да, извините, я использую llblgen, и он хорошо работает в простых запросах.
Если вы посмотрите на комментарии в принятом ответе, то увидите, что Нолдорин спросил об использовании DayOfYear. Оказывается, DayOfYear не поддерживается Linq to Entities. Ответ Павла включает ссылку на список поддерживаемых методов. Кроме того, это сделало бы каждую «неделю» начните со дня, когда начался год, который не очень хорошо отвечает потребностям большинства людей в бизнесе. StriplingWarrior
2

это возможно, но это огромный PITA и он обходит часть LINQ Entity Framework. Потребовалось некоторое исследование, но вы можете выполнить то, что вы хотите, с помощью специфических для поставщика функций SqlServer SqlServer.DateDiff () и «Entity SQL». Это означает, что вы возвращаетесь к строковым запросам и вручную заполняете пользовательские объекты, но это делает то, что вы хотите. Возможно, вы сможете немного уточнить это, используя Select вместо цикла foreach и т. Д., Но на самом деле я получил это для работы с созданным дизайнером набором сущностей 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; }
}
Имейте в виду, что базовый объект «недели» все еще IQueryable, и вы можете построить его итеративно. Так, например, после вышесказанного вы можете сделать что-то вроде недели = недели. Где (& quot; t.User = & quot; Смит & quot; & quot;);
Вероятно, не результат, по которому я бы пошел, по понятным причинам, но +1 за серьезные усилия и нестандартное мышление! StriplingWarrior
-2

static int WeekNumber( this DateTime dateTime, DateTime firstDay) {
    return (dateTime - firstDay).Days / 7;
}

Тогда у вас будет:

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)
                };
Моя ошибка - я использовал только linq-to-objects.
Это не сработает, потому что LINQ to Entities не может преобразовать вызов метода расширения WeekNumber во что-то, что он может применить к слою данных - короче, по той же причине, по которой я не могу использовать арифметику напрямую. StriplingWarrior
40

ts вместо LINQ to Entities для группировки, используя вызовAsEnumerable метод расширения.

Попробуйте следующее:

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)
    };

Это будет по крайней мере означать, чтоwhere предложение выполняется LINQ для сущностей, ноgroup Предложение, которое является слишком сложным для сущностей для обработки, выполняется LINQ to Objects.

Дайте мне знать, если вам повезет с этим.

Update

Вот еще одно предложение, которое может позволить вам использовать LINQ to Entities для всего этого.

(t.TargetDate.Days - firstDay.Days) / 7

Это просто расширяет операцию, так что выполняется только целочисленное вычитание, а неDateTime вычитание.

В настоящее время он не проверен, поэтому он может или не может работать ...

@StriplingWarrior: dmo не предлагал то же самое (по крайней мере, его решение, похоже, не сработало), но да, я понимаю вашу точку зрения ... Это действительно становится проблемой передачи данных.
Смотрите мой обновленный ответ.
Как я прокомментировал для dmo, этот подход, вероятно, будет функциональным, но он устранит многие преимущества использования Linq для сущностей. Это приведет к гораздо большему количеству данных, передаваемых между базой данных и приложением, чем это необходимо. Насколько я понимаю, правильное решение - это то, которое позволяет Linq to Entities выполнять группировку на стороне базы данных. StriplingWarrior
+1, тем не менее, за то, что не только придумал практическое (если не идеальное) решение, но также включил чистый пример кода, чего не смог сделать dmo. StriplingWarrior
DateTime не имеет свойства .Days. .Days происходит от объекта TimeSpan, который создается, когда вы вычитаете два DateTimes, что возвращает нас к нашей первоначальной проблеме, потому что мы не можем вычесть два DateTimes. Я бы попробовал использовать .Ticks и умножить на количество тиков в день, но .Ticks также не работает в Entity Framework. Основываясь на некоторых других форумах, похоже, что они приближаются к крайнему сроку для .NET 3.5, они решили прекратить свою ограниченную поддержку DateDiff, чтобы обеспечить более полную поддержку в .NET 4. Возможно, мне просто придется подождать. StriplingWarrior
6

которое должно сообщать о задачах по неделям. Я попробовал арифметический подход, но не получил ничего разумного, работающего. В итоге я просто добавил новый столбец в базу данных с номером недели и вычислил его для каждой новой строки. Внезапно вся боль ушла. Я ненавидел это делать, потому что я большой сторонникDRY но мне нужно было добиться прогресса. Не тот ответ, который вам нужен, а точка зрения другого пользователя. Я буду смотреть это для достойного ответа.

Похоже, что у вас был шаблон, похожий на вид, который я думал создать. Спасибо, что нашли время, чтобы сообщить мне, что это сработало для вас. StriplingWarrior
1

ежедневные групповые записи, а затем перебирать их. Когда вы увидите начало недели, выполните операцию суммирования или подсчета.

Вот пример кода, который рассчитывает статистику доходов по неделям.

GetDailyIncomeStatisticsData method retrieves data from database on daily bases.

   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;
    }
1

что единственная проблема, с которой вы столкнулись, - это арифметические операции над типом DateTime, попробуйте пример ниже. Он сохраняет операцию в БД как скалярную функцию, возвращает только сгруппированные результаты, которые вы хотите. Функция .Days не будет работать на вас, потому что реализация TimeSpan ограничена и (в основном) доступна только в SQL 2008 и .Net 3.5 SP1,дополнительные заметки здесь о том, что.

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)
    };

Функция в базе данных добавлена в ваш контекст (как context.WeekOfYear):

CREATE FUNCTION WeekOfYear(@Date DateTime) RETURNS Int AS 
BEGIN
RETURN (CAST(DATEPART(wk, @Date) AS INT))
END

Для дополнительной справки: Вот поддерживаемые методы DateTime, которые правильно переводятся в сценарии LINQ to SQL

Это выглядит многообещающе. Похожа на идею View, которая у меня была, но намного более пригодна для повторного использования. Я посмотрю, сработает ли это завтра утром. Если это так, вы, вероятно, получите вознаграждение. StriplingWarrior
Вы были очень близки, но мы снова огорчены недостатками текущей версии Entity Framework, которая не позволяет нам вызывать пользовательские функции с помощью Linq to Entities. +1 за такой полный ответ, хотя. StriplingWarrior
8

если вы используете 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();

Этот образец был адаптирован из MVP Zeeshan Hirani в этой теме: MSDN

Можете ли вы расширить этот пример, чтобы показать, как это позволит кому-то группировать предметы по неделям? StriplingWarrior
Я прошу прощения за путаницу в этом посте; Я отредактировал свой ответ, чтобы вместо этого использовать SqlFunctions, обнаружив, что я не могу использовать две вложенные функции EDM в Linq; Процессор Linq взрывается с неверными псевдонимами таблиц в операторе SQL.
В основном это будет связано с использованием AddDays; Я проработаю полный образец и опубликую его.
Я хотел сказать, что вы можете использовать ВСЕ функции даты таким образом, если вы предоставляете связанные функции EDM и немного переписываете свои запросы, чтобы быть менее объектно-ориентированными. Я использую это на запрос к базе данных самостоятельно.

Похожие вопросы