Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 836
  • Last Modified:

LinqToSQL Expression

Hi,
I am trying to convert a datetime that's coming from the database into a particular type of string for the resulting json to work with, but I am getting this error: "The LINQ expression node type 'Invoke' is not supported in LINQ to Entities.".  What am I doing wrong?

  static Func<DateTime, string> ConvertDateForJSON = a => "new Date(" + SqlFunctions.StringConvert((double)a.Date.Year) + ", " + SqlFunctions.StringConvert((double)a.Date.Month) + ", " + SqlFunctions.StringConvert((double)a.Date.Day) + ")";
 
   lst = (from h in entities.Holidays
                           join s in entities.Sicknesses on h.userID equals s.UserID
                           join p in entities.People on h.userID equals p.ID
                           select new
                    CalendarRecord
                    {
                        id = h.StartDate != null ? "h" + SqlFunctions.StringConvert((double)h.ID) : "s" + SqlFunctions.StringConvert((double)s.ID),
                        title = p.FullName,
                        start = h.StartDate != null ? ConvertDateForJSON(h.StartDate) : ConvertDateForJSON(s.StartDate),
                        end = h.EndDate != null ? ConvertDateForJSON(h.EndDate) : ConvertDateForJSON(s.EndDate),
                        allDay =  h.FullDay == false ? "false" : "true",
                        className = h.StartDate != null ? "Holiday" : "Sick",
                    }
                   ).ToList();

Thanks!
0
itfocus
Asked:
itfocus
  • 3
  • 2
1 Solution
 
Gary DavisDir Internet SvcsCommented:
Perhaps Linq is unable to convert the statement to SQL. So break up the statement into one that selects/joins the data into a result and then a second non-Sql Linq statement to do the projection with the date conversion on the result of the 1st statement.

Gary Davis
0
 
käµfm³d 👽Commented:
LinqToSql actually builds a SQL query to send to the database. Your problem is that there is no equivalent ConvertDateForJSON in SQL. One would think that inline functions would be executed prior to building the SQL statement for the database, but that is just not how L2S works (in my observations). You will need to send the query off to the database, and then do a transformation on the result. (Note:  this is what gardavis mentioned.)
0
 
Gary DavisDir Internet SvcsCommented:
I often use LinqPad (one of the best developer tools I use) and it can show you the Sql that gets generated for the Linq (toSql) statements.

Check http://www.linqpad.com
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
itfocusAuthor Commented:
Thanks for the responses.  I thought the reason would be something like that.  However, the only way around this I could think of was to loop through the resulting list<> and make the format updates there.  This rather negates the benefits of using Linq though, and seems like an unwieldy way to achieve this.  Is there any other way?  SQL does allow conversions such as that I am trying to do, but I can't seem to get Linq to accept it.

Thanks
0
 
itfocusAuthor Commented:
I realised I was overthinking this, and actually managed to fix it in a much simpler way:

 lst = (from h in entities.Holidays
                           join s in entities.Sicknesses on h.userID equals s.UserID
                           join p in entities.People on h.userID equals p.ID
                           select new
                    CalendarRecord
                    {
                        id = h.StartDate != null ? "h" + SqlFunctions.StringConvert((double)h.ID) : "s" + SqlFunctions.StringConvert((double)s.ID),
                        title = p.FullName,
                        start = h.StartDate != null ? SqlFunctions.StringConvert((decimal)h.StartDate.Year) + ", " +  SqlFunctions.StringConvert((double)h.StartDate.Month) + ", " +  SqlFunctions.StringConvert((double)h.StartDate.Day) : SqlFunctions.StringConvert((decimal)s.StartDate.Year) + ", " +  SqlFunctions.StringConvert((double)s.StartDate.Month) + ", " +  SqlFunctions.StringConvert((double)s.StartDate.Day),
                        end = h.EndDate != null ? SqlFunctions.StringConvert((decimal)h.EndDate.Year) + ", " + SqlFunctions.StringConvert((double)h.EndDate.Month) + ", " + SqlFunctions.StringConvert((double)h.EndDate.Day) : SqlFunctions.StringConvert((decimal)s.EndDate.Year) + ", " + SqlFunctions.StringConvert((double)s.EndDate.Month) + ", " + SqlFunctions.StringConvert((double)s.EndDate.Day),
                        allDay =  h.FullDay == false ? "false" : "true",
                        className = h.StartDate != null ? "Holiday" : "Sick",
                    }
                   ).ToList();

Thanks all for the advice.
0
 
itfocusAuthor Commented:
Was overthinking the thing; solution was much simpler than what I was initially trying to do.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now