Invalid sql statement with groupcount on nullable type

Discussion of open issues, suggestions and bugs regarding LinqConnect – Devart's LINQ to SQL compatible ORM
Post Reply
ChrisT
Posts: 6
Joined: Mon 26 Feb 2018 16:05

Invalid sql statement with groupcount on nullable type

Post by ChrisT » Thu 15 Aug 2019 13:33

Hello Devart-Team,

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();
	}
}
The generated sql looks like:

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]
Is there anything wrong I do on my side?
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

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Invalid sql statement with groupcount on nullable type

Post by Shalex » Tue 20 Aug 2019 12:37

Thank you for your report. We will investigate the issue and notify you about the result.

ChrisT
Posts: 6
Joined: Mon 26 Feb 2018 16:05

Re: Invalid sql statement with groupcount on nullable type

Post by ChrisT » Wed 25 Sep 2019 13:53

Hello there, any news on this?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Invalid sql statement with groupcount on nullable type

Post by Shalex » Mon 30 Sep 2019 14:02

The investigation is in progress. As soon as we have any results, we will contact you.

ChrisT
Posts: 6
Joined: Mon 26 Feb 2018 16:05

Re: Invalid sql statement with groupcount on nullable type

Post by ChrisT » Fri 02 Oct 2020 07:29

Hello,

it's been a year now. Are there any news for this topic? We still need a solution for this.

Greetings,
Chris

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Invalid sql statement with groupcount on nullable type

Post by Shalex » Mon 05 Oct 2020 17:02

The investigation is in progress. We cannot provide a timeframe for the fix.

Post Reply