First to some necessary information:

• I am using the latest release of LLBL Gen Pro 2.6 (September 12th)
• The query yields no compiletime-errors, but a runtime error (InvalidCastException), so no DQE output is available

When trying to execute the query, i get an Exception with the (german) message

"Das Objekt des Typs System.Linq.Expressions.ConstantExpression kann nicht in Typ SD.LLBLGen.Pro.LinqSupportClasses.ExpressionClasses.SetExpression umgewandelt werden."

which means that a cast from System.Linq.Expressions.ConstantExpression to SD.LLBLGen.Pro.LinqSupportClasses.ExpressionClasses.SetExpression has failed.

When i edit the enclosed linq query to fit in LINQPad (so it uses the EntityFramework), there is no such problem (obviously).

I must admit i am pretty new to Linq, but i like it a lot, any input (also how the query can be made better) is highly appreciated.

• initial expression to evaluate:

value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[XXX.DAL.EntityClasses.ReasonEntity]). GroupJoin(value(System.Linq.Enumerable+<SelectManyIterator>d__31`3[<>f__AnonymousType2`2[XXX.DAL.EntityClasses. NonPresentTimeHeaderEntity,System.Collections.Generic.IEnumerable`1[ XXX.DAL.EntityClasses.NonPresentTimeEntity]], XXX.DAL.EntityClasses.NonPresentTimeEntity,XXX.DAL.EntityClasses.NonPresentTimeEntity]), reason => Convert(reason.Id), time => time.ReasonId, (reason, reasonJoin) => new <>f__AnonymousType3`2(reason = reason, reasonJoin = reasonJoin)).SelectMany(<>h__TransparentIdentifier0 => <>h__TransparentIdentifier0.reasonJoin.DefaultIfEmpty(), (<>h__TransparentIdentifier0, joinedReason) => new NonPresentTimeDisplayItem() {ReasonID = <>h__TransparentIdentifier0.reason.Id, Reason = <>h__TransparentIdentifier0.reason.Reason, HeaderID = (joinedReason.HeaderId ?? -1), TimeID = joinedReason.Id, Notes = joinedReason.Notes, DateStart = joinedReason.DateStart, DateEnd = joinedReason.DateEnd})

• Stack Trace

   bei SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleJoinSide(Expression side, Expression sideSelector, SetExpression& handledSide, LambdaExpression& handledSideSelector)
   bei SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallGroupJoin(MethodCallExpression expressionToHandle)
   bei SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleQueryableExtensionMethod(MethodCallExpression expressionToHandle)
   bei SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallPerType(MethodCallExpression expressionToHandle, Type declaringType)
   bei SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression(MethodCallExpression expressionToHandle)
   bei SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   bei SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle)
   bei SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallSelectMany(MethodCallExpression expressionToHandle)
   bei SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleQueryableExtensionMethod(MethodCallExpression expressionToHandle)
   bei SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallPerType(MethodCallExpression expressionToHandle, Type declaringType)
   bei SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression(MethodCallExpression expressionToHandle)
   bei SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   bei SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle)
   bei SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.HandleExpressionTree(Expression expression)
   bei SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
   bei SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
   bei SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute()
   bei SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.SD.LLBLGen.Pro.LinqSupportClasses.ILLBLGenProQuery.Execute[TResult]()
   bei Abwesenheit.DataManagement.Services.NonPresentTimeService.GetTimeDisplay(Int32 headerId) in D:\coding\Abwesenheit\DataManagement\Services\NonPresentTimeService.cs:Zeile 67.
   bei Abwesenheit.DataManagement.Services.NonPresentTimeService.GetTimeDisplay() in D:\coding\Abwesenheit\DataManagement\Services\NonPresentTimeService.cs:Zeile 19.
   bei Abwesenheit.DataManagement.DataManagementController.GetTimeData() in D:\coding\Abwesenheit\DataManagement\DataManagementController.cs:Zeile 29.
   bei Abwesenheit.DataManagement.Views.DefaultViewPresenter.OnViewLoaded() in D:\coding\Abwesenheit\DataManagement\Views\DefaultViewPresenter.cs:Zeile 18.
   bei Abwesenheit.DataManagement.Views.DataManagementDefault.Page_Load(Object sender, EventArgs e) in d:\coding\Abwesenheit\WebSites\Abwesenheit\DataManagement\Default.aspx.cs:Zeile 20.
   bei System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
   bei System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
   bei System.Web.UI.Control.OnLoad(EventArgs e)
   bei System.Web.UI.Control.LoadRecursive()
   bei System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

