Solved

LINQ Group By Key Format Date?

Posted on 2013-11-10
4
1,871 Views
Last Modified: 2016-02-10
The group by Year/Month works fine, just cannot format the Key to a datetime. Keep getting this error:

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

[Linq]
var model = (from b in context.Budgets
                         let j = new ViewModel { Budget = b }
                         let dt = j.Budget.Created
                         group j by new { y = dt.Year, m = dt.Month } into g
                         select new Group<string, ViewModel>
                         {
                             Key = Convert.ToDateTime(g.Key).ToString("MM/yyyy"),
                         }
                          ).ToList();
0
Comment
Question by:WorknHardr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 39637793
Hi WorknHardr;

In this statement, "Convert.ToDateTime(g.Key).ToString("MM/yyyy")", g.Key is an anonymous data type and can not be converted to a DateTime object with the Convert.ToDateTime method because there is no method that accepts an anonymous type as a parameter. But if it did Linq to Entity Framework doest not implement the ToString() method into a SQL native statement. What you can do in this case is the following

using System.Data.Objects.SqlClient
// You will also need to add a reference to System.Data.Entity.dll

var model = (from b in context.Budgets
             let j = new ViewModel { Budget = b }
             let dt = j.Budget.Created
             group j by new { y = dt.Year, m = dt.Month } into g
             let month = (double) g.Key.Month
             let year = (double) g.Key.Year
             select new Group<string, ViewModel>
             {
             	   Key = SqlFunctions.StringConvert(month, 2) + "/" + SqlFunctions.StringConvert(year, 4) 
             }).ToList();
             
foreach (var element in results)
{
	 // Because the above query will not fill in 0 in positions where there is no digit you will need to replace String " " with "0".
         // Not knowing what the structure of the object Group<string, ViewModel> is I will leave this to you to do using a String.Replace(" ", "0")
}    

Open in new window

0
 

Author Comment

by:WorknHardr
ID: 39640029
Wow, great code!

I'm surprise Entity doesn't have a function like so:

           EntityFunctions.TruncateDate(j.Budget.Created)
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39640607
Maybe in some future release.

Glad that worked out for you.
0
 

Author Closing Comment

by:WorknHardr
ID: 39640747
Thx
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question