on a queryable I want to group by a property and count the values that are not null in the groups. The resulting sql statement generates errors.
You can reproduce it with the following code:
Code: Select all
using Devart.Data.Linq;
using Devart.Data.Linq.Mapping;
using Devart.Data.SqlServer.Linq.Provider;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
internal class Program
{
[Provider(typeof(SqlDataProvider))]
class SqlDataContext : DataContext
{
public SqlDataContext(IDbConnection connection) : base(connection)
{
}
}
class QueryTypeNullable
{
public string GroupColumn { get; set; }
public int? ValueColumn { get; set; }
}
static void Main(string[] args)
{
SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder()
{
DataSource = @"(LocalDB)\MSSQLLocalDB",
AttachDBFilename = "|DataDirectory|Database.mdf",
IntegratedSecurity = true,
};
SqlConnection connection = new SqlConnection(connectionStringBuilder.ToString());
SqlDataContext dataContext = new SqlDataContext(connection);
string query = @"
SELECT 'A' AS GroupColumn, 10 AS ValueColumn
UNION ALL
SELECT 'A' AS GroupColumn, NULL AS ValueColumn
";
var queryable = dataContext.Query<QueryTypeNullable>(query);
var result = queryable
.GroupBy(row => row.GroupColumn)
.Select(group => new
{
Group = group.Key,
NotNullCount = group.Count(row => row.ValueColumn.HasValue),
})
.ToList();
}
}
Code: Select all
SELECT [t1].[GroupColumn] AS [Key], (
SELECT COUNT(*) AS [C1]
FROM (
SELECT 'A' AS GroupColumn, 10 AS ValueColumn
UNION ALL
SELECT 'A' AS GroupColumn, NULL AS ValueColumn
) [t3]
WHERE ([t1].[ValueColumn] IS NOT NULL) AND ([t1].[GroupColumn] = [t1].[GroupColumn])
) AS [C1]
FROM (
SELECT [t3].GroupColumn AS [GroupColumn], [t3].ValueColumn AS [ValueColumn]
FROM (
SELECT 'A' AS GroupColumn, 10 AS ValueColumn
UNION ALL
SELECT 'A' AS GroupColumn, NULL AS ValueColumn
) [t3]
GROUP BY [t3].GroupColumn
) [t1]
Or can you may fix this?
Hint: I tested this with static data and with entity framework. The statement that is there generated is working fine.
Chris