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.