Perform convert(NVARCHAR, ValidDate.Date, 102) with Linq to entities

Question

I have an SQL statement:

Select convert(NVARCHAR, MyDate, 102) + ' ' + convert(NVARCHAR, AccountId) AS Temp from MyTable

I want to convert it with Linq to Entities. I was suggested to query MyDate and AccountId and with Linq to object I can use .ToString(). But I want the sql server to make the conversion by performing the convert() method.

I was happy to find SqlClient class which helps to perform methods on the server side. Unfortunately, there is no Convert() method there only StringConvert() which can only convert a double to string. Can I perform convert(NVARCHAR, MyDate, 102) on the server?

Maybe import it as a function? https://learn.microsoft.com/en-us/ef/ef6/modeling/designer/stored-procedures/query — Charlieface, Jul 14 '21 at 15:21
@Charlieface You are right I can write a stored procedure and import it. Or easier I can send the raw sql statement to the server, but in that case I did not use too much Linq to Entities. — Istvan Heckl, Jul 15 '21 at 07:06

score 0 · Accepted Answer · answered Jul 15 '21 at 12:04

I have found confirmation that you can not use Cast and Convert in Linq to entities which is sad. The referenced page suggested to use User defined functions but UDF has performance impact.

I realized though I can not convert a date to string, I can convert a date to date part (int), that to double, and that to string.

Temp = SqlFunctions.StringConvert((double)SqlFunctions.DatePart("year", MyDate), 4) + "."
            + SqlFunctions.StringConvert((double)SqlFunctions.DatePart("month", MyDate), 2) + "."
            + SqlFunctions.StringConvert((double)SqlFunctions.DatePart("day", MyDate), 2) + ". - "
            + SqlFunctions.StringConvert((double)x.AccountId, 5)

This is not the nicest solution because I can use "01" instead of " 1".

Perform convert(NVARCHAR, ValidDate.Date, 102) with Linq to entities

1 Answers1