How to format`Datetime? ` in dd/mm/yyyy format using Linq?

Question

I have the query below, but while trying to format the field Data_nascimento and Data_exclusao with the .ToString("dd/MM/yyyy") returns me to an error at runtime.

The 'System.String Tostring(System.String)' method has no conversions SQL supported.

var dependentes = (from dependentes in ctx.Tabela_DocsItens
                    where itens.Controle == dependentes.Controle_titular
                    select new listaDependentes
                    {
                         carteirinhaSegurado = dependentes.Cod_identific,
                         nomeSegurado = dependentes.Segurado,
                         dataNascimento = dependentes.Data_nascimento.Value.ToString("dd/MM/yyyy"),
                         dataExclusao = dependentes.Data_exclusao.Value.ToString("dd/MM/yyyy"),
                    }).ToList();

Is there any way to convert this before materializing the object, ie throw to the database a query that returns the data fields in the format I want?

Answer 1

This is not possible as the Entity cannot translate the ToString(format) for a function Sql, Then it will be necessary to execute the query without formatting the date, only then format the date.

var dependentes = (
    from dependentes in ctx.Tabela_DocsItens
    where itens.Controle == dependentes.Controle_titular
    select new {
        carteirinhaSegurado = dependentes.Cod_identific,
        nomeSegurado = dependentes.Segurado,
        dataNascimento = dependentes.Data_nascimento,
        dataExclusao = dependentes.Data_exclusao,
    }).AsEnumerable().Select(x => new listaDependentes {
        carteirinhaSegurado = x.carteirinhaSegurado,
        nomeSegurado = x.nomeSegurado,
        dataNascimento = x.dataNascimento?.Value.ToString("dd/mm/yyyy") ?? String.Empty,
        dataExclusao = x.dataExclusao?.Value.ToString("dd/mm/yyyy") ?? String.Empty,
    }).ToList();

Just a small detail, you were accessing the Value without checking the HasValue, then case Data_nascimento or Data_exclusao are null, you would have an error at runtime.

In the above example, this problem has been solved using the syntax of C#6, if you are using a previous version, you will need to use a ternary operator.