Project a DateTime from an IQueryable to a string in a custom class

Question

Hi. I have this code running on the server part of my Silverlight application with WCF Ria Services:

public IQueryable<PersonePM> GetPersoneByCognome(string cognome)
    {
        return
            (from p in ObjectContext.Persone
             where p.Cognome.ToLower().Contains(cognome.Trim().ToLower())
             select new PersonePM
             {
                 Id = p.ID,
                 Cognome = p.Cognome,
                 Nome = p.Nome,
                 Sesso = p.IsMaschio == true ? "M" : "F",
                 StringaCognomeNome = p.Cognome + " " + p.Nome,
                 DataNascita = p.DataNascita == null ? DateTime.MinValue : p.DataNascita.Value,
                 LuogoNascita = (p.IsMaschio == true ? "Nato a " : "Nata a ") + p.Citta.Denominazione + " (" + p.Citta.Provincia.Trim() + ")",
                 CodiceFiscale = p.CodiceFiscale,
                 StringaNascita =
                    (p.IsMaschio == true ? "Nato a " : "Nata a ") + p.Citta.Denominazione + " (" + p.Citta.Provincia.Trim() + ")"
                 +
                 (p.DataNascita != null ?
                     (((p.DataNascita.Value.Day == 1) || (p.DataNascita.Value.Day == 8) || (p.DataNascita.Value.Day == 11)) ? " l'" : " il ") +
                     p.DataNascita.Value : string.Empty)
             });            
    }

public class PersonePM
{
    [Key]
    public Guid Id { get; set; }
    public string Cognome { get; set; }
    public string Nome { get; set; }
    public string Sesso { get; set; }
    public string StringaCognomeNome { get; set; }
    public DateTime DataNascita { get; set; }
    public string LuogoNascita { get; set; }
    public string StringaNascita { get; set; }
    public string CodiceFiscale { get; set; }
}

Because of Italian language, I'd like to format where a person is born and when in a common language form for best users comprehension. But the code above doesen't work because Linq-to-Entities is not capable of trasform a DateTime to a String (the whole story is a little different.. but let's say that for short); the error is thrown here:

StringaNascita =
                    (p.IsMaschio == true ? "Nato a " : "Nata a ") + p.Citta.Denominazione + " (" + p.Citta.Provincia.Trim() + ")"
                 +
                 (p.DataNascita != null ?
                     (((p.DataNascita.Value.Day == 1) || (p.DataNascita.Value.Day == 8) || (p.DataNascita.Value.Day == 11)) ? " l'" : " il ") +
                     p.DataNascita.Value : string.Empty)

The problem is well known and I found diffrent solutions but no one with projecting on a custom class like the one I use as a presentation model. It's about a week I'm working on this problem and I haven't figured out a solution yet. Any ideas?

Thank you!

EDIT Jul 19 16.27GMT+1

If I comment out this part

StringaNascita =
                    (p.IsMaschio == true ? "Nato a " : "Nata a ") + p.Citta.Denominazione + " (" + p.Citta.Provincia.Trim() + ")"
                 +
                 (p.DataNascita != null ?
                     (((p.DataNascita.Value.Day == 1) || (p.DataNascita.Value.Day == 8) || (p.DataNascita.Value.Day == 11)) ? " l'" : " il ") +
                     p.DataNascita.Value : string.Empty)

everything works fine.

Answer 1

I would suggest you get the raw data from the database, and then perform the string conversion later.

To force a conversion from IQueryable<T> to IEnumerable<T> you can use AsEnumerable() - so you'd have:

var dbQuery = from data in source
              where stuff
              select simple-projection;

var clrQuery = from data in dbQuery.AsEnumerable()
               where client-side-filters
               select client-side-projection;

if you see what I mean.

Note that AsEnumerable() really just changes the compile-time type of the expression, so that the other bits of the query are performed using Enumerable.* instead of Queryable.*.

Answer 2

Now I solved the problem what I'm asking is: was that question too stupid no one reply or am I a sort of genius? I think it's the first! Thanks to every one helped me to solve the problem.

Solution

public class PersonePM
{
    [Key]
    public Guid Id { get; set; }
    public string Cognome { get; set; }
    public string Nome { get; set; }
    public string Sesso { get; set; }
    public string StringaCognomeNome { get; set; }
    public DateTime DataNascita { get; set; }
    public string LuogoNascita { get; set; }

    /*Use the Getter to set the property based on other fields*/
    public string StringaNascita 
    {
        get
        {
            return LuogoNascita +
                (DataNascita != DateTime.MinValue ?
                     (((DataNascita.Day == 1) || (DataNascita.Day == 8) || (DataNascita.Day == 11)) ? " l'" : " il ") +
                     string.Format("{0:d MMMM yyyy}", DataNascita) : string.Empty);
        }
    }
    /* END of solution */

    public string CodiceFiscale { get; set; }
}

public IEnumerable<PersonePM> GetPersoneByCognome(string cognome)
    {
        return
            (from p in ObjectContext.Persone
             where p.Cognome.ToLower().Contains(cognome.Trim().ToLower())
             select new PersonePM
             {
                 Id = p.ID,
                 Cognome = p.Cognome,
                 Nome = p.Nome,
                 Sesso = p.IsMaschio == true ? "M" : "F",
                 StringaCognomeNome = p.Cognome + " " + p.Nome,
                 DataNascita = p.DataNascita.HasValue ? p.DataNascita.Value : DateTime.MinValue,
                 LuogoNascita = (p.IsMaschio == true ? "Nato a " : "Nata a ") + p.Citta.Denominazione + " (" + p.Citta.Provincia.Trim() + ")",
                 CodiceFiscale = p.CodiceFiscale,
             });
    }