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

Have you tried string.Format("{0:MM dd yyyy} etc.", date)? — M. Nasir Javaid
– M. Nasir Javaid, Commented Aug 24, 2015 at 9:06
Yes, but maybe I made some mistake? I try to insert this format into predicate, and it's throws exception "LINQ to Entities does not recognize the method 'System.String ToString(System.DateTime)' method, and this method cannot be translated into a store expression." Any ideas? — flow2012est
– flow2012est, Commented Aug 24, 2015 at 9:17
possible duplicate of How can I convert DateTime to String in Linq Query? — Dah Sra
– Dah Sra, Commented Aug 24, 2015 at 9:23
No, this one is "convert for displaying", my task is to "convert for search in database per query" — flow2012est
– flow2012est, Commented Aug 24, 2015 at 9:30

Dah Sra · Accepted Answer · 2015-08-24 09:17:31Z

4

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).

answered Aug 24, 2015 at 9:17

Dah Sra

4,4753 gold badges35 silver badges76 bronze badges

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

1 Comment

flow2012est Over a year ago

I understand it, that LINQ should translate commands only friendly to SQL. That solution as I understood is for displaying data with formatted string, but I need during request to take all data where DateTime contains required string... I know that in SQL I can write request and SQL can provide me with CONVERTS functionality, and that convert will work during request, Can Linq2Entities made same thing? Thank you for answer :)

flow2012est · Accepted Answer · 2015-08-24 14:01:57Z

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;
    }

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related