DateTime to String in LINQ during request for retrieving data c#

Question

I have search string input, then I need to check if DateTime field contains that string. Here's the code

        System.Linq.Expressions.Expression<Func<FileConversion, bool>> predicate =
GetPredicateForFileConversion(sSearch);
        //fetch and sort results
        List<FileConversion> conversions = new List<FileConversion>();
        if (asc)
        {
            var pages2skip = (iDisplayStart);

            var sqlRequest = new System.Data.SqlClient.SqlCommand();
            sqlRequest.CommandText = "SELECT * FROM FileConversions JOIN "

            conversions = (from fc in _dataContext.FileConversions
                           join cs in _dataContext.ConversionStatuses on fc.Id equals cs.FileConversionId
                           join rts in _dataContext.ReturnSettings on fc.ReturnSettingId equals rts.Id
                           join r in _dataContext.Returns on rts.ReturnId equals r.Id
                           join e in _dataContext.Entities on r.EntityId equals e.Id
                           join a in _dataContext.Accounts on e.AccountId equals a.Id
                           where ((fc.CurrentStatusCode == ConversionStatusCode.Processing) || (fc.CurrentStatusCode == ConversionStatusCode.Ready) || (fc.CurrentStatusCode == ConversionStatusCode.Error) ||
                            (fc.CurrentStatusCode == ConversionStatusCode.ValidationErrors) || (fc.CurrentStatusCode == ConversionStatusCode.Pending))
                           select fc).Where(predicate).OrderBy(sort).Distinct().Skip(pages2skip).Take(iDisplayLength).ToList();
        }

Here's a predicate:

private System.Linq.Expressions.Expression<Func<FileConversion, bool>> GetPredicateForFileConversion(String sSearch)
    {
        System.Linq.Expressions.Expression<Func<FileConversion, bool>> predicate = null;
        if (sSearch != null && !Convert.ToString(sSearch).Equals(""))            
        {
           predicate = row => row.FileName.Contains(sSearch)
                || row.ReturnSetting.Return.Entity.Account.Name.Contains(sSearch) || Convert.ToString(row.CreationDate).Contains(sSearch)
                || row.ConversionStatuses.Any(s => s.UserName.Contains(sSearch));
        }
        else
        {
            predicate = row => true;
        }
        return predicate;
    }

Here's a question - how to convert DateTime to string in linq to make search in one request? I tried ToString(), DateTime.TryParse() and something else, but it won't work

Answer 1

This is happening because LINQ to Entities is trying to convert the expression tree into a SQL query, and while .ToString() can be translated into SQL, .ToString(string) can not. (SQL doesn't have the same concepts of string formatting.)

To resolve this, don't perform the formatting in the query, perform it in the display logic. Keep the query as simple as possible:

In this case it is still a DateTime value. It's not formatting the data, just carrying it. (Like a DTO should.)

Then when you display the value, perform the formatting. For example, is this being used in an MVC view?:

@yourDateTime.FormattedReviewDate.ToString("MMM dd,yyyy")

You might even just add a simple property to dateTime for the formatted display:

public string FormattedReviewDate
{
    get { return ReviewDate.ToString("MMM dd,yyyy"); }
}

Then whatever is binding to properties on the DTO can just bind to that (assuming it's a one-way binding in this case).

Answer 2

Thanks for all who answers in this post, I found solution I needed. Simply when I retrieve data before "format-needs" I convert all of it into a List, and from that moment I can manipulate data (converts between DateTime and String, etc.) with C# instruments to to except useless data (that not match to my condition).

public List<FileConversion> GetConversionsForDataTables(String sSearch, int iDisplayStart, int iDisplayLength, Func<FileConversion, string> sort, bool asc)
    {

        System.Linq.Expressions.Expression<Func<FileConversion, bool>> predicate = GetPredicateForFileConversion(sSearch);
        //fetch and sort results
        List<FileConversion> conversions = new List<FileConversion>();
        if (asc)
        {
            var pages2skip = (iDisplayStart);

            var sqlRequest = new System.Data.SqlClient.SqlCommand();

            conversions = (from fc in _dataContext.FileConversions
                           join cs in _dataContext.ConversionStatuses on fc.Id equals cs.FileConversionId
                           join rts in _dataContext.ReturnSettings on fc.ReturnSettingId equals rts.Id
                           join r in _dataContext.Returns on rts.ReturnId equals r.Id
                           join e in _dataContext.Entities on r.EntityId equals e.Id
                           join a in _dataContext.Accounts on e.AccountId equals a.Id
                           where ((fc.CurrentStatusCode == ConversionStatusCode.Processing) || (fc.CurrentStatusCode == ConversionStatusCode.Ready) || (fc.CurrentStatusCode == ConversionStatusCode.Error) ||
                            (fc.CurrentStatusCode == ConversionStatusCode.ValidationErrors) || (fc.CurrentStatusCode == ConversionStatusCode.Pending))
                           select fc).ToList().Where(p => GetDataByCondition(p, sSearch)).OrderBy(sort).Distinct().Skip(pages2skip).Take(iDisplayLength).ToList();
        }
        else
        {
            conversions = (from fc in _dataContext.FileConversions
                           join cs in _dataContext.ConversionStatuses on fc.Id equals cs.FileConversionId
                           join rts in _dataContext.ReturnSettings on fc.ReturnSettingId equals rts.Id
                           join r in _dataContext.Returns on rts.ReturnId equals r.Id
                           join e in _dataContext.Entities on r.EntityId equals e.Id
                           join a in _dataContext.Accounts on e.AccountId equals a.Id
                           where ((fc.CurrentStatusCode == ConversionStatusCode.Processing) || (fc.CurrentStatusCode == ConversionStatusCode.Ready) || (fc.CurrentStatusCode == ConversionStatusCode.Error) ||
                            (fc.CurrentStatusCode == ConversionStatusCode.ValidationErrors) || (fc.CurrentStatusCode == ConversionStatusCode.Pending))
                           select fc).ToList().Where(p => GetDataByCondition(p, sSearch)).OrderByDescending(sort).Distinct().Skip(iDisplayStart).Take(iDisplayLength).ToList();

        }
        return conversions;
    }

    private bool GetDataByCondition(FileConversion fileConversion, string sSearch)
    {
        if (sSearch == null)
        {
            sSearch = String.Empty;
        }
        if (fileConversion.FileName.Contains(sSearch)
            || fileConversion.ReturnSetting.Return.Entity.Account.Name.Contains(sSearch)
            || fileConversion.ConversionStatuses.Any(s => s.UserName.Contains(sSearch) || s.Date.ToShortDateString().Contains(sSearch))
            || fileConversion.CurrentStatusCode.ToString().Contains(sSearch))
        {
            return true;
        }
        return false;
    }