使用链接到实体将字符串转换为日期时间的大问题
本文关键字:日期 时间 大问题 转换 字符串 链接 实体 | 更新日期: 2023-09-27 18:10:58
如何使用linq to entities将字符串转换为日期时间....
我得到了下面的查询,其中visit_date
列数据类型是字符串…
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
};
不幸的是我不能改变schema…
我得到了错误:
linq to entites does not recognise Convert.ToDatetime method
是否有任何可能的解决方案将字符串转换为日期时间?
更新代码:
根据请求我已经更新了我的问题
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
};
我不认为EF支持String到DateTime的转换,反之亦然。
在我看来,根据字符串字段中日期的格式,您有两个选项:
如果格式相当简单,一个字符串比较可能就足够了:
// 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
};
如果日期的字符串表示更复杂,简单的字符串比较无法提供帮助,您可以考虑在visits
表上创建view
,它将在数据库级别为您完成转换:
CREATE VIEW VisitsWithDate (MemberId, VisitDate)
AS
SELECT MemberId, Convert(datetime, VisitDate, 112) -- For instance
FROM Visits
然后将这个视图导入到您的DataModel中。你可能需要施展一些魔法来让这些关系发挥作用。
希望能有所帮助。
Convert.ToDatetime
是Linq2SQL支持的。Linq唯一支持的实体方法如下:http://msdn.microsoft.com/en-us/library/bb738681.aspx
startdate
和enddate
转换为字符串,并比较linq表达式中的字符串值。
首先尝试将结果转换为List<>
,然后从列表中过滤结果:
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 memberl = from d in data
where Convert.ToDateTime(d.VisitDate) >= startdate && Convert.ToDateTime(d.VisitDate) <= enddate
group d 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
};
因为DateTime和DateTimeOffset是结构体,它们本质上不是可空的。当您需要可空性时,有两种方法:
- 使用可空类型(即DateTime?还是DateTimeOffset ?)。
- 使用静态字段DateTime。MinValue或DateTimeOffset。MinValue(默认值)