在把字符串大问题使用，以DATETIME LINQ到实体 [英] big issue in converting string to datetime using linq-to-entities
问题描述
我怎么能转换成字符串使用LINQ to实体DATETIME ....
我有以下的查询，其中 visit_date 列的数据类型为字符串...
VAR memberl =从V IN abc.visits
加入米abc.members上v.member_Id等于m.member_Id
其中，Convert.ToDateTime（v.visit_Date）＆GT; =起始日期和放大器;＆安培;
Convert.ToDateTime（v.visit_Date）＆LT; =结束日期
通过新的{m.member_Firstname组男，
m.member_Lastname，m.member_Id}为G
排序依据g.Count（）
选择新
{
numVisits = g.Count（），
名字= g.Key.member_Firstname，
姓氏= g.Key.member_Lastname
};
不幸的是我无法改变的模式...
我已经得到了错误：
的LINQ to entites的不承认Convert.ToDatetime方法
是否有将字符串转换为datetime任何可能的解决方案？
更新code：
为每个请求我已经更新了我的问题。
VAR数据=（从V IN abc.visits
加入米abc.members上v.member_Id等于m.member_Id
选择新
{
会员id = v.member_Id，
VisiteDate = v.visit_Date，
名字= m.member_Firstname，
姓氏= m.member_Lastname
}）了ToList（）。
VAR membersdata =从D数据
其中，Convert.ToDateTime（d.VisiteDate）＆GT; =起始日期和放大器;＆安培; Convert.ToDateTime（d.VisiteDate）＆LT; =结束日期
通过新的{d.FirstName，d.LastName，d.MemberID}为G m组
排序依据g.Count（）
选择新
{
numVisits = g.Count（），
名字= g.Key.FirstName，
姓氏= g.Key.LastName
};
我不认为EF支持转换为字符串日期时间，反之亦然转换。
在我看来，你有两个选择，具体取决于字符串字段的日期格式为：
如果该格式是相当简单的，字符串比较可能就足够了：
//将界限第一为字符串
// TODO：设置的ToString格式选项数据库格式匹配
字符串startDateAsString = startdate.ToString（年月日）;
字符串endDateAsString = enddate.ToString（年月日）;
//查询基于字符串比较
VAR memberl =从V IN abc.visits
加入米abc.members上v.member_Id等于m.member_Id
其中v.visit_Date.CompareTo（startDateAsString）＆GT; = 0＆安培;＆安培;
v.visit_Date.CompareTo（endDateAsString）＆LT; = 0
通过新的{m.member_Firstname组男，
m.member_Lastname，m.member_Id}为G
排序依据g.Count（）
选择新
{
numVisits = g.Count（），
名字= g.Key.member_Firstname，
姓氏= g.Key.member_Lastname
};
如果日期的字符串，再presentation比较复杂，简单的字符串比较不能帮忙，你可以考虑创建一个视图在访问次数表，由它来完成转换为你在数据库级别：
CREATE VIEW VisitsWithDate（会员id，VisitDate）
如
SELECT会员id，转换（日期时间，VisitDate，112） - 例如
从访问
随后导入这种观点到您的DataModel。你可能需要做一些魔法使的关系的工作。
希望它帮助。
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.
这篇关于在把字符串大问题使用，以DATETIME LINQ到实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
