Custom date format from Linq to SQL query

Question

Im using the folowing function to return a SelectList. I need a custom format on Descr, but replacing Key.ToString() to Key.ToString("DD/MM/YY") render me the error "Method 'System.String ToString(System.String)' has no supported translation to SQL.". How could i use a custom date format on Descr?

    Public Function ReturnDates(ByVal Param_Pesq_Cod As Integer) As SelectList
        Dim Qry = From E In DB.Execs _
        Where E.Pesq_Cod = Param_Pesq_Cod _
        Group E By Key = E.DataHora.Date _
        Into Group Order By Key Select New With {.Descr = Key.ToString(), .Val = Key}

        Return New SelectList(Qry, "Val", "Descr")
    End Function

Guffa · Accepted Answer · 2010-07-13 19:41:28Z

2

If you put the conversion in the LINQ to SQL query, it will try to create an SQL query from it. Get the result first, realise it to a list of dates, and make the conversion using LINQ to Objects.

I think it goes like this:

Public Function ReturnDates(ByVal Param_Pesq_Cod As Integer) As SelectList
  Dim Qry = From E In DB.Execs _
  Where E.Pesq_Cod = Param_Pesq_Cod _
  Group E By Key = E.DataHora.Date _
  Into Group Order By Key Select Key

  Dim List = _
    Qry.ToList() _
    .Select(Function(d) New With {.Descr = d.ToString(), .Val = d})

  Return New SelectList(List, "Val", "Descr")
End Function

answered Jul 13, 2010 at 19:41

Guffa

703k112 gold badges760 silver badges1k bronze badges

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

Collectives™ on Stack Overflow

Custom date format from Linq to SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related