Convert datetime to a formatted string inside a LINQ-to-entities query

Question

How can I convert DateTime into a formatted string?

This is the line in the following query that needs help:

StartDate = string.Format("{0:dd.MM.yy}", p.StartDate)

The whole query:

var offer = (from p in dc.CustomerOffer
             join q in dc.OffersInBranch
             on p.ID equals q.OfferID
             where q.BranchID == singleLoc.LocationID
             let value = (p.OriginalPrice - p.NewPrice) * 100 / p.OriginalPrice
             orderby value descending
             select new Offer()
             {
                 Title = p.OfferTitle,
                 Description = p.Description,
                 BestOffer = value,
                 ID = p.ID,
                 LocationID = q.BranchID,
                 LocationName = q.CustomerBranch.BranchName,
                 OriginalPrice = SqlFunctions.StringConvert((decimal)p.OriginalPrice),
                 NewPrice = SqlFunctions.StringConvert((decimal)p.NewPrice),
                 StartDate = string.Format("{0:dd.MM.yy}", p.StartDate)
             }).First();

I get the following error message:

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

score 38 · Answer 1 · edited Jun 30 '20 at 08:37

Another option is using SqlFunctions.DateName, your code will be like this:

var offer = (from p in dc.CustomerOffer
                 join q in dc.OffersInBranch
                     on p.ID equals q.OfferID
                 where q.BranchID == singleLoc.LocationID
                 let value = (p.OriginalPrice - p.NewPrice) * 100 / p.OriginalPrice
                 orderby value descending
                 select new
                 {
                     Title = p.OfferTitle,
                     Description = p.Description,
                     BestOffer = value,
                     ID = p.ID,
                     LocationID = q.BranchID,
                     LocationName = q.CustomerBranch.BranchName,
                     OriginalPrice = SqlFunctions.StringConvert((decimal)p.OriginalPrice),
                     NewPrice = SqlFunctions.StringConvert((decimal)p.NewPrice),
                     StartDate = SqlFunctions.DateName("day", p.StartDate) + "/" + SqlFunctions.DateName("month", p.StartDate) + "/" +  SqlFunctions.DateName("year", p.StartDate)
                 })

I found it useful if you don't want to add an extra select new block.

I had to add this using: `using System.Data.Objects.SqlClient;` in order to use `SqlFunctions` — Kristopher, Jul 07 '17 at 15:44
Add reference to System.Data.Entity.dll if you dint before to use sqlfunctions — Ram, Jul 21 '17 at 01:50

mccow002 · Accepted Answer · 2011-11-03T19:09:18.887

EDIT: Now that I understand the question, I'm giving it another shot :)

var offer = (from p in dc.CustomerOffer
                     join q in dc.OffersInBranch
                         on p.ID equals q.OfferID
                     where q.BranchID == singleLoc.LocationID
                     let value = (p.OriginalPrice - p.NewPrice) * 100 / p.OriginalPrice
                     orderby value descending
                     select new
                     {
                         Title = p.OfferTitle,
                         Description = p.Description,
                         BestOffer=value,
                         ID=p.ID,
                         LocationID=q.BranchID,
                         LocationName=q.CustomerBranch.BranchName,
                         OriginalPrice=SqlFunctions.StringConvert((decimal)p.OriginalPrice),
                         NewPrice=SqlFunctions.StringConvert((decimal)p.NewPrice),
                         StartDate=p.StartDate

                     })
                     .ToList()
                     .Select(x => new Offer()
                     {
                         Title = x.OfferTitle,
                         Description = x.Description,
                         BestOffer=value,
                         ID=x.ID,
                         LocationID=x.BranchID,
                         LocationName=x.CustomerBranch.BranchName,
                         OriginalPrice=x.OriginalPrice,
                         NewPrice=x.NewPrice,
                         StartDate=x.StartDate.ToString("dd.MM.yy")
                     }).First();

I know it's a bit long, but that's the problem with Linq To SQL.

When you use linq, the database call isn't executed until you use something such as ToList() or First() that results in actual objects. Once that SQL call is executed by the first .First() call, you're now working with .NET types, and can use DateTime stuff.

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. — Reza.Hoque, Nov 03 '11 at 18:10
yes. LINQ can not convert datetime to string. there are lot of forum post about this. see this one http://stackoverflow.com/questions/6887776/linq-convert-datetime-to-string . But i am not sure how to get it work. — Reza.Hoque, Nov 03 '11 at 18:17
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/4700/discussion-between-kandroid-and-mccow002) — Reza.Hoque, Nov 03 '11 at 18:46
You should replace the first `First()`, ugh, by e.g. `Take(1)` to get an `IEnumerable` for the subsequent `Select`. Or leave it out. — Gert Arnold, Nov 03 '11 at 18:52

score 4 · Answer 3 · answered Dec 27 '18 at 19:05

I ended up using the sql function FORMAT; here's a simplified version of this implementation:

https://weblogs.asp.net/ricardoperes/registering-sql-server-built-in-functions-to-entity-framework-code-first

First you need to define the function in EF:

public class FormatFunctionConvention : IStoreModelConvention<EdmModel>
{
    public void Apply(EdmModel item, DbModel model)
    {
        var payload = new EdmFunctionPayload
        {
            StoreFunctionName = "FORMAT",
            Parameters = new[] {
                FunctionParameter.Create("value", PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.DateTime), ParameterMode.In),
                FunctionParameter.Create("format", PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.String), ParameterMode.In)
            },
            ReturnParameters = new[] {
                FunctionParameter.Create("result", PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.String), ParameterMode.ReturnValue)
            },
            Schema = "dbo",
            IsBuiltIn = true
        };

        item.AddItem(EdmFunction.Create("FORMAT", "CodeFirstDatabaseSchema", item.DataSpace, payload, null));
    }
}

