none
Count in Date Range RRS feed

  • Question

  • Hello,

    I need to count the number of POSTS in a date range and by period.

    For examples:

    - Count, by minute, the POSTS created from 10 of February to 12 of February;

    - Count, by day, the POSTS created from 01 of February to 15 of February;

    I created the following:

    context.Posts.
      .Where(x => x.Created >= request.Start && x.Created <= request.End)
      .GroupBy(x => x.Created)
      .Select(x => new { Date = x.Key, Count = x.Count() })
      .ToList();

    However, I am not sure how to Group by minute, by day, etc.

    And I would like the query to run on the database.

    How can I do this?

    Thank You,

    Miguel

    Wednesday, February 15, 2012 6:25 PM

Answers

  • Hi MDMoura,

    With such a T-SQL with many functions, I suggest you not to translate it into Linq to Entities, just call the T-SQL directly. Because not all functions the Linq to Entities can translate to and though it could, the performance may low than call the T-SQL directly. You don't know how the Linq to Entities translate to the T-SQL, it may translate to a very complext one, it certainly will affect the performance.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Friday, February 24, 2012 2:30 AM
    Moderator

All replies

  • Hi MDMoura,

    Welcome to MSDN Forum.

    Linq query can't run on the database, if you want the query run on the database, you need to write T-SQL, below is the link which you post in another forum, the MCC's relpy can help you to solve the issue.

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/575fcdc8-8593-4a68-95b7-6a298f24622e/#575fcdc8-8593-4a68-95b7-6a298f24622e

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Friday, February 17, 2012 7:46 AM
    Moderator
  • I meant, I don't want to load all files into memory ... Just that.

    Friday, February 17, 2012 1:37 PM
  • Hi MDMoura,

    I'm not clear about the meaning, load which files into memory?

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, February 20, 2012 3:14 AM
    Moderator
  • Hi MDMoura,

    Have you solved the issue? I look foward to hearing from you.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, February 23, 2012 12:58 AM
    Moderator
  • Yes,

    What I am looking for is to translate such code into LinqToEntities.

    Thursday, February 23, 2012 4:16 PM
  • I am using the following:

          IEnumerable<DateTime> dates = Enumerable.Range(0, 1 + end.Subtract(start.Days).Select(x => start.Date.AddDays(x));
         
          IDictionary<DateTime, Int32> a = users.Set()
            .Where(x => EntityFunctions.TruncateTime(x.LastLogin) >= start.Date && EntityFunctions.TruncateTime(x.LastLogin) <= end.Date)
            .GroupBy(x => EntityFunctions.TruncateTime(x.LastLogin))
            .Select(x => new { Key = x.Key.Value, Value = x.Count() })
            .ToDictionary(k => k.Key, v => v.Value);

    I am not sure if this is the best approach.

    I am defining the dates first because when there is not items on that that I want the date to still be present but with value 0.

    Anyway, what do you think?

    Thank You,

    Miguel


    Thursday, February 23, 2012 5:10 PM
  • Hi MDMoura,

    With such a T-SQL with many functions, I suggest you not to translate it into Linq to Entities, just call the T-SQL directly. Because not all functions the Linq to Entities can translate to and though it could, the performance may low than call the T-SQL directly. You don't know how the Linq to Entities translate to the T-SQL, it may translate to a very complext one, it certainly will affect the performance.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Friday, February 24, 2012 2:30 AM
    Moderator