Count

[shapper]

Hello,

I am trying to count the number of errors by date and somehow return
the dates and the count in some kind of format. At the moment I have
the following:

public ??? CountByCreated(DateTime start, DateTime end) {

return _errors.Where(e => e.Created >= start && e.Created <=
end).GroupBy(e => e.Created).Select(g => new { Created = g.Key, Count
= g.Count() });

}

But in this case I am returning an anonymous type.

Maybe I should return an array? Or maybe an Dictionary?

How should I do this?

Thank you,
Miguel

 

[shapper]

[...]
       return _errors.Where(e =>  e.Created>= start&&  e.Created<=
end).GroupBy(e =>  e.Created).Select(g =>  new { Created = g.Key, Count
= g.Count() });

     }

But in this case I am returning an anonymous type.

Maybe I should return an array? Or maybe an Dictionary?

Dictionary is fine, especially if you're going to have to do look-ups on
the key later anyway.  Or you could just go ahead and define a proper
type to return.

Pete

Yes, I did the following:

_errors.Where(e => e.Created >= start && e.Created <= end).GroupBy(e
=> e.Created).ToDictionary(g => g.Key, g => g.Count());

But then I notice that the count was always 1. This is because it is
counting by DateTime and not only by date. So I changed it to:

_errors.Where(e => e.Created.Date >= start.Date && e.Created.Date <=
end.Date).GroupBy(e => e.Created.Date).ToDictionary(g => g.Key.Date, g
=> g.Count());

But now I get an error:

The specified type member 'Date' is not supported in LINQ to Entities.
Only initializers, entity members, and entity navigation properties
are supported.

Any idea what might be wrong?

I should be able to group by Date to, correct?

Thank You,
Miguel

 

[shapper]

[...]
_errors.Where(e =>  e.Created.Date>= start.Date&&  e.Created.Date<=
end.Date).GroupBy(e =>  e.Created.Date).ToDictionary(g =>  g.Key.Date, g
=>  g.Count());

But now I get an error:

The specified type member 'Date' is not supported in LINQ to Entities.
Only initializers, entity members, and entity navigation properties
are supported.

Any idea what might be wrong?

Sure.  The error message is quite clear: you are using LINQ to Entities,
and like LINQ to SQL, there are restrictions on what you can put in a
lambda expression.

With LINQ to Objects, the code just winds up executing as straight .NET
code.  You have full access to all type members in that case.

But with other forms of LINQ, including LINQ to Entities, the lambda has
to be convertible to some domain-specific instructions.  If a property
like DateTime.Date is not available in that domain, the code fails to run..

Work-arounds include restating the lambda in terms of an entity member
that does exist (for example, it may be that you can effective recreate
the Date property using some Year, Month, and Day properties that might
be available), or use LINQ to Objects instead of going directly to the
Entities framework.

Pete

Hello,

I did try other options. For example:

public IDictionary<DateTime, Int32> CountByCreated(DateTime start,
DateTime end) {
return _errors
.Where(e => e.Created.Date >= start && e.Created.Date <= end)
.GroupBy(e => EntityFunctions.TruncateTime(e.Created))
.ToDictionary(g => g.Key.Value, g => g.Count());
}

Where I get the error:
The specified type member 'Date' is not supported in LINQ to Entities.
Only initializers, entity members, and entity navigation properties
are supported.

Or:

public IDictionary<DateTime, Int32> CountByCreated(DateTime start,
DateTime end) {
DateTime startDate = start.Date;
DateTime endDate = end.Date;
return _errors.Where(e => e.Created.Date >= startDate &&
e.Created.Date <= endDate).GroupBy(e => new {e.Created.Year,
e.Created.Month, e.Created.Day}).ToDictionary(g => g.Key, g =>
g.Count());
}

Where I get the error:
Cannot implicitly convert type
'System.Collections.Generic.Dictionary<AnonymousType#1,int>' to
'System.Collections.Generic.IDictionary<System.DateTime,int>'. An
explicit conversion exists (are you missing a cast?)

With this last one I tried a few more options with no luck.

But I think the way to go should be using
EntityFunctions.TruncateTime, right?

But I keep getting errors.

Thank You,
Miguel

 

[shapper]