• full linq query

var times = from reason in metaData.Reason
                            join time in

                                (
                                    from timeHeader in
                                        (
                                            from timeHeader in
                                                (
                                                    from timeHeader in metaData.NonPresentTimeHeader.Where(filter)
                                                    join userDetail in metaData.UserDetail on timeHeader.UserId equals
                                                        userDetail.Id into userJoin
                                                    from user in userJoin.DefaultIfEmpty()
                                                    select timeHeader
                                                )
                                            join userDetail in metaData.UserDetail on timeHeader.ApprovedFromId equals
                                                userDetail.Id into approverJoin
                                            from approver in approverJoin.DefaultIfEmpty()
                                            select timeHeader
                                        )
                                    join time in metaData.NonPresentTime on timeHeader.Id equals time.HeaderId into
                                        timeJoin
                                    from joinedTime in timeJoin.DefaultIfEmpty()
                                    select joinedTime
                                ) on reason.Id equals time.ReasonId into reasonJoin
                            from joinedReason in reasonJoin.DefaultIfEmpty()
                            select new NonPresentTimeDisplayItem
                                       {
                                           ReasonID = reason.Id,
                                           Reason = reason.Reason,
                                           HeaderID = joinedReason.HeaderId ?? -1,
                                           TimeID = joinedReason.Id,
                                           Notes = joinedReason.Notes,
                                           DateStart = joinedReason.DateStart,
                                           DateEnd = joinedReason.DateEnd
                                       };


                return ((ILLBLGenProQuery)times).Execute<ICollection<NonPresentTimeDisplayItem>>();

• there is no inheritance involved in the entities, it is all about master/detail tables

• SQL DDL for involved tables:

BEGIN
CREATE TABLE [dbo].[Reason](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Reason] [varchar](150) NULL,
 CONSTRAINT [PK_tblReason] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END

BEGIN
CREATE TABLE [dbo].[UserDetail](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [varchar](50) NOT NULL,
    [SignaturePicPath] [varchar](200) NULL,
    [FullName] [varchar](100) NULL,
    [EmployeeNr] [varchar](10) NULL,
    [Department] [varchar](20) NULL,
 CONSTRAINT [PK_tblUserDetails] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_tblUserDetails] UNIQUE NONCLUSTERED 
(
    [UserName] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_tblUserDetails_1] UNIQUE NONCLUSTERED 
