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

entity-framework linq linq-to-sql

Question

I am aware that LINQ to SQL cannot be converted.DateTime to string since there is noToString() in SQL. However, how can I translate theDateTime a formatted string into?

The part of the following inquiry that requires assistance is this:

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

The whole inquiry:

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 have the following 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.

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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

EDIT: I'm going to try again now that I understand the question.

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 am aware that it is a little lengthy, however Linq To SQL has this issue.

When using Linq, the database call is not made until you use a function that returns real objects, such ToList() or First(). Once the initial is finished with the SQL request. After using First(), you may utilize DateTime objects and.NET types.

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

Popular Answer

Using SqlFunctions is an additional option. Your code will look like this: DateName

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)

                 })

If you don't want to add a second pick new block, I found it to be handy.

Alberto Montellano

Related Questions

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

I fixed this problem by explicitly putting the following MySQl query inside of C#:...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 believe you need the overload of to do what you're attempting to achieve (convert a particular float into a string).....ToString()... It enables you to provide a format supplier....something similar...SmallBlind.ToString("{0:0.00}")...You may find what you're seeking for best represented by:...Limit = string.Format("{0} / {1}", SmallBl...
.net c#-4.0 entity-framework linq wpf
asked by AllenG

DateTime to string conversion using LINQ

By projecting into an anonymous type and then afterwards projecting into a specific type, you may solve your issue....Post... once the DB has already sent the data back....(from .... select new { /* stuff */, Date = c.Date }) .AsEnumerable() .Select(p => new Post { /* stuff */, PostingDate = p.Date.ToString() }) .ToList(); ...However, given that y...
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... Linq2SQL supports this. These are the only supported Linq to Entities methods: ...http://msdn.microsoft.com/en-us/library/bb738681.aspx...about your issue, try to convert...startdate... and ...enddate... in string and use a linq expression to compare the value of the string.
.net c# entity-framework linq-to-entities winforms
asked by Max Zerbini

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

(I'm copying and pasting my remark here to include some example code.)...Couldn't you use a string conversion if the date is in that format?...ToString... to you...DateTime... (presumably ...x... ) after which do a string comparison?...Since you are dealing with string representations, you must address a number of problems that would otherwise be ...
c# entity-framework linq
asked by Douglas

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

This is related to the database's culture. By performing: you can determine your current culture....SELECT @@language ...Execute: to view the date formats specific to your culture....EXEC sp_helplanguage ...Considering that, you...Convert.ToDateTime()... in C# using your particular cultural style, as follows:...REVISED ANSWER...DateTime startDate =...
asp.net-mvc-4 c# entity-framework sql sql-server
asked by McCee

LINQ Query to Convert string to datetime

Prior to parsing the query, you must materialize it, or load the data into your application. Entity Framework lacks execution skills. Net techniques, like...DateTime.Parse... ), it only understands how to convert them to...SQL...var DateQuery = db.Order_Reports.ToList().Select(o => new demoDate { DueDate=DateTime.Parse(o.ReportDueDateTime), ...
.net c# datetime entity-framework linq
asked by i3arnon

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

I'm not sure how to put it into practice the manner you suggest, because...The dates in string format can't be filtered by inequality using a SQL function (at least those that work with your date format and are convertable from LINQ to SQL query)....Your database design has a fault that prohibits "normal" procedures from functioning....There are, h...
c# datetime entity-framework linq
asked by DarkWanderer

How can I convert DateTime to String in Linq Query?

I typically approach this challenge by first simply gathering the facts and then choosing it from memory....var performancereviews = from pr in db.PerformanceReviews .Include(a => a.ReviewedByEmployee) .ToArray() .Select( ....); ...By u...
c# entity-framework linq linq-to-entities
asked by Haedrian

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

ZZZ_tmp
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