Linq2DB adding unnecessary statements to query

Question

I am trying to generate a query that gets the number of entries with dates that occur within each given month. The SQL form of the query I wish to generate goes like this:

SELECT
DatePart(Year, [t1].[StartTime]) as Year,
DatePart(Month, [t1].[StartTime]) as Month,
Count(*) as 'Visits',
Sum([t1].[PageCount]) as 'Total Pageviews'
FROM
[MyDatabase] [t1]
GROUP BY
DatePart(Year, [t1].[StartTime]),
DatePart(Month, [t1].[StartTime])

I tried the following Linq2DB code:

var query = from table in dataContext.MyDataContext(tablePath)
group table by new { table.StartTime.Year, table.StartTime.Month} into grp
select new { Month = grp.Key.Month, Year = grp.Key.Year,
TotalVisitors =   grp.Count(), 
TotalPageviews = grp.Sum(table2 => table2.PageCount) };

but the SQL query being generated by this is

--  SqlServer.2008  --
SELECT
[t1].[StartTime],
Count(*) as [c1],
Sum([t1].[PageCount]) as [c2]
FROM
[MyDatabase] [t1]
GROUP BY
DatePart(Year, [t1].[StartTime]),
DatePart(Month, [t1].[StartTime]),
[t1].[StartTime]

Why is [t1].[StartTime] rather than the month or year? And why is it grouping by that extra [t1].[StartTime] at the end? How do I generate the SQL query I have above using Linq2DB?

score 1 · Answer 1 · answered May 15 '17 at 11:17

Please use the following workaround with Sql.AsSql function. We are trying to fix this longstanding issue.

var query = from table in dataContext.MyDataContext(tablePath)
group table by new { Sql.AsSql(table.StartTime.Year), Sql.AsSql(table.StartTime.Month)} into grp
select new { Month = Sql.AsSql(grp.Key.Month), Year = Sql.AsSql(grp.Key.Year),
TotalVisitors =   grp.Count(), 
TotalPageviews = grp.Sum(table2 => table2.PageCount) };

Linq2DB adding unnecessary statements to query

1 Answers1