How to set DateTime format in lambda expression?

Question

I am using linq-to-sql joins and group by features to fetch the data I need. I got everything worked but the dateTime format. Here is my code

from emp in dc.Employees
                    join lv in
                        (from l in dc.Leaves
                         group l by l.RequestID into le
                         select new {
                                    NumberOfDays = (from lea in le select lea.Date).Count(),
                                    Date = (DateTime)(from lea in le select lea.Date).Min(),
                                    StartDate = (DateTime)(from lea in le select lea.Date).Min(),
                                    EndDate = (from lea in le select lea.Date).Min(),  
                                    EmployeeID =  (from lea in le select lea.EmployeeID).Max(),                                        
                                    LengthHours = (float)(from lea in le select lea.LengthHours).Sum(),
                                    LenghtDays = (float)(from lea in le select lea.LengthDays).Sum(),
                                    TypeID = (from lea in le select lea.TypeID).Max(),
                                    Comments = (from lea in le select lea.Comments).Max()                                        
                                    }
                         )
                     on emp.ID equals lv.EmployeeID
                     join lt in dc.ReferenceLeaveTypes 
                     on lv.TypeID equals lt.ID.ToString()                         
                     select new LeaveList
                        {
                            Date = (lv.NumberOfDays > 1 ? (lv.StartDate.ToString() + " --> " + lv.EndDate.ToString()) : lv.StartDate.ToString()),
                            EmployeeName = emp.FirstName,                            
                            LeavePeriod = lv.LengthHours,
                            LenghtDays = lv.LenghtDays,
                            LeaveType = lt.Name,
                            Comments = lv.Comments
                        };

        return llist;

Here are the most important parts

this is the part fetching Date. It works fine

 StartDate = (DateTime)(from lea in le select lea.Date).Min(),

here is converting it into string and works fine

Date = (lv.NumberOfDays > 1 ? (lv.StartDate.ToString() + " --> " + lv.EndDate.ToString()) : lv.StartDate.ToString())

However when I try to change the string format into something like

(lv.StartDate.ToString("dd/MM/yyyy") + " --> " + lv.EndDate.ToString("dd/MM/yyyy"))

it gives me an exception. I tried different formats and I also tried ToShortDateString() method. But the most interesting part is that these methods would work on the "else" part of my lambda expression like this.

Date = (lv.NumberOfDays > 1 ? (lv.StartDate.ToString() + " --> " + lv.EndDate.ToString()) : lv.StartDate.ToString("dd/MM/yyyy"))

this piece of code(shown up) would work without any problems. But I want to use it in the other part too. like this

lv.StartDate.ToString("dd/MM/yyyy") + " --> " + lv.EndDate.ToString("dd/MM/yyyy")

Any suggestion? tnx in advance

Answer 1

I think I found where I lost it.

(lv.StartDate.ToString() + " --> " + lv.EndDate.ToString())

is there any better way to concatenate these strings

Answer 2

Thanks for checking. I got it working. instead of

lv.StartDate.ToString("dd/MM/yyyy") + " --> " + lv.EndDate.ToString("dd/MM/yyyy")

I wrote separate function like this.

 Date = (lv.NumberOfDays > 1 ? buildStrDate(lv.StartDate, lv.EndDate) : lv.StartDate.ToShortDateString()),

private String buildStrDate(DateTime a, DateTime b)
{
    return a.ToShortDateString() + " --> " + b.ToShortDateString();
}

exception message was InvalidOperationException

Could not translate expression '<>h__TransparentIdentifier0.lv.EndDate.ToShortDateString()' into SQL and could not treat it as a local expression.

Any idea what happened?