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, Sep 12 '13 at 10:54
James, please try to move that 'select' to be executed after the 'ToList()'. — Piotr Justyna, Sep 12 '13 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, Sep 12 '13 at 11:00
@JamesSmithyCleave Something like this: (I didn't compile it though) http://pastebin.com/UtDRQ515 — Piotr Justyna, Sep 12 '13 at 11:03
Okay, so `select` the fields separately, and then in a second "query", build your `DataBindingProjection` objects. - Basically what @PiotrJustyna does. ^_^ — Corak, Sep 12 '13 at 11:04
It helped, Just getting Error 1 'AnonymousType#1' does not contain a definition for 'LastName' and no extension method 'LastName' accepting a first argument of type 'AnonymousType#1' could be found (are you missing a using directive or an assembly reference?) — James Smithy Cleave, Sep 12 '13 at 11:10
Guessing I now need to specify that it is a datetime and that lastname is a string? EDIT: updated question — James Smithy Cleave, Sep 12 '13 at 11:11
Try to do just ...select d).ToList(); - you don't use other objects. — Piotr Justyna, Sep 12 '13 at 11:18
I will end up using all the objects! Will select the ones I need specifically! — James Smithy Cleave, Sep 12 '13 at 11:19
@Piotr Justyna can you move your comment to an answer, worked perfectly now I am specifying what to take out! How can I now format the datetime to DD-MM-YYYY, currently have d.val2.ToString() where val2 is the datetime for dateofbirth, tried using d.val2.ToString("yyyy-MM-dd") but no luck! — James Smithy Cleave, Sep 12 '13 at 11:24

score 1 · Accepted Answer · answered Sep 12 '13 at 11:28

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 '13 at 11:28

Piotr Justyna

4,888
3
25
40

Thanks for your help! 1 Final thing, How can I convert my datetime value currently incluiding timestamp to something like (DD-MM-YYYY)? Thanks! – James Smithy Cleave Sep 12 '13 at 11:30
Please try something like this: String.Format("Your formatted date is: {0:MM/dd/yyyy}", yourDate); – Piotr Justyna Sep 12 '13 at 11:40
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 Sep 12 '13 at 11:46
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! – James Smithy Cleave Sep 12 '13 at 11:50
1

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 http://stackoverflow.com/a/16364548/550975 – Serj Sagan Mar 04 '16 at 07:43
Yeah, fair enough - it's a quick and dirty solution only suitable for small hack projects. Thanks for linking a more appropriate answer. If you post that better answer, I'll upvote. – Piotr Justyna Mar 04 '16 at 17:36

Converting DateTime to String Entity Framework

1 Answers1