Convert sql query into linq getting error in converting date

Question

My Sql Query Is:

select count(*),CONVERT(varchar, AddedOn, 101) 
from MemberNotifications where IsActive=1  
group by CONVERT(varchar, AddedOn, 101) 
order by CONVERT(varchar, AddedOn, 101) desc

but i m not able to get result, in following attempt

    List<NotificationCounts> lst =
(from mn in this.MemberNotifications 
 where  mn.UId.Equals(friendId) 
select new NotificationCounts{ NotificationDate = mn.AddedOn.ToString("mm/dd/yyyy") })
.ToList<NotificationCounts>();

I want to get only list of date in string for mat but it is giving an exception

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

Is there any solution for this error?

what is the type of `NotificationDate` in `NotificationCounts` class — Damith, May 15 '13 at 08:09

score 0 · Answer 1 · answered May 15 '13 at 07:47

0

true... linq can't transform the .ToString into a SQL instruction... need to get the plain value of mn.AddedOn and transform it after the database get. Hope it helps

answered May 15 '13 at 07:47

stealth

16
1

i have did it but i want to use group by on date and AddedOn is a datetime field and i want group the rows only by Date – Pushpendra May 15 '13 at 07:50
Retrive data to anonymous type and then you can use the ToString there. The only problem is when retrieving to database. – stealth May 16 '13 at 09:45

score 0 · Answer 2 · edited May 23 '17 at 12:29

You can't call ToString() there because there is no translation to SQL. You have to call ToList() first and after that you can manipulate the in memory object like you want:

List<NotificationCounts> lst =
    (from mn in this.MemberNotifications 
     where  mn.UId.Equals(friendId) 
     select 
       new { 
            NotificationDate = mn.AddedOn
       })
    .ToList()
    .Select(n => new NotificationCounts 
                  {
                      NotificationDate  = n.NotificationDate.ToString("mm/dd/yyyy")
                  });

EDIT: For grouping by date see this question: LINQ to Entities group-by failure using .date In short: Use the EntityFunctions.TruncateTime Method

score 0 · Answer 3 · edited May 23 '17 at 10:32

Just save the string to a temp variable and then use that in your expression:

Sample:

var strItem = item.Key.ToString();
IQueryable<entity> pages = from p in context.pages
                           where  p.Serial == strItem
                           select p;

The problem is that ToString() isn't really executed, it is turned into a MethodGroup and then parsed and translated to SQL. Since there is no ToString() equivalent, the expression fails. Check this link for more infos

score 0 · Answer 4 · answered May 15 '13 at 07:57

Use this (Not tested)

List<NotificationCounts> lst = (from mn in this.MemberNotifications 
                                where  mn.UId.Equals(friendId) select mn).ToList()
                               .Select(mn=>new NotificationCounts
                               { 
                                  NotificationDate = mn.AddedOn.ToString("mm/dd/yyyy") 
                               }).ToList();

Convert sql query into linq getting error in converting date

4 Answers4