In a LINQ to SQL query, convert a datetime to a formatted text.

entity-framework linq linq-to-sql

Question

I know that LINQ to SQL does not convert DateTime to string since there is no ToString() in SQL. But how can I convert the 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.

1
25
2/2/2016 3:41:23 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

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.

19
11/3/2011 7:09:18 PM

Popular Answer

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.

Alberto Montellano

Related Questions

How to convert an integer to a string using the ToString() function in LINQ

I resolved this issue by directly writing MySQl query inside C# as below - ...string queryTemplate = @"select inv.* from invoices as inv where userID = '123' and date like '%abc%'"; List<invoice> totalSearch = context.ExecuteStoreQuery<invoice>(queryTemplate).ToList();
c# entity entity-framework linq mysql
asked by CHash_Mike

Linq query String.Format

I think for what you're trying to do (format a specific float into a string) you want the overload of ....ToString()... which allows you to provide a format provider....Something like ...SmallBlind.ToString("{0:0.00}")...What you're probably looking for might best be represented:...Limit = string.Format("{0} / {1}", SmallBlind.ToString("...
.net c#-4.0 entity-framework linq wpf
asked by AllenG

DateTime to string conversion using LINQ

You can remedy your problem by projecting into an anonymous type, and then at a later step project into ...Post... after the data has already been returned from the DB....(from .... select new { /* stuff */, Date = c.Date }) .AsEnumerable() .Select(p => new Post { /* stuff */, PostingDate = p.Date.ToString() }) .ToList(); ...However, given that yo...
asp.net-mvc entity entity-framework linq
asked by Anthony Pegram

Using linq-to-entities to convert a string to a datetime has a significant problem.

Convert.ToDatetime... is supported by Linq2SQL. The only supported method of Linq to entities are these: ...http://msdn.microsoft.com/en-us/library/bb738681.aspx...about your problem ... try to convert ...startdate... and ...enddate... in string and compare the string value in linq expression.
.net c# entity-framework linq-to-entities winforms
asked by Max Zerbini

LinqToEntities can convert a string to a datetime (inside the query)

(Replicating my comment to paste some sample code)...If the date is in string format, couldn’t you apply a ...ToString... on your ...DateTime... (presumably ...x...) and then do a string comparison? ...Since you are working on string representations, you need to take care of several issues that would otherwise be handled transparently by ...DateT...
c# entity-framework linq
asked by Douglas

DateTime in entity Framework && DateTime in sql format returning different results

This has to do with the culture of your database. You can check your current culture by executing:...SELECT @@language ...To see the date formats for your culture, execute:...EXEC sp_helplanguage ...Based on that, you can ...Convert.ToDateTime()... in C# with your specific culture like so:...UPDATED ANSWER...DateTime startDate = DateTime.MinValue;...
asp.net-mvc-4 c# entity-framework sql sql-server
asked by McCee

LINQ Query to Convert string to datetime

You need to first materialize the query (i.e. load the data) to your application and then parse it. Entity Framework doesn't know how to execute .Net methods (like ...DateTime.Parse...), it only knows how to translate them to ...SQL...var DateQuery = db.Order_Reports.ToList().Select(o => new demoDate { DueDate=DateTime.Parse(o.ReportDueDateTim...
.net c# datetime entity-framework linq
asked by i3arnon

In LINQ Query convert the string to datetime and check with today's date

I don't know a way to implement it as you describe it, because ...There's no function in SQL that will filter the dates in string format by inequality (at least those that work with your date format and are convertable from LINQ to SQL query)....You have a flaw in your database design which prevents "normal" methods from working...However, there ar...
c# datetime entity-framework linq
asked by DarkWanderer

How can I convert DateTime to String in Linq Query?

The way I generally solve this problem is by first just getting the data and then selecting it in memory'....var performancereviews = from pr in db.PerformanceReviews .Include(a => a.ReviewedByEmployee) .ToArray() .Select( ....); ...By ...
c# entity-framework linq linq-to-entities
asked by Haedrian

C# convert sql query into EF dbcontext linq to entities query

Your SQL equivalent LINQ query is,...string statusValue = "Done"; string codeState = "Completed"; DateTime paDate = DateTime.ParseExact("2018-12-10", "yyyy-MM-dd", new CultureInfo("en-US", true)); int year = 2017; decimal amount = 500; var result = (from PR in context.Products join OD in context.Orders on PR.Id equals OD.Id ...
c# entity-framework-6 linq linq-to-entities sql
asked by er-sho

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow