I am using LinqPad to test out some LINQ queries that I was hoping to implement into our system. All of these queries are summary queries on tables, so I need to do various group by's.

from T in new LinqMetaData().TaskCenter
join TCC in new LinqMetaData().TaskCenterCategories on T.EventId equals TCC.EventId
join TG in new LinqMetaData().TaskGroup on TCC.TaskCenterListId  equals TG.TaskCenterListId
select TG

These are all tables that exist in the database, no views or grouped types, and this query works fine. It compiles down to sql and gets executed.

Now when I put in a group statement. IE:

from T in new LinqMetaData().TaskCenter
join TCC in new LinqMetaData().TaskCenterCategories on T.EventId equals TCC.EventId
join TG in new LinqMetaData().TaskGroup on TCC.TaskCenterListId  equals TG.TaskCenterListId
group TG by TG.TaskGroupId into g
select g

I get this following error.

ORMQueryConstructionException: The type 'System.Linq.IQueryable`1[[<>f__AnonymousType1`2[[<>f__AnonymousType0`2[[PipeFire2.Data.EntityClasses.TaskCenterEntity, PipeFire2.Data, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null],[PipeFire2.Data.EntityClasses.TaskCenterCategoriesEntity, PipeFire2.Data, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]], query_lervju, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null],[PipeFire2.Data.EntityClasses.TaskGroupEntity, PipeFire2.Data, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]], query_lervju, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null]]' can't be converted to an entity type in any way. Did you use a let statement on a group by clause?

If I switch data providers to regular Linq to Sql it compiles down fine and generates correct SQL.

I can provide other examples as well, if they are needed, however I don't understand that exception error.

What are your runtime library version? make sure you are using the last one: http://llblgen.com/tinyforum/Messages.aspx?ThreadID=12769

I think it shouldn't matter but please try to use just one linqMetadata variable:

var metaData = new LinqMetaData();
from T in new metaData .TaskCenter
join TCC in new metaData .TaskCenterCategories on T.EventId equals TCC.EventId
join TG in newmetaData .TaskGroup on TCC.TaskCenterListId equals TG.TaskCenterListId
select TG

And... What is the variable's type you are assigning this query to?

I updated to the latest LLBLGen version right before doing this test.

http://www.llblgen.com/pages/secure/ListDownloads.aspx?ProductVersion=8 The Nov 4th release

Usually in code I do only make one LinqMetaData object out of concern for efficiency, but this was in LinqPad and it won't let me declare it before hand (or at least I don't know how).

The group by is grouping by an integer, then selecting that integer.

Just to be consistent, here's the DDL code

USE [PipeFire_Streetlinks]
GO
/****** Object:  Table [dbo].[Task_Group]   Script Date: 11/08/2011 08:13:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Task_Group](
    [task_group_id] [int] NOT NULL,
    [task_center_list_id] [int] NOT NULL,
    [task_group_name] [varchar](50) NULL,
    [task_group_identity] [int] IDENTITY(1,1) NOT NULL,
    [task_group_changed] [datetime] NULL,
    [update_time] [int] NOT NULL,
    [warning_threshold] [decimal](18, 6) NULL,
    [oldest_threshold] [int] NULL,
    [oldest_sec_threshold] [int] NULL,
    [work_group] [int] NOT NULL,
 CONSTRAINT [PK_Task_Group] PRIMARY KEY CLUSTERED 
(
    [task_group_identity] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Task_Center_Categories]   Script Date: 11/08/2011 08:13:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Task_Center_Categories](
    [task_center_category_id] [int] IDENTITY(1,1) NOT NULL,
    [task_center_list_id] [int] NOT NULL,
    [event_id] [int] NOT NULL,
    [task_name] [varchar](50) NULL,
    [resolve] [bit] NOT NULL,
    [forward] [bit] NOT NULL,
    [snooze] [bit] NOT NULL,
    [escalate] [bit] NOT NULL,
    [work_group] [int] NOT NULL,
 CONSTRAINT [PK_Task_Center_Categories] PRIMARY KEY CLUSTERED 
(
    [task_center_category_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Task_Center]  Script Date: 11/08/2011 08:13:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Task_Center](
    [task_center_id] [int] IDENTITY(1,1) NOT NULL,
    [order_id] [int] NULL,
    [event_id] [int] NOT NULL,
    [task_client_group] [int] NOT NULL,
    [reason] [varchar](2000) NOT NULL,
    [manual] [bit] NOT NULL,
    [created] [datetime] NOT NULL,
    [last_assigned] [datetime] NULL,
    [todo_date] [datetime] NOT NULL,
    [todo_date_secondary] [datetime] NOT NULL,
    [priority] [int] NOT NULL,
    [login_id] [int] NULL,
    [note] [varchar](5000) NULL,
    [todo_date_login_id] [int] NULL,
    [todo_date_started] [datetime] NULL,
    [forward_login_id] [int] NULL,
    [order_note_id] [int] NULL,
    [queued_login_id] [int] NULL,
    [work_group] [int] NULL,
    [start_calling] [decimal](18, 2) NOT NULL,
    [end_calling] [decimal](18, 2) NOT NULL,
    [forward_timer] [datetime] NULL,
    [office_id] [int] NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[Task_Center] ADD [external_url] [varchar](500) NULL
ALTER TABLE [dbo].[Task_Center] ADD  CONSTRAINT [PK_Task_Center] PRIMARY KEY CLUSTERED 
(
    [task_center_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Default [DF_Call_Center_manual]    Script Date: 11/08/2011 08:13:28 ******/
