Converting DateTime to String Entity Framework

Question

I have the following code, trying to return d.dateofbirth and d.dateofdeath as strings without much luck.

I have tried using tostring() or convert but both are converted to sql during the runtime so therefore don't work,

I need to be able to show DOB and DOD on 2 lines in the same gridview cell, like this:

DOB DOD

Heres my code so far:

fmsEntities context = new fmsEntities();
var query = from f in context.funerals
            where f.IsPencil == 0
            join d in context.deceaseddetails on f.DeceasedID equals d.ID
            join i in context.funeralservices on f.ID equals i.FuneralID
            where i.IsAlternative == 0
            join h in context.htvalues on f.HtValuesID equals h.ID
            join p in context.placeofdeaths on f.PlaceOfDeathID equals p.ID
            join c in context.coroners on f.CoronerID equals c.ID
            select new DataBindingProjection { DeceasedName = (d.LastName + Environment.NewLine + d.FirstName),
            DOBDOD = Convert.ToString(d.DateOfBirth)};

var dataobjects = query.ToList();

dataGridView1.DataSource = dataobjects;

private class DataBindingProjection
{
    public string DeceasedName {get; set;}
    public string DOBDOD {get; set;}
}

Update, Moved ToList before building the dataProjection as recommended in comments,

fmsEntities context = new fmsEntities();
        var query = (from f in context.funerals
                    where f.IsPencil == 0
                    join d in context.deceaseddetails on f.DeceasedID equals d.ID
                    join i in context.funeralservices on f.ID equals i.FuneralID
                    where i.IsAlternative == 0
                    join h in context.htvalues on f.HtValuesID equals h.ID
                    join p in context.placeofdeaths on f.PlaceOfDeathID equals p.ID
                    join c in context.coroners on f.CoronerID equals c.ID
                    select new { f , d , i , h , p , c }).ToList();

        var dataobjects = query.Select(d => new DataBindingProjection {DeceasedName = (d.LastName + Environment.NewLine + d.FirstName),
                                        DOBDOD = (d.DateOfBirth.ToString() + Environment.NewLine + d.DateOfDeath.ToString())});

        dataGridView1.DataSource = dataobjects;
    }

    private class DataBindingProjection
    {
        public string DeceasedName {get; set;}
        public string DOBDOD {get; set;}
    }

DOBDOD = d.DateOfBirth.ToString("yyyy-MM-dd") + Environment.NewLine + d.dateOfDeath.ToString("yyyy-MM-dd")? — Corak
– Corak, Commented Sep 12, 2013 at 10:54
James, please try to move that 'select' to be executed after the 'ToList()'. — Piotr Justyna
– Piotr Justyna, Commented Sep 12, 2013 at 10:55
The error is + base {"LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression."} System.SystemException {System.NotSupportedException} How do I move the ToList before the select? Tried to move it before and the select had an error! — James Smithy Cleave
– James Smithy Cleave, Commented Sep 12, 2013 at 11:00
@JamesSmithyCleave Something like this: (I didn't compile it though) pastebin.com/UtDRQ515 — Piotr Justyna
– Piotr Justyna, Commented Sep 12, 2013 at 11:03

Piotr Justyna · Accepted Answer · 2013-09-12 11:28:49Z

1

James, please consider doing the projection after the "ToList()" is called for the first time, something like this:

var dataobjects = query
.ToList()
.Select(d => new DataBindingProjection { DeceasedName = (d.LastName + Environment.NewLine + d.FirstName),
            DOBDOD = Convert.ToString(d.DateOfBirth)})
.ToList();

answered Sep 12, 2013 at 11:28

Piotr Justyna

5,0243 gold badges28 silver badges40 bronze badges

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

6 Comments

James Smithy Cleave Over a year ago

Thanks for your help! 1 Final thing, How can I convert my datetime value currently incluiding timestamp to something like (DD-MM-YYYY)? Thanks!

Piotr Justyna Over a year ago

Please try something like this: String.Format("Your formatted date is: {0:MM/dd/yyyy}", yourDate);

James Smithy Cleave Over a year ago

Problem is if it is null I get error: System.Nullable' does not contain a definition for 'ToShortDateString' and no extension method 'ToShortDateString' accepting a first argument of type 'System.Nullable' could be found

James Smithy Cleave Over a year ago

Sorted! Checked to see if it was null by checking hasvalue, then took the value to shortdatestring and finally if no value emptystring! Handling nulls are painful! DOBDOD = (d.val2.HasValue ? d.val2.Value.ToShortDateString() : string.Empty) Thanks again!

Serj Sagan Over a year ago

Downvoted because this is doing to list is not an option on large datasets and you need the search to be done database side otherwise you wont't get the results that you want... the correct answer is actually stackoverflow.com/a/16364548/550975

|

Collectives™ on Stack Overflow

Converting DateTime to String Entity Framework

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related