CODEWITHSUNDEEP
c#entity-frameworklinq-to-entities
CodeWarrior
CodeWarrior

Reputation: 783

Get formatted date in string format in LINQ to Entities

I am using the following LINQ to Entities code for getting data from a database table named Stocks:

IEnumerable<DrugInfoViewModel> Info = from a in db.Stocks.Where(
   r => r.SiteID == SiteID 
        && r.MachineID == MachineID 
        && EntityFunctions.TruncateTime(r.ExpiryDate) <= ExpiryDate)
   select new DrugInfoViewModel()
   {
        ItemName = a.DrugBrand.Name,                           
        ItemBatchNo = a.BatchNo,
        ItemExpiryDate = (a.ExpiryDate == null ? null :
          Convert.ToDateTime(a.ExpiryDate).ToString("MM/dd/yyyy")),
                       Quantity = (int?)a.Qty
   };

Here, ItemExpiryDate is a string field and a.ExpiryDate is a nullable datetime field in the table. When I run this code I am getting this error:

LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression.

This same line of code works properly in another page. Why is this happening?

Upvotes: 1

Views: 4337

Answers (2)

Alexander Pastukhov
Alexander Pastukhov

Reputation: 58

Just add ToList() or ToArray() method after Where(). This will fetch filtered objects to your memory and you will be able to call ToString(). Please, make sure that you call ToList() after filtering to avoid fetching all of the records from the table.

IEnumerable<DrugInfoViewModel> Info = from a in db.Stocks.Where(
   r => r.SiteID == SiteID 
        && r.MachineID == MachineID 
        && EntityFunctions.TruncateTime(r.ExpiryDate) <= ExpiryDate)
   .ToList()
   select new DrugInfoViewModel()
   {
        ItemName = a.DrugBrand.Name,                           
        ItemBatchNo = a.BatchNo,
        ItemExpiryDate = (a.ExpiryDate == null ? null :
          Convert.ToDateTime(a.ExpiryDate).ToString("MM/dd/yyyy")),
                       Quantity = (int?)a.Qty
   };

Upvotes: 1

DrKoch
DrKoch

Reputation: 9762

This happens because the LINQ expression is evaluated on the server side i.e. inside SQL Server and there the function ToString() is not available.

As suggested in the comments already: Get a DateTime, format on the client side.

Upvotes: 0

Related Questions