我有一个名为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()
- }
