How to format 'DateTime?' in dd / mm / yyyy format using Linq?

Question

How to format 'DateTime?' in dd / mm / yyyy format using Linq?

Navigation

#1 by (3 votes)

0

I have query below, but when I try to format the field Data_nascimento and Data_exclusao with .ToString("dd/MM/yyyy") it returns me a runtime error.

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

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 to materialize the object?

In other words, does it throw a query into the database that returns the data fields in the format I want?

c# entity-framework linq-to-entities

asked by anonymous 11.11.2016 / 13:24

1 answer

Database error Missing Driver Name property Variable-sized object may not be initialized

score 3 · Accepted Answer

It is not possible because Entity can not translate ToString(format) to a Sql function, so it will be necessary to execute the query without formatting the date, only to 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 Value without checking HasValue , so if Data_nascimento or Data_exclusao are null, you would have a run-time error.

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