[...]
_errors.Where(e =>  e.Created.Date>= start.Date&&  e.Created.Date<=
end.Date).GroupBy(e =>  e.Created.Date).ToDictionary(g =>  g.Key.Date, g
=>  g.Count());

But now I get an error:

The specified type member 'Date' is not supported in LINQ to Entities.
Only initializers, entity members, and entity navigation properties
are supported.

Any idea what might be wrong?

Sure.  The error message is quite clear: you are using LINQ to Entities,
and like LINQ to SQL, there are restrictions on what you can put in a
lambda expression.

With LINQ to Objects, the code just winds up executing as straight .NET
code.  You have full access to all type members in that case.

But with other forms of LINQ, including LINQ to Entities, the lambda has
to be convertible to some domain-specific instructions.  If a property
like DateTime.Date is not available in that domain, the code fails to run..

Work-arounds include restating the lambda in terms of an entity member
that does exist (for example, it may be that you can effective recreate
the Date property using some Year, Month, and Day properties that might
be available), or use LINQ to Objects instead of going directly to the
Entities framework.

Pete

I think I got it:

return _errorRepository.Fetch()
.Where(e => EntityFunctions.TruncateTime(e.Created) >= start
&& EntityFunctions.TruncateTime(e.Created) <= end)
.GroupBy(e => EntityFunctions.TruncateTime(e.Created))
.ToDictionary(g => g.Key.Value, g => g.Count());

A site question that came up:

Is it possible to add records to the IDictionary where there is a
missing date.
So for all missing dates in that period I would insert a record with
value 0

Thank You,
Miguel

 

[shapper]

On 11/9/10 6:07 AM, shapper wrote:

[...]
_errors.Where(e =>  e.Created.Date>= start.Date&&  e.Created.Date<=
end.Date).GroupBy(e =>  e.Created.Date).ToDictionary(g =>  g.Key.Date, g
=>  g.Count());
But now I get an error:
The specified type member 'Date' is not supported in LINQ to Entities..
Only initializers, entity members, and entity navigation properties
are supported.
Any idea what might be wrong?

Sure.  The error message is quite clear: you are using LINQ to Entities,
and like LINQ to SQL, there are restrictions on what you can put in a
lambda expression.

With LINQ to Objects, the code just winds up executing as straight .NET
code.  You have full access to all type members in that case.

But with other forms of LINQ, including LINQ to Entities, the lambda has
to be convertible to some domain-specific instructions.  If a property
like DateTime.Date is not available in that domain, the code fails to run.

Work-arounds include restating the lambda in terms of an entity member
that does exist (for example, it may be that you can effective recreate
the Date property using some Year, Month, and Day properties that might
be available), or use LINQ to Objects instead of going directly to the
Entities framework.

Pete

I think I got it:

      return _errorRepository.Fetch()
        .Where(e => EntityFunctions.TruncateTime(e.Created) >= start
&& EntityFunctions.TruncateTime(e.Created) <= end)
        .GroupBy(e => EntityFunctions.TruncateTime(e.Created))
        .ToDictionary(g => g.Key.Value, g => g.Count());

A site question that came up:

Is it possible to add records to the IDictionary where there is a
missing date.
So for all missing dates in that period I would insert a record with
value 0

Thank You,
Miguel

I think I almost got it ... I am missing a item at the end. Here is my
code:

public IDictionary<DateTime, Int32> CountByCreated(DateTime start,
DateTime end) {

IEnumerable<DateTime> dates = Enumerable.Range(0, 1 +
end.Date.Subtract(start).Days).Select(offset =>
start.Date.AddDays(offset));

IDictionary<DateTime, Int32> errors = _errorRepository.Fetch()
.Where(e => EntityFunctions.TruncateTime(e.Created) >=
start.Date && EntityFunctions.TruncateTime(e.Created) <= end.Date)
.GroupBy(e => EntityFunctions.TruncateTime(e.Created))
.ToDictionary(g => g.Key.Value, g => g.Count());

return dates.ToDictionary(date => date, date =>
errors.ContainsKey(date) ? errors[date] : 0);

} // CountByCreated

Any idea what am I doing wrong?

And any tips to improve this code?

Thank you,
Miguel