Translating SQL statement with dates to Linq-to-Sql for use with EF4

Question

I have the following SQL command:

SELECT        CONVERT(varchar, Logged, 103) AS Visited, COUNT(ID) AS Totals
FROM            tblStats
GROUP BY CONVERT(varchar, Logged, 103)
ORDER BY Visited DESC

I want to translate this into a L2S statement that can be used with the Entity Framework, but in working with datetime types, I'm getting various errors depending on how I try to attack the problem.

Approach:

var results = from s in db.Stats
      group s by (s.Logged.Date.ToString()) into Grp
      select new { Day = Grp.Key, Total = Grp.Count() };

Error:

LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression.

Approach:

var results = from s in db.Stats
              group s by (s.Logged.Date) into Grp
              select new { Day = Grp.Key, Total = Grp.Count() };

Error:

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

What syntax do I need to make the query work?

Jeff Ogata · Accepted Answer · 2011-01-24 21:43:54Z

1

Try using the EntityFunctions.TruncateTime method:

var results = from s in db.Stats
              group s by EntityFunctions.TruncateTime(s.Logged) into Grp
              select new { Day = Grp.Key, Total = Grp.Count() };

answered Jan 24, 2011 at 21:43

Jeff Ogata

58k20 gold badges117 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Phil.Wheeler Over a year ago

That's the problem! Thanks. Now I have a different issue, but I'll set up a new question for that.

szeliga · Accepted Answer · 2011-01-24 21:46:37Z

0

Do you need the Date section in s.Logged.Date?

Have you tried this:

var results = from s in db.Stats
              group s by (s.Logged) into Grp
              select new { Day = Grp.Key, Total = Grp.Count() };

I'm assuming that Logged is the property (column in the table).

EDIT: you guys are way to quick.

answered Jan 24, 2011 at 21:46

szeliga

6216 silver badges12 bronze badges

1 Comment

Kirk Broadhurst Over a year ago

If you don't use the Date part then you will group by the full datetime, including the time component.

Collectives™ on Stack Overflow

Translating SQL statement with dates to Linq-to-Sql for use with EF4

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related