(
    [EmployeeNr] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END

BEGIN
CREATE TABLE [dbo].[NonPresentTime](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [HeaderID] [int] NULL,
    [ReasonID] [int] NULL,
    [DateStart] [smalldatetime] NULL,
    [DateEnd] [smalldatetime] NULL,
    [Notes] [varchar](250) NULL,
 CONSTRAINT [PK_tblNonPresentTimes] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END

BEGIN
CREATE TABLE [dbo].[NonPresentTimeHeader](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [int] NULL,
    [ApprovedFromID] [int] NULL,
    [EnteredDate] [smalldatetime] NULL,
    [ApprovedDate] [smalldatetime] NULL,
 CONSTRAINT [PK_tblNonPresentTimesHeader] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_NonPresentTime_Reason]') AND parent_object_id = OBJECT_ID(N'[dbo].[NonPresentTime]'))
ALTER TABLE [dbo].[NonPresentTime]  WITH CHECK ADD  CONSTRAINT [FK_NonPresentTime_Reason] FOREIGN KEY([ReasonID])
REFERENCES [dbo].[Reason] ([ID])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblNonPresentTimes_tblNonPresentTimesHeader]') AND parent_object_id = OBJECT_ID(N'[dbo].[NonPresentTime]'))
ALTER TABLE [dbo].[NonPresentTime]  WITH CHECK ADD  CONSTRAINT [FK_tblNonPresentTimes_tblNonPresentTimesHeader] FOREIGN KEY([HeaderID])
REFERENCES [dbo].[NonPresentTimeHeader] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_NonPresentTimeHeader_UserDetail]') AND parent_object_id = OBJECT_ID(N'[dbo].[NonPresentTimeHeader]'))
ALTER TABLE [dbo].[NonPresentTimeHeader]  WITH CHECK ADD  CONSTRAINT [FK_NonPresentTimeHeader_UserDetail] FOREIGN KEY([UserID])
REFERENCES [dbo].[UserDetail] ([ID])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_NonPresentTimeHeader_UserDetail1]') AND parent_object_id = OBJECT_ID(N'[dbo].[NonPresentTimeHeader]'))
ALTER TABLE [dbo].[NonPresentTimeHeader]  WITH CHECK ADD  CONSTRAINT [FK_NonPresentTimeHeader_UserDetail1] FOREIGN KEY([ApprovedFromID])
REFERENCES [dbo].[UserDetail] ([ID])

You're using the latest runtime libraries build? To be sure, please post the runtime library build nr (see guidelines thread in this forum). Your query uses DefaultIfEmpty() and groupjoin stuff, which has seen a couple of revisions.

Also please describe what you want to achieve, as the joins in your query seem overly complicated for what I think you want to achieve.

Otis wrote:

You're using the latest runtime libraries build? To be sure, please post the runtime library build nr (see guidelines thread in this forum). Your query uses DefaultIfEmpty() and groupjoin stuff, which has seen a couple of revisions.

Also please describe what you want to achieve, as the joins in your query seem overly complicated for what I think you want to achieve.

I downloaded the latest build from the website (build nr. 10162008, released 16-okt-2008 ) and replaced the libraries from the 2.6 release - the Exception stayed the same.

What i'm trying to achieve is the following:

There is a number of Resons in the Reason table, which i want to display in any case. If there is a NonPresentTime associated with a reason, i need to join that in and follow the path down to NonPresentTimeHeader and the two UserDetail (User and Approver) and obtain their information as well (all selected into the new object at the end of the statement). The hirarchie is the following:

Reason -- 1:n -- NonPresentTime -- n:1 -- NonPresentTimeHeader -- n:1 -- UserDetail (Twice)

Hope this helps.

Cheers, Alex

Do you want to fetch these in a graph (using prefetchPaths), or in a flat way? Would you please post the desired SQL statement?

Walaa wrote:

Do you want to fetch these in a graph (using prefetchPaths), or in a flat way? Would you please post the desired SQL statement?

I need some of the joined data into objects to display to the user, so i guess i just need the flat data.

I replaced the previous query with a simplified version, which seems to work in the initial case:

query = from reason in metaData.Reason
                                from time in reason.NonPresentTime.DefaultIfEmpty()
                                select new NonPresentTimeDisplayItem
                                {
                                    ReasonID = reason.Id,
                                    Reason = reason.Reason,
                                    HeaderID = time.NonPresentTimeHeader.Id,
                                    TimeID = time.Id,
                                    Notes = time.Notes,
                                    DateStart = time.DateStart,
                                    DateEnd = time.DateEnd
                                };

The only thing that misses now is that if i add a where clause like

where time.NonPresentTimeHeader.Id == headerId

under the second from (so searching for items on a specific header), the outer joins implemented in the first version of the query don't work as expected anymore - i still want all Reasons, no matter if a NonPresentTime is associated with it or not.

The initial SQL Statement was in a stored procedure from a collegue:

