"group by" drops columns from sub-query

[inosyryev]

LINQ

var q = (from engineeringCircuitEnd in repository.EngineeringCircuitEnds
         where engineeringCircuitEnd.ServiceCircuitID != null
         select new
             {
                 RevisionId = engineeringCircuitEnd.EngineeringConnectoRef.HarnessRef.RevisionID,
                 engineeringCircuitEnd.EngineeringCircuitNumberRef.EngineeringCircuitNumber,
                 ServiceFunction =
                     engineeringCircuitEnd.ServiceCircuitRef.ServiceFunctionRef.ServiceFunctionNames
                         ?? string.Empty
             }).Distinct();

var q2 =
    from t3 in q
    group t3 by new { t3.RevisionId, t3.EngineeringCircuitNumber }
    into g
    where g.Count() > 1
    select new { g.Key.RevisionId, g.Key.EngineeringCircuitNumber, Count = g.Count() };

SQL

From sub-query "q" executed alone (notice column Coalesce([t4].[ServiceFunctionNames], '') as [c1]):

SELECT DISTINCT
    [t1].[RevisionID],
    [t3].[EngineeringCircuitNumber],
    Coalesce([t4].[ServiceFunctionNames], '') as [c1]
FROM
    [EngineeringCircuitEnd] [engineeringCircuitEnd]
        INNER JOIN [EngineeringConnector] [t2]
            INNER JOIN [Harness] [t1] ON [t2].[HarnessID] = [t1].[HarnessID]
        ON [engineeringCircuitEnd].[EngineeringConnectorID] = [t2].[EngineeringConnectorID]
        LEFT JOIN [EngineeringCircuitNumber] [t3] ON [engineeringCircuitEnd].[EngineeringCircuitNumberID] = [t3].[EngineeringCircuitNumberID]
        LEFT JOIN [ServiceCircuitEnd] [t5]
            LEFT JOIN [ServiceFunctionNames] [t4] ON [t5].[ServiceFunctionID] = [t4].[ServiceFunctionID]
        ON [engineeringCircuitEnd].[ServiceCircuitID] = [t5].[ServiceCircuitID]
WHERE
    [engineeringCircuitEnd].[ServiceCircuitID] IS NOT NULL

From main query "q2" (notice that [ServiceFunctionNames] is missing):

SELECT
    [g].[RevisionID1] as [RevisionID11],
    [g].[EngineeringCircuitNumber1] as [EngineeringCircuitNumber11],
    [g].[c1] as [c11]
FROM
    (
        SELECT
            Count(*) as [c1],
            [t4].[RevisionID] as [RevisionID1],
            [t4].[EngineeringCircuitNumber] as [EngineeringCircuitNumber1]
        FROM
            (
                SELECT DISTINCT
                    [t1].[RevisionID],
                    [t3].[EngineeringCircuitNumber]
                FROM
                    [EngineeringCircuitEnd] [engineeringCircuitEnd]
                        INNER JOIN [EngineeringConnector] [t2]
                            INNER JOIN [Harness] [t1] ON [t2].[HarnessID] = [t1].[HarnessID]
                        ON [engineeringCircuitEnd].[EngineeringConnectorID] = [t2].[EngineeringConnectorID]
                        LEFT JOIN [EngineeringCircuitNumber] [t3] ON [engineeringCircuitEnd].[EngineeringCircuitNumberID] = [t3].[EngineeringCircuitNumberID]
                WHERE
                    [engineeringCircuitEnd].[ServiceCircuitID] IS NOT NULL
            ) [t4]
        GROUP BY
            [t4].[RevisionID],
            [t4].[EngineeringCircuitNumber]
    ) [g]
WHERE
    [g].[c1] > 1

Problems

The columns [ServiceFunctionNames].[ServiceFunctionNames] exists when the sub-query is executed alone. It disappears as soon as the sub-query is grouped by columns other than [ServiceFunctionNames].[ServiceFunctionNames].

BLToolkit generated files

Sub-query:

//---------------------------------------------------------------------------------------------------
// This code was generated by BLToolkit.
//---------------------------------------------------------------------------------------------------
using System;
using System.Linq.Expressions;

using NUnit.Framework;

namespace SPX.GCIS.Data.Sql
{
    [BLToolkit.DataAccess.TableNameAttribute(Name = "EngineeringCircuitEnd")]
    public class EngineeringCircuitEndRecord
    {
        [BLToolkit.Mapping.AssociationAttribute(ThisKey = "EngineeringConnectorID", OtherKey = "EngineeringConnectorID", CanBeNull = False)]
        public SPX.GCIS.Data.Sql.EngineeringConnectorRecord EngineeringConnectoRef { get; set; }

        [BLToolkit.Mapping.AssociationAttribute(ThisKey = "ServiceCircuitID", OtherKey = "ServiceCircuitID", CanBeNull = True)]
        public SPX.GCIS.Data.Sql.ServiceCircuitEndRecord ServiceCircuitRef { get; set; }

