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 can I convert this SQL into LINQ? My entity name is dbcontext
. Thanks in advance for any help.
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()
}
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
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With