Hi, Otis

This time it seems I cannot get the Sum() to work with a group by query when the query has a table join.

The queries are based on NW database.

Here is the query that works. It is without a table join.

        var result = from o in metaData.Orders
                     where o.OrderDate > new DateTime(1998,1,1)
                     group o by o.CustomerId into g
                     select new { g.Key, cnt = g.Count(), total = g.Sum(a => a.OrderId)};

Here is the query that breaks. It has a table join.

            var result2 = from o in metaData.Orders
                          join em in metaData.Employees on o.EmployeeId equals em.EmployeeId
                     where o.OrderDate > new DateTime(1998,1,1)
                     group o by o.CustomerId into g
                     select new { g.Key, cnt = g.Count(), total = g.Sum(a => a.OrderId)};

Thanks for your help.

Reproduced with LLBLGen RTL 2.6.8.709.

Generated SQL

SELECT 
    [LPA_L1].[CustomerId] AS [Key], 
    [LPA_L1].[LPAV_] AS [cnt], 
    [LPA_L1].[LPAV_1] AS [total] 
FROM    ( SELECT 
        [LPA_L3].[CustomerID] AS [CustomerId], 
        COUNT(*) AS [LPAV_], 
        SUM([LPLA_3].[OrderID] ) AS [LPAV_1]

      FROM ( [Northwind].[dbo].[Orders] [LPA_L3]  
        INNER JOIN [Northwind].[dbo].[Employees] [LPA_L4]  
            ON  [LPA_L3].[EmployeeID] = [LPA_L4].[EmployeeID] )

      WHERE ( (( [LPA_L3].[OrderDate] > @OrderDate1)) ) 
    
      GROUP BY [LPA_L3].[CustomerID]
    ) [LPA_L1]
    

Parameter: @OrderDate1 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 01/01/1998 12:00:00 a.m..

Exception message An exception was caught during the execution of a retrieval query: The multi-part identifier "LPLA_3.OrderID" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Stack Trace

at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) at Northwind.DAL.DataAccessAdapter.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) in C:\Documents and Settings\David\My Documents\Dev\LLBLGenPro\v2.6\Adapter\Northwind\code\DAL\DataAccessAdapter.cs:line 292 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Dictionary2 typeConvertersToRun)

Anyway, does this join make sense ?

join em in metadata.Employee on o.EmployeeId equals em.EmployeeId

Query sounds like something we've in the tests. I'll check it out.

(edit) Indeed, doesn't work in latest build either. Looking into it. (likely a re-aliasing issue. With a join and a groupby it's a bit hard to track down which side of the join was referred to as the aggregate expression simply refers to the joined set... )

Finally, FINALLY, I found what's the problem....

As I described above, it's impossible to determine the real source of the aggregate if they refer to the group by which refers to a join (or worse, as in this case, to a where which deep down refers to a join)... which side of the join is used? I've puzzled about this a couple of times before and had implemented some workarounds which would produce the right side, but obviously this doesn't always work, like in this query.

To my surprise I suddenly understood that the aggregate source is the same as the groupby source (i.e.: the elements grouped are the same as the elements aggregated). Yeah, pretty 'Duh!', if you ask me, but through all the expression goo it sometimes is a bit hard to find the causes of problems.

So this went OK, unless you grouped on a field in a related entity. THAT caused me a couple of hours, but I suddenly saw I ignored a complete lambda in the groupby method call. (as it was always the same as the groupby list). It's not the same in cases where the grouping is on related fields! So I've to connect these dots and then it should work stay tuned.

Fixed it. What a struggle...

All those different lambda variants in the GroupBy method call... Add to that that the vb.net compiler creates different stuff...

but! it works. Please use the attached dll.

Thanks guys! Your new binary fixed the problem.

But now there is another problem. I have isolated it to calling mapped db functions with group key's property.

Here are the queries against NW database. The first query works but the second fails.

                LinqMetaData metaData = new LinqMetaData(adapter);
                metaData.CustomFunctionMappings = new DbFunctionMappings();

                //group by one field, then call function, use the key as parameter
                // works
                var result1 = from em in metaData.Employees
                              group em by em.City into q
                              select new { city = q.Key, UPPERCITY = DbFunctions.TOUPPER(q.Key) };
                IList list1 = result1.ToList();

                //group by two fields, then call function use one of the keys as parameter
                // got exception
                var result2 = from em in metaData.Employees
                              group em by new { em.City, em.Country } into q
                              select new { city = q.Key.City, UPPERCITY = DbFunctions.TOUPPER(q.Key.City) };
                IList list2 = result2.ToList();

Here is the function mapping that we made.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Northwind.EntityClasses;
using Northwind.Linq;
using Northwind.DatabaseSpecific;
using SD.LLBLGen.Pro.ORMSupportClasses;
using System.Collections;
namespace LLBLTest
{

    /// Class which is used to specify the call to the database function. We'll map
    /// the function inside it, CalculateOrderTotal, to the database function.
    public class DbFunctions
    {
        /// <summary>
        /// change string to upper case
        /// </summary>
        /// <param name="str">string.</param>
        /// <returns></returns>
        public static string TOUPPER(string str)
        {
            // empty body, as it's just here to make the query compile. The call is converted to a SQL function.
            return "";
        }
    }

    public class DbFunctionMappings : FunctionMappingStore
    {
        public DbFunctionMappings()
            : base()
        {
            this.Add(new FunctionMapping(typeof(DbFunctions), "TOUPPER", 1, "UPPER({0})"));
        }
    }

}

Will look into it.

Please, Savanna, NEXT time post stack trace and exception details! Btw, these queries worked before? That's not entirely clear from your post.

(edit) I get a MemberAccess is unexpected error. This is related to the fact that the reference on the key in that context isn't expected, and I think this also didn't work before the fix I posted above.

(edit) hmm. Member access on groupby key reference isn't resulting in a proper element. If that's added it works. (just doing City = g.Key.City also doesn't work, it results in a query with both key elements in the resultset, the City is then retrieved from that in-memory.

Fixed it. You now also can request properties on key members and g.Key, if they've mappings on a db construct. it will do an in-memory resolve if no mapping is found.

See attached dll.

Thank you Otis. You are fast! Next time I will certainly post exception and the stack trace. Thanks again.