SELECT   dbo.Reason.Reason, fn_PresenTimesFilter.ID, fn_PresenTimesFilter.UserID, fn_PresenTimesFilter.DateStart, fn_PresenTimesFilter.DateEnd, 
                      fn_PresenTimesFilter.Notes, fn_PresenTimesFilter.ApprovedFromID, dbo.UserDetail.UserName, UserDetail_1.UserName AS ApprovedFrom, 
                      fn_PresenTimesFilter.DetID, dbo.Reason.ID AS ReasonID, dbo.UserDetail.FullName, dbo.UserDetail.EmployeeNr, 
                      UserDetail_1.FullName AS ApprovedFromFullName, UserDetail_1.EmployeeNr AS ApprovedFromEmployeeID, 
                      fn_PresenTimesFilter.EnteredDate, fn_PresenTimesFilter.ApprovedDate, UserDetail_1.Department AS ApprovedDep
FROM         dbo.fn_PresenTimesFilter(@id) fn_PresenTimesFilter LEFT OUTER JOIN
                      dbo.UserDetail UserDetail_1 ON fn_PresenTimesFilter.ApprovedFromID = UserDetail_1.ID LEFT OUTER JOIN
                      dbo.UserDetail ON fn_PresenTimesFilter.UserID = dbo.UserDetail.ID RIGHT OUTER JOIN
                      dbo.Reason ON fn_PresenTimesFilter.ReasonID = dbo.Reason.ID

The corresponding dbo.fn_PresenTimesFilter function is:

SELECT   dbo.NonPresentTimeHeader.ID, dbo.NonPresentTime.ReasonID, dbo.NonPresentTimeHeader.UserID, dbo.NonPresentTime.DateStart, 
                      dbo.NonPresentTime.DateEnd, dbo.NonPresentTime.Notes, dbo.NonPresentTimeHeader.ApprovedFromID, 
                      dbo.NonPresentTimeHeader.EnteredDate, dbo.NonPresentTimeHeader.ApprovedDate, dbo.NonPresentTime.ID AS DetID
FROM         dbo.NonPresentTime RIGHT OUTER JOIN
                      dbo.NonPresentTimeHeader ON dbo.NonPresentTime.HeaderID = dbo.NonPresentTimeHeader.ID
WHERE    (dbo.NonPresentTimeHeader.ID = @id)

Cheers, Alex

By the way, the following is the query that works as expected (in LINQPad, using EntityFramework) - you can change the header.ID to something that doesn't exist to still get all Reasons displayed - and the filled out Reasons if the header does exist.

from reason in Reasons
join time in    

(
    from timeHeader in
        (
        from timeHeader in
            (
                from timeHeader in NonPresentTimeHeaders.Where(header => header.ID==6)
                join userDetail in UserDetails on timeHeader.UserID equals
                userDetail.ID into userJoin
                from user in userJoin.DefaultIfEmpty()
                select timeHeader
            )
            join userDetail in UserDetails on timeHeader.ApprovedFromID equals
            userDetail.ID into approverJoin
            from approver in approverJoin.DefaultIfEmpty()
            select timeHeader
        )
    join time in NonPresentTimes on timeHeader.ID equals time.HeaderID into timeJoin
    from joinedTime in timeJoin.DefaultIfEmpty()
    select joinedTime
) on reason.ID equals time.ReasonID into reasonJoin
from joinedReason in reasonJoin.DefaultIfEmpty()
select new 
{
    ReasonID=reason.ID,
    Reason=reason.Content,
    HeaderID=joinedReason.HeaderID==null?-1:joinedReason.HeaderID,
    TimeID=joinedReason.ID==null?-1:joinedReason.ID,
    Notes=joinedReason.Notes,
    DateStart=joinedReason.DateStart,
    DateEnd=joinedReason.DateEnd
}

That results in the following SQL code:

SELECT [t0].[ID] AS [ReasonID], [t0].[Reason], 
    (CASE 
        WHEN [t4].[HeaderID] IS NULL THEN @p1
        ELSE [t4].[HeaderID]
     END) AS [HeaderID], 
    (CASE 
        WHEN ([t4].[ID]) IS NULL THEN @p2
        ELSE [t4].[ID]
     END) AS [TimeID], [t4].[Notes] AS [Notes], [t4].[DateStart] AS [DateStart], [t4].[DateEnd] AS [DateEnd]