Then define it as C# methods:

public static class SqlFunctions
{
    [DbFunction("CodeFirstDatabaseSchema", "FORMAT")]
    public static String Format(this DateTime value, string format)
    {
        return value.ToString(format);
    }

    [DbFunction("CodeFirstDatabaseSchema", "FORMAT")]
    public static String Format(this DateTime? value, string format)
    {
        return value?.ToString(format);
    }
}

Register it in your DbContext:

public class SqlDb : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Conventions.Add(new FormatFunctionConvention());
    }
}

And finally, you can call it like so:

var x = db.MyItems.Select(i => new { FormattedDate = SqlFunctions.Format(i.MyDate, "MM/dd/yyyy") }).ToArray();

score 0 · Answer 4 · answered May 11 '17 at 22:15

That is what we did, we added a new function to the class and we query the date as normal in the query:

[ComplexType]
public class Offer
{    
    public DateTime StartDate 
    {
        get;
        set;
    }

   public String Title
   {
       get;
       set;
   }

   /*Other fields*/      
   .
   .
   .


    public string FormattedDate(string format)
    {
        return Date.ToString(format);
    }
}



var offer = (from p in dc.CustomerOffer
         join q in dc.OffersInBranch
         on p.ID equals q.OfferID
         where q.BranchID == singleLoc.LocationID
         let value = (p.OriginalPrice - p.NewPrice) * 100 / p.OriginalPrice
         orderby value descending
         select new Offer()
         {
             Title = p.OfferTitle,
             Description = p.Description,
             BestOffer = value,
             ID = p.ID,
             LocationID = q.BranchID,
             LocationName = q.CustomerBranch.BranchName,
             OriginalPrice = SqlFunctions.StringConvert((decimal)p.OriginalPrice),
             NewPrice = SqlFunctions.StringConvert((decimal)p.NewPrice),
             StartDate = p.StartDate
         }).First();

Then you can just call the FormattedDate field passing the desired format.

edit1.Text = offer.FormattedDate("dd.MM.yy");

Or can can define it as a field with just the getter:

    public string FormattedDate
                {
                   get { return Date.ToString("dd.MM.yy") };
                }

 edit1.Text = offer.FormattedDate;

In case you class is an Entity, you need to declare a new partial of that class and add the field.

Hope this help someone.

Dani · Answer 5 · 2020-05-12T17:58:23.970

0

In vb (valid to c# too changing syntax):

Imports System.Data.Entity
... 
query.Select(Function(x) New MyObject With {
    ...
    .DateString = DbFunctions.Right("00" & x.DateField.Day, 2) & "/" & DbFunctions.Right("00" & x.DateField.Month, 2) & "/" & x.DateField.Year
    ...
}).ToList()

Note: ToList(), ToEnumerable() are not the way because its executes a query, the user wants linq to sql..

edited May 12 '20 at 17:58

answered May 12 '20 at 13:08

Dani

1,825
2
15
29

score 0 · Answer 6 · answered Jan 23 '23 at 18:19

The easiest and most efficient way I have found to do string formats on numeric or datetime objects is by using string interpolation. It will bring back the actual DateTime/int/float/double/etc.. objects in the SQL query, and then client side it will do the string format during projection. I modified your query below, note how OriginalPrice, NewPrice, and StartDate are converted:

var offer = (from p in dc.CustomerOffer
         join q in dc.OffersInBranch
         on p.ID equals q.OfferID
         where q.BranchID == singleLoc.LocationID
         let value = (p.OriginalPrice - p.NewPrice) * 100 / p.OriginalPrice
         orderby value descending
         select new Offer()
         {
             Title = p.OfferTitle,
             Description = p.Description,
             BestOffer = value,
             ID = p.ID,
             LocationID = q.BranchID,
             LocationName = q.CustomerBranch.BranchName,
             OriginalPrice = $"{p.OriginalPrice:C2}",
             NewPrice = $"{p.NewPrice:C2}",
             StartDate = $"{p.StartDate:dd.MM.yy}"
         }).First();

score -1 · Answer 7 · answered Jan 02 '13 at 23:44

if it's a datetime you need to use the .ToShortDateString(). But you also need to declare it AsEnumerable().

var offer = (from p in dc.CustomerOffer.AsEnumerable()
                 join q in dc.OffersInBranch
                     on p.ID equals q.OfferID
                 where q.BranchID == singleLoc.LocationID
                 let value = (p.OriginalPrice - p.NewPrice) * 100 / p.OriginalPrice
                 orderby value descending
                 select new
                 {
                     Title = p.OfferTitle,
                     Description = p.Description,
                     BestOffer=value,
                     ID=p.ID,
                     LocationID=q.BranchID,
                     LocationName=q.CustomerBranch.BranchName,
                     OriginalPrice=SqlFunctions.StringConvert((decimal)p.OriginalPrice),
                     NewPrice=SqlFunctions.StringConvert((decimal)p.NewPrice),
                     StartDate=p.StartDate

                 })
                 .ToList()
                 .Select(x => new Offer()
                 {
                     Title = x.OfferTitle,
                     Description = x.Description,
                     BestOffer=value,
                     ID=x.ID,
                     LocationID=x.BranchID,
                     LocationName=x.CustomerBranch.BranchName,
                     OriginalPrice=x.OriginalPrice,
                     NewPrice=x.NewPrice,
                     StartDate=x.StartDate.ToShortDateString()
                 }).First();

No. If we use only iqueryable, ToShortDateString will not be work. — , Aug 03 '21 at 06:51

Convert datetime to a formatted string inside a LINQ-to-entities query

7 Answers7

Linked