        [BLToolkit.Mapping.AssociationAttribute(ThisKey = "EngineeringCircuitNumberID", OtherKey = "EngineeringCircuitNumberID", CanBeNull = True)]
        public SPX.GCIS.Data.Sql.EngineeringCircuitNumberRecord EngineeringCircuitNumberRef { get; set; }

        [BLToolkit.Mapping.NullableAttribute()]
        public int? ServiceCircuitID { get; set; }
    }
}

namespace SPX.GCIS.Data.Sql
{
    [BLToolkit.DataAccess.TableNameAttribute(Name = "EngineeringCircuitNumber")]
    public class EngineeringCircuitNumberRecord
    {
        public string EngineeringCircuitNumber { get; set; }
    }
}

namespace SPX.GCIS.Data.Sql
{
    [BLToolkit.DataAccess.TableNameAttribute(Name = "EngineeringConnector")]
    public class EngineeringConnectorRecord
    {
        [BLToolkit.Mapping.AssociationAttribute(ThisKey = "HarnessID", OtherKey = "HarnessID", CanBeNull = False)]
        public SPX.GCIS.Data.Sql.HarnessRecord HarnessRef { get; set; }
    }
}

namespace SPX.GCIS.Data.Sql
{
    [BLToolkit.DataAccess.TableNameAttribute(Name = "Harness")]
    public class HarnessRecord
    {
        public int RevisionID { get; set; }
    }
}

namespace SPX.GCIS.Data.Sql
{
    [BLToolkit.DataAccess.TableNameAttribute(Name = "ServiceCircuitEnd")]
    public class ServiceCircuitEndRecord
    {
        [BLToolkit.Mapping.AssociationAttribute(ThisKey = "ServiceFunctionID", OtherKey = "ServiceFunctionID", CanBeNull = True)]
        public SPX.GCIS.Data.Sql.ServiceFunctionNameRecord ServiceFunctionRef { get; set; }
    }
}

namespace SPX.GCIS.Data.Sql
{
    [BLToolkit.DataAccess.TableNameAttribute(Name = "ServiceFunctionNames")]
    public class ServiceFunctionNameRecord
    {
        public string ServiceFunctionNames { get; set; }
    }
}

namespace Data.Linq
{
    [TestFixture]
    public class UserTest : TestBase
    {
        [Test]
        public void Test()
        {
            // Table(EngineeringCircuitEndRecord).Where(engineeringCircuitEnd => (engineeringCircuitEnd.ServiceCircuitID != null)).Select(engineeringCircuitEnd => new <>f__AnonymousTypec`3(RevisionId = engineeringCircuitEnd.EngineeringConnectoRef.HarnessRef.RevisionID, EngineeringCircuitNumber = engineeringCircuitEnd.EngineeringCircuitNumberRef.EngineeringCircuitNumber, ServiceFunction = (engineeringCircuitEnd.ServiceCircuitRef.ServiceFunctionRef.ServiceFunctionNames ?? String.Empty))).Distinct()
            ForEachProvider(db =>
                Table(EngineeringCircuitEndRecord)
                    .Where<SPX.GCIS.Data.Sql.EngineeringCircuitEndRecord>(
                        (SPX.GCIS.Data.Sql.EngineeringCircuitEndRecord engineeringCircuitEnd) => (engineeringCircuitEnd.ServiceCircuitID != null))
                    .Select(
                        (SPX.GCIS.Data.Sql.EngineeringCircuitEndRecord engineeringCircuitEnd) => new
                        {
                            RevisionId = engineeringCircuitEnd.EngineeringConnectoRef.HarnessRef.RevisionID,
                            EngineeringCircuitNumber = engineeringCircuitEnd.EngineeringCircuitNumberRef.EngineeringCircuitNumber,
                            ServiceFunction = (engineeringCircuitEnd.ServiceCircuitRef.ServiceFunctionRef.ServiceFunctionNames ?? .Empty)
                        })
                    .Distinct());
        }
    }
}

Main query:

//---------------------------------------------------------------------------------------------------
// This code was generated by BLToolkit.
//---------------------------------------------------------------------------------------------------
using System;
using System.Linq.Expressions;

using NUnit.Framework;

namespace SPX.GCIS.Data.Sql
{
    [BLToolkit.DataAccess.TableNameAttribute(Name = "EngineeringCircuitEnd")]
    public class EngineeringCircuitEndRecord
    {
        [BLToolkit.Mapping.AssociationAttribute(ThisKey = "EngineeringConnectorID", OtherKey = "EngineeringConnectorID", CanBeNull = False)]
        public SPX.GCIS.Data.Sql.EngineeringConnectorRecord EngineeringConnectoRef { get; set; }

        [BLToolkit.Mapping.AssociationAttribute(ThisKey = "ServiceCircuitID", OtherKey = "ServiceCircuitID", CanBeNull = True)]
        public SPX.GCIS.Data.Sql.ServiceCircuitEndRecord ServiceCircuitRef { get; set; }

