Hi, i was playing with Linq to LLBL and tried the following

var test = from c in metaData.Customer
                 join o in metaData.Order on c.CustomerID equals o.CustomerID
                 group new {c, o} by new {c, o} into g
                 select new { customer = g.Key, total = g.Sum(x => x.o.Price) };

Then I thought I could simply do

foreach (var t in test)
{
     Console.WriteLine(t.customer.CustomerID + t.total);
}

but I get an exception

The multi-part identifier "LPLA_1.CustomerID" could not be bound.
The multi-part identifier "LPLA_2.OrderID" could not be bound.
The multi-part identifier "LPLA_2.CustomerID" could not be bound.
The multi-part identifier "LPLA_2.Price" could not be bound.

Generated Sql query: 
    Query: SELECT DISTINCT [LPA_L3].[CustomerID] AS [CustomerId], [LPA_L4].[OrderID] AS [OrderId], [LPA_L4].[CustomerID] AS [CustomerId0], [LPA_L4].[Price], SUM([LPA_L4].[Price]) AS [LPAV_] FROM ( [testdb].[dbo].[Customer] [LPA_L3]  INNER JOIN [testdb].[dbo].[Order] [LPA_L4]  ON  [LPA_L3].[CustomerID] = [LPA_L4].[CustomerID]) GROUP BY [LPA_L3].[CustomerID], [LPA_L4].[OrderID], [LPA_L4].[CustomerID], [LPA_L4].[Price]

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT [LPLA_1].[CustomerID] AS [CustomerId], [LPLA_2].[OrderID] AS [OrderId], [LPLA_2].[CustomerID] AS [CustomerId0], [LPLA_2].[Price], [LPA_L1].[LPAV_] AS [total] FROM (SELECT DISTINCT [LPA_L3].[CustomerID] AS [CustomerId], [LPA_L4].[OrderID] AS [OrderId], [LPA_L4].[CustomerID] AS [CustomerId0], [LPA_L4].[Price], SUM([LPA_L4].[Price]) AS [LPAV_] FROM ( [testdb].[dbo].[Customer] [LPA_L3]  INNER JOIN [testdb].[dbo].[Order] [LPA_L4]  ON  [LPA_L3].[CustomerID] = [LPA_L4].[CustomerID]) GROUP BY [LPA_L3].[CustomerID], [LPA_L4].[OrderID], [LPA_L4].[CustomerID], [LPA_L4].[Price]) [LPA_L1]

What exactly is the 2nd query? I don't see the alias [LPLA_1] and [LPLA_2] defined anywhere?

Many thanks

SD.LLBLGen.Pro.DQE.SqlServer.NET20 ( 2.6.9.917 ) SD.LLBLGen.Pro.LinqSupportClasses.NET35 ( 2.6.9.1008 ) SD.LLBLGen.Pro.ORMSupportClasses.NET20 ( .6.9.1005 )

var test = from c in metaData.Customer join o in metaData.Order on c.CustomerID equals o.CustomerID group new {c, o} by new {c, o} into g select new { customer = g.Key, total = g.Sum(x => x.o.Price) };

First of all: What exactly that you want to fetch with the above query? I see you group by the all fields of the Customer and Order entities, so I think the Aggregation (Sum) will have no effecr.

Also, you're fetching entities into an anonymous type. This isn't supported, as llblgen pro uses two different pipelines for fetching data: entities use the entity fetch pipeline (so inheritance is taken into account) and projections (like the one you use) are using the projection pipeline. This means that at the point where an entity has to be inserted into the anonymous type, it's not yet materialized.

We know this is a limitation of our linq provider and we're looking into fixing this for v3, but it's quite cumbersome to do, due to polymorphic fetches. So in short: don't fetch entities inside a custom projection (Like, don't do: var q = from c in metaData.Customer select new { Customer=c, Orders = c.Orders};

as that won't work, fetch the entities using an entity fetch, so return the entities themselves.

I agree with Walaa, your query is a bit unclear what you want to achieve, if you could elaborate a bit, perhaps we can suggest a workaround for you.

Walla/Otis: I was trying to fetch the customer entity and an aggregate field using a single query

ie

SELECT C.CustomerID, C.Name, C.Address, C.City, Sum(o.Price)
FROM Customer as "c"
JOIN [Order] as "o" on c.CustomerID = o.CustomerID
GROUP BY C.CustomerID, C.Name, C.Address, C.City

For now, my workaround is to select every columns in the select/group statement

from c in metaData.Customer
join o in metaData.Order on c.CustomerID equals o.CustomerID
group new { c, o } by new { c.CustomerId, c.Name, c.Address, c.City }
select new
{
     id = g.Key.CustomerID,
     name = g.Key.Name,
     address = g.Key.Address,
     city = g.Key.City,
     total = g.Sum(x => x.o.Price)
}

but it would be really cool if I could do it like in my first post.

The above scenario is dumbed down and the query will get huge if joining multiple tables so that's why I thought a shortcut like in my first post would work. Unless there is a simpler way of doing it using LINQ? I could do it using a dynamic list but it's much more elegant using linq. If v3 has this feature, it is definitely a buy from me

n/m

n\m ?