Convert SQL query to LINQ

Question

I have a table called visit with the following columns:

 visit_Id member_Id visit_Date visit_Time visit_DateTime visit_Status values like (accepted, refused) 

I have the following SQL query:

 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"; 

How to convert this SQL to LINQ? My dbcontext object dbcontext . Thanks in advance for any help.

Answer 1

You need to use EntityFunctions

 DateTime dateFrom = new DateTime(2001, 9, 8); DateTime dateTo = new DateTime(2001, 9, 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() } 

Answer 2

Can you tell me what you are trying to do with Count ('x')?

 from v in dbcontext.visits where v.visit_Date >= "2001-09-08" && v.visitDate <= "2009-09-09" group by v.visit_Status select new { Status = string.Concat(char.ToUpper(v.visit_Status[0], v.visit_Status.Substring(1)), Visits = //Not sure what 'x' is in your example }