        [BLToolkit.Mapping.AssociationAttribute(ThisKey = "EngineeringCircuitNumberID", OtherKey = "EngineeringCircuitNumberID", CanBeNull = True)]
        public SPX.GCIS.Data.Sql.EngineeringCircuitNumberRecord EngineeringCircuitNumberRef { get; set; }

        [BLToolkit.Mapping.NullableAttribute()]
        public int? ServiceCircuitID { get; set; }
    }
}

namespace SPX.GCIS.Data.Sql
{
    [BLToolkit.DataAccess.TableNameAttribute(Name = "EngineeringCircuitNumber")]
    public class EngineeringCircuitNumberRecord
    {
        public string EngineeringCircuitNumber { get; set; }
    }
}

namespace SPX.GCIS.Data.Sql
{
    [BLToolkit.DataAccess.TableNameAttribute(Name = "EngineeringConnector")]
    public class EngineeringConnectorRecord
    {
        [BLToolkit.Mapping.AssociationAttribute(ThisKey = "HarnessID", OtherKey = "HarnessID", CanBeNull = False)]
        public SPX.GCIS.Data.Sql.HarnessRecord HarnessRef { get; set; }
    }
}

namespace SPX.GCIS.Data.Sql
{
    [BLToolkit.DataAccess.TableNameAttribute(Name = "Harness")]
    public class HarnessRecord
    {
        public int RevisionID { get; set; }
    }
}

namespace SPX.GCIS.Data.Sql
{
    [BLToolkit.DataAccess.TableNameAttribute(Name = "ServiceCircuitEnd")]
    public class ServiceCircuitEndRecord
    {
        [BLToolkit.Mapping.AssociationAttribute(ThisKey = "ServiceFunctionID", OtherKey = "ServiceFunctionID", CanBeNull = True)]
        public SPX.GCIS.Data.Sql.ServiceFunctionNameRecord ServiceFunctionRef { get; set; }
    }
}

namespace SPX.GCIS.Data.Sql
{
    [BLToolkit.DataAccess.TableNameAttribute(Name = "ServiceFunctionNames")]
    public class ServiceFunctionNameRecord
    {
        public string ServiceFunctionNames { get; set; }
    }
}

namespace Data.Linq
{
    [TestFixture]
    public class UserTest : TestBase
    {
        [Test]
        public void Test()
        {
            // Table(EngineeringCircuitEndRecord).Where(engineeringCircuitEnd => (engineeringCircuitEnd.ServiceCircuitID != null)).Select(engineeringCircuitEnd => new <>f__AnonymousTypec`3(RevisionId = engineeringCircuitEnd.EngineeringConnectoRef.HarnessRef.RevisionID, EngineeringCircuitNumber = engineeringCircuitEnd.EngineeringCircuitNumberRef.EngineeringCircuitNumber, ServiceFunction = (engineeringCircuitEnd.ServiceCircuitRef.ServiceFunctionRef.ServiceFunctionNames ?? String.Empty))).Distinct().GroupBy(t3 => new <>f__AnonymousTyped`2(RevisionId = t3.RevisionId, EngineeringCircuitNumber = t3.EngineeringCircuitNumber)).Where(g => (g.Count() > 1)).Select(g => new <>f__AnonymousTypee`3(RevisionId = g.Key.RevisionId, EngineeringCircuitNumber = g.Key.EngineeringCircuitNumber, Count = g.Count()))
            ForEachProvider(db =>
                Table(EngineeringCircuitEndRecord)
                    .Where<SPX.GCIS.Data.Sql.EngineeringCircuitEndRecord>(
                        (SPX.GCIS.Data.Sql.EngineeringCircuitEndRecord engineeringCircuitEnd) => (engineeringCircuitEnd.ServiceCircuitID != null))
                    .Select(
                        (SPX.GCIS.Data.Sql.EngineeringCircuitEndRecord engineeringCircuitEnd) => new
                        {
                            RevisionId = engineeringCircuitEnd.EngineeringConnectoRef.HarnessRef.RevisionID,
                            EngineeringCircuitNumber = engineeringCircuitEnd.EngineeringCircuitNumberRef.EngineeringCircuitNumber,
                            ServiceFunction = (engineeringCircuitEnd.ServiceCircuitRef.ServiceFunctionRef.ServiceFunctionNames ?? .Empty)
                        })
                    .Distinct()
                    .GroupBy(
                        (t3) => new
                        {
                            RevisionId = t3.RevisionId,
                            EngineeringCircuitNumber = t3.EngineeringCircuitNumber
                        })
                    .Where<System.Linq.IGrouping<>>(
                        (System.Linq.IGrouping<> g) => (g
                            .Count() > 1))
                    .Select(
                        (System.Linq.IGrouping<> g) => new
                        {
                            RevisionId = g.Key.RevisionId,
                            EngineeringCircuitNumber = g.Key.EngineeringCircuitNumber,
                            Count = g
                                .Count()
                        }));
        }
    }
}