Linq problems

A few problems with Linq:

(1) var res = from c in uow.Customers join o in uow.Orders on c.Id equals o.CustomerId into orderGroup select new { Name = c.ContactName, Orders = orderGroup.Count()};

This reports: unexpected expression type Parameter in projection expression

(2) var res = from c in uow.Customers join o in uow.Orders on c.Id equals o.CustomerId into orderGroup select new { Name = c.ContactName, OrdersSum = orderGroup};

SQL profiler returns too many fields (fields marked red should not be in select clause, because they are not specified in linq query)

SELECT [t0].[Id] AS [t0.Id], [t0].[Address] AS [t0.Address], [t0].[City] AS [t0.City], [t0].[CompanyName] AS [t0.CompanyName], [t0].[ContactName] AS [t0.ContactName], [t0].[ContactTitle] AS [t0.ContactTitle], [t0].[Country] AS [t0.Country], [t0].[CustomerType] AS [t0.CustomerType], [t0].[Fax] AS [t0.Fax], [t0].[Phone] AS [t0.Phone], [t0].[PostalCode] AS [t0.PostalCode], [t0].[Region] AS [t0.Region], [t1].[Id] AS [t1.Id], [t1].[CustomerId] AS [t1.CustomerId], [t1].[EmployeeId] AS [t1.EmployeeId], [t1].[Freight] AS [t1.Freight], [t1].[OrderDate] AS [t1.OrderDate], [t1].[ShippedDate] AS [t1.ShippedDate] FROM [Customer] [t0] LEFT OUTER JOIN [Order] [t1] ON [t0].[Id] = [t1].[CustomerId]

(3) var res = from c in uow.Customers join o in uow.Orders on c.Id equals o.CustomerId into group1 //where c.Id > 0 select new { Name = c.ContactName, OrdersCount = group1.Count()};

If I uncomment 3rd line query works as expected else an exception is raised unexpected expression type Parameter in projection expression

(4) Also I saw the page with linq limitations. What exactly do you mean by group joins over more than two tables?

Can you give any comments?

Please find attached also a file in a more readable form.

Thanks for letting us know about this. (1) and (3) are the same issue and I have implemented a fix which will be in the next nightly build. (2) occurs because some projections are too complex for us to translate to SQL, and in these cases we load the data as entities, and perform the projection to non-entities client-side. In your case, the selection of the orderGroup collection is not something LightSpeed can do in SQL alone, hence you are seeing a query to load the entities and therefore all entity fields.

I will have to check on the reference to group joins and will get back to you on this.

(2) So what are the options of selecting data from a join without loading whole entites but instead only a few columns?

(4) Another case: var r = from c in uow.Customers where c.Id > 0 select new { Name = c.ContactName, MyCount=c.Orders.Count()};

SQL actually executed:

exec sp_executesql N'SELECT
[Customer].[ContactName] AS [Customer.ContactName],
[Customer].[CountOrders] AS [Customer.CountOrders]
FROM
(
SELECT
[sxt0].[Id] AS [Id],
[sxt0].[City] AS [City],
[sxt0].[CompanyName] AS [CompanyName],
[sxt0].[ContactName] AS [ContactName],
[sxt0].[ContactTitle] AS [ContactTitle],
[sxt0].[Country] AS [Country],
[sxt0].[CustomerType] AS [CustomerType],
[sxt0].[Fax] AS [Fax],
[sxt0].[Phone] AS [Phone],
[sxt0].[PostalCode] AS [PostalCode],
[sxt0].[Region] AS [Region],
(SELECT
COUNT([sx_CountOrders].[Id]) AS [sx_CountOrders.Id]
FROM
(
SELECT
[Order].[Id] AS [Id],
[Order].[CustomerId] AS [CustomerId]
FROM
[Order]
)
[sx_CountOrders]
WHERE
[sx_CountOrders].[CustomerId] = [sxt0].[Id]
) AS [CountOrders]
FROM
[Customer] [sxt0]
)
[Customer]
WHERE
[Customer].[Id] > @p0',N'@p0 int',@p0=0

Why is this select created: SELECT [Order].[Id] AS [Id], [Order].[CustomerId] AS [CustomerId] FROM [Order] Why not just use Order?