FROM [Reason] AS [t0]
LEFT OUTER JOIN ([NonPresentTimeHeader] AS [t1]
    LEFT OUTER JOIN [UserDetail] AS [t2] ON [t1].[UserID] = ([t2].[ID])
    LEFT OUTER JOIN [UserDetail] AS [t3] ON [t1].[ApprovedFromID] = ([t3].[ID])
    LEFT OUTER JOIN [NonPresentTime] AS [t4] ON ([t1].[ID]) = [t4].[HeaderID]) ON (([t0].[ID]) = [t4].[ReasonID]) AND ([t1].[ID] = @p0)
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [6]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [-1]
-- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [-1]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

If I do:

var q = from reason in metaData.Reason
        join time in metaData.NonPresentTime.Where(npt=>npt.NonPresentTimeHeader.Id==6) on reason.Id equals time.ReasonId into reasonJoin
        from joinedReason in reasonJoin.DefaultIfEmpty()
        select new
        {
            ReasonID = reason.Id,
            Reason = reason.Reason,
            HeaderID = joinedReason.HeaderId ?? -1,
            TimeID = joinedReason.Id,
            Notes = joinedReason.Notes,
            DateStart = joinedReason.DateStart,
            DateEnd = joinedReason.DateEnd
        };

I get the query you want, I think, as the joins with the users are meaningless as the data isn't used.

It's very hard to track down the reason of the failure. When I run your query I get different errors, and as the expression tree is extremely big, it's very hard to debug this, due to the fact that you use a lot of defaultifempty and groupjoin constructs (the join into.. ) which result in a Linq expression tree which has references all over the place (as group joins are defined at place X and referred to by the defaultifempty ... ) We have done a lot of work in this area 2 months ago to rewrite part of the linq provider to have better support for this, though at the same time we learned that this particular construct (DefaultIfEmpty + groupjoin) is so complex (and there's no documentation when what appears in an expression tree... yay!) that debugging a failure takes a lot of time, and is in general pretty much undoable with massive queries like yours.

If the workaround above doesn't cut it, please let us know.

Otis wrote:

If I do:

var q = from reason in metaData.Reason
        join time in metaData.NonPresentTime.Where(npt=>npt.NonPresentTimeHeader.Id==6) on reason.Id equals time.ReasonId into reasonJoin
        from joinedReason in reasonJoin.DefaultIfEmpty()
        select new
        {
            ReasonID = reason.Id,
            Reason = reason.Reason,
            HeaderID = joinedReason.HeaderId ?? -1,
            TimeID = joinedReason.Id,
            Notes = joinedReason.Notes,
            DateStart = joinedReason.DateStart,
            DateEnd = joinedReason.DateEnd
        };

I get the query you want, I think, as the joins with the users are meaningless as the data isn't used.

It's very hard to track down the reason of the failure. When I run your query I get different errors, and as the expression tree is extremely big, it's very hard to debug this, due to the fact that you use a lot of defaultifempty and groupjoin constructs (the join into.. ) which result in a Linq expression tree which has references all over the place (as group joins are defined at place X and referred to by the defaultifempty ... ) We have done a lot of work in this area 2 months ago to rewrite part of the linq provider to have better support for this, though at the same time we learned that this particular construct (DefaultIfEmpty + groupjoin) is so complex (and there's no documentation when what appears in an expression tree... yay!) that debugging a failure takes a lot of time, and is in general pretty much undoable with massive queries like yours.

If the workaround above doesn't cut it, please let us know.

Frans,

Thanks a lot for this - figures i don't need to join the users as i don't need them. This code works perfectly and delivers what i need - thanks!

Cheers, Alex

Still, I think there's an issue in our code somewhere, I made your query much smaller and I ran into a problem with tree rewriting. I'll investigate that further.