我有一个名为visit的表,其中包含以下列：
visit_Id member_Id visit_Date visit_Time visit_DateTime visit_Status values like (accepted,refused)
我有以下SQL查询：
string sql = @"SELECT CONCAT(UPPER(SUBSTRING(visit_Status,1,1)),SUBSTRING(visit_Status FROM 2)) as Status,COUNT('x') AS Visits FROM visits WHERE visit_Date BETWEEN '2001-09-08' AND '2009-09-09' GROUP BY visit_Status";
解决方法
你需要使用
EntityFunctions
DateTime dateFrom = new DateTime(2001,9,8);
DateTime dateTo = new DateTime(2001,9);
var query = from v in dbcontext.Visits
where v.visit_Date >= dateFrom && v.visit_Date <= dateTo
group v by v.visit_Status into vg
select new
{
Status = EntityFunctions.Concat(EntityFunctions.ToUpper(vg.Key[0]),EntityFunctions.SubString(1,EntityFunctions.Length(vg.Key) -1),Visits = vg.Count()
}