ALTER TABLE [dbo].[Task_Center] ADD  CONSTRAINT [DF_Call_Center_manual]  DEFAULT ((0)) FOR [manual]
GO
/****** Object:  Default [DF_Call_Center_priority]  Script Date: 11/08/2011 08:13:28 ******/
ALTER TABLE [dbo].[Task_Center] ADD  CONSTRAINT [DF_Call_Center_priority]  DEFAULT ((0)) FOR [priority]
GO
/****** Object:  Default [DF_Task_Center_start_calling] Script Date: 11/08/2011 08:13:28 ******/
ALTER TABLE [dbo].[Task_Center] ADD  CONSTRAINT [DF_Task_Center_start_calling]  DEFAULT ((0.0)) FOR [start_calling]
GO
/****** Object:  Default [DF_Task_Center_end_calling]   Script Date: 11/08/2011 08:13:28 ******/
ALTER TABLE [dbo].[Task_Center] ADD  CONSTRAINT [DF_Task_Center_end_calling]  DEFAULT ((24.0)) FOR [end_calling]
GO
/****** Object:  Default [DF_Task_Center_Categories_resolve]    Script Date: 11/08/2011 08:13:28 ******/
ALTER TABLE [dbo].[Task_Center_Categories] ADD  CONSTRAINT [DF_Task_Center_Categories_resolve]  DEFAULT ((1)) FOR [resolve]
GO
/****** Object:  Default [DF_Task_Center_Categories_forward]    Script Date: 11/08/2011 08:13:28 ******/
ALTER TABLE [dbo].[Task_Center_Categories] ADD  CONSTRAINT [DF_Task_Center_Categories_forward]  DEFAULT ((1)) FOR [forward]
GO
/****** Object:  Default [DF_Task_Center_Categories_snooze] Script Date: 11/08/2011 08:13:28 ******/
ALTER TABLE [dbo].[Task_Center_Categories] ADD  CONSTRAINT [DF_Task_Center_Categories_snooze]  DEFAULT ((1)) FOR [snooze]
GO
/****** Object:  Default [DF_Task_Center_Categories_escalate]   Script Date: 11/08/2011 08:13:28 ******/
ALTER TABLE [dbo].[Task_Center_Categories] ADD  CONSTRAINT [DF_Task_Center_Categories_escalate]  DEFAULT ((0)) FOR [escalate]
GO
/****** Object:  Default [DF_Task_Center_Categories_work_group] Script Date: 11/08/2011 08:13:28 ******/
ALTER TABLE [dbo].[Task_Center_Categories] ADD  CONSTRAINT [DF_Task_Center_Categories_work_group]  DEFAULT ((0)) FOR [work_group]
GO
/****** Object:  Default [DF_Task_Group_update_time]    Script Date: 11/08/2011 08:13:28 ******/
ALTER TABLE [dbo].[Task_Group] ADD  CONSTRAINT [DF_Task_Group_update_time]  DEFAULT ((10)) FOR [update_time]
GO
/****** Object:  Default [DF_Task_Group_work_group] Script Date: 11/08/2011 08:13:28 ******/
ALTER TABLE [dbo].[Task_Group] ADD  CONSTRAINT [DF_Task_Group_work_group]  DEFAULT ((0)) FOR [work_group]
GO

Does the query run fine on your app? I mean, you get this exception from LinqPad, right? Are you using the latest version of linqPad and the latest of the llblgen-linqpad context?

I'm using the latest of both, and I get the same error in my application when I try to execute the same LINQ query.

I think the issue might be in the way you use the IQueryable, the Linq itself might be ok, but how you materialize it might be the problem.

Please note that 'select g' selects an IGrouping<T, U>, not just the value, it's a hierarchical query.

That was the issue Walaa thanks, total case of getting lost in the code.

On a side note, now that it's bringing back results it stopped spitting out SQL, so on to the next problem

This seems like a linqPad issue, isn't it?