How can I convert the string to datetime using linq to entities ....
I have got the below query, where the visit_date
column datatype 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
};
Unfortunately I can't change the schema ...
I have got the error:
linq to entites does not recognise Convert.ToDatetime method
Is there any possible solution for converting string to Datetime?
Updated code:
as per request i Have updated my question
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
};
I don't think EF supports a translation for a String to DateTime or vice-versa conversion.
As I see it, you have two options, depending on the format of the date in the string field:
If the format is fairly simple, a string comparison might be enough:
// 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 the string representation of the date is more complex, and a simple string comparison cannot help, you might consider creating a view
on the visits
table, which does the conversion for you at database level:
CREATE VIEW VisitsWithDate (MemberId, VisitDate)
AS
SELECT MemberId, Convert(datetime, VisitDate, 112) -- For instance
FROM Visits
Followed by importing this view into your DataModel. You might need to do some magic to make the relationships work.
Hope it helps.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With