Solved

Linq Grouping By Day

Posted on 2014-04-09
3
263 Views
Last Modified: 2016-02-15
Can someone tell me where I'm going wrong here?

Dim q = From s In db.Spots Where s.StartDate >= New DateTime(2009, 2, 22) And s.StartDate <= New DateTime(2014, 5, 22) _
                Group s By day = s.StartDate.Value.Date Into g = Group _
                Select New With {.Date = day.ToString("dd/MM/yyyy"), .Count = g.Count()}

Open in new window


Locals shows 'Error' and if I do q.count, I get An unhandled exception of type 'System.NotSupportedException' occurred in System.Data.Linq.ni.dll
0
Comment
Question by:Dodsworth
[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
3 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39988659
Is this Linq to SQL or Linq to Entity Framework?
0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39988679
sql
0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 39988758
Hi Dodsworth;

Linq to SQL does not translate the method ToString() into SQL type queries so therefore it can not be use. What you can do is in place of this statement in your query, .Date = day.ToString("dd/MM/yyyy"), replace it with this, .Date = day.Date, then before using it in your code convert it to a string in the correct format. Or you can create a class that will convert it for you automatically as shown below.

Dim q = From s In db.Spots _
        Where s.StartDate >= New DateTime(2009, 2, 22) And s.StartDate <= New DateTime(2014, 5, 22) _
        Group s By day = s.StartDate.Value.Date Into g = Group _
        Select New Data With 
        {
        	  .Date = day.Date, 
        	  .Count = g.Count()
        }
        
Public Class Data
    Private _date As String
    Public Property [Date]() As String
        Get
            Return _date
        End Get
        Set
            _date = CType(Value, DateTime).ToString("dd/MM/yyyy")
        End Set
    End Property
    
    Public Count As Integer
End Class

Open in new window

0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

617 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