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

.net c# entity-framework linq-to-entities winforms

Question

How can I use linq to entities to convert a string to a datetime?

I've received the following inquiry, where thevisit_date datatype for the column is string

var memberl = from v in abc.visits
              join m in abc.members on v.member_Id equals m.member_Id
              where Convert.ToDateTime(v.visit_Date) >= startdate && 
                    Convert.ToDateTime(v.visit_Date) <= enddate
              group m by new { m.member_Firstname, 
                               m.member_Lastname, m.member_Id } into g
              orderby g.Count()
              select new
              {
                  numVisits = g.Count(),
                  firstname = g.Key.member_Firstname,
                  lastname = g.Key.member_Lastname
              };

I'm afraid I can't alter the schema.

I encountered the problem:

linq to entites does not recognise Convert.ToDatetime method

Exists a method for converting a string to a Datetime?

Revised code:

I modified my inquiry in response to your request.

 var data = (from v in abc.visits
                  join m in abc.members on v.member_Id equals m.member_Id
                  select new
                  {
                      MemberID = v.member_Id,
                      VisiteDate = v.visit_Date,
                      FirstName = m.member_Firstname,
                      LastName = m.member_Lastname
                  }).ToList();

      var membersdata = from d in data
                        where Convert.ToDateTime(d.VisiteDate) >= startdate && Convert.ToDateTime(d.VisiteDate) <= enddate
                        group m by new { d.FirstName, d.LastName, d.MemberID } into g
                        orderby g.Count()
                        select new
                        {
                            numVisits = g.Count(),
                            firstname = g.Key.FirstName,
                            lastname = g.Key.LastName
                        }; 
1
13
10/12/2011 2:07:28 PM

Accepted Answer

I don't believe EF has a translation for converting a String to a DateTime or the other way around.

According on how the date is formatted in the string field, you have two choices:

An easy-to-use format could just need a string comparison:

// Convert the boundaries to strings first
// TODO: Set the ToString format option to match the database format
string startDateAsString = startdate.ToString("yyyyMMdd");
string endDateAsString = enddate.ToString("yyyyMMdd");

// Query based on string comparison
var memberl = from v in abc.visits
              join m in abc.members on v.member_Id equals m.member_Id
              where v.visit_Date.CompareTo(startDateAsString) >= 0 && 
                    v.visit_Date.CompareTo(endDateAsString) <= 0
              group m by new { m.member_Firstname, 
                               m.member_Lastname, m.member_Id } into g
              orderby g.Count()
              select new
              {
                  numVisits = g.Count(),
                  firstname = g.Key.member_Firstname,
                  lastname = g.Key.member_Lastname
              };

If a straightforward string comparison is ineffective because the string representation of the date is more complicated, you may think about developing aview on thevisits the database level table, which does the conversion for you:

CREATE VIEW VisitsWithDate (MemberId, VisitDate)
AS
SELECT MemberId, Convert(datetime, VisitDate, 112) -- For instance
FROM Visits

then adding this view to your DataModel after that. It's possible that some magic is required to make the linkages work.

Hope it's useful.

18
10/12/2011 2:17:34 PM

Popular Answer

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 convertstartdate and enddate in string and use a linq expression to compare the value of the string.



Related Questions





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