Hello,

Using build 3.0.10.0927, I am trying to do a query exactly like this:

http://stackoverflow.com/questions/1122942/linq-to-sql-left-outer-join-with-multiple-join-conditions

My solution follows the one posted by dahlbyk. I tried both the .Where clause and the subquery examples but I get an error:

The multi-part identifier "LPLA_1.ClientPortalId" could not be bound.
The multi-part identifier "LPLA_1.RowId" could not be bound.
The multi-part identifier "LPLA_1.DataLabel" could not be bound.
The multi-part identifier "LPLA_1.MetaDataTypeId" could not be bound.
The multi-part identifier "LPLA_1.IsRequired" could not be bound. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: The multi-part identifier "LPLA_1.ClientPortalId" could not be bound.
The multi-part identifier "LPLA_1.RowId" could not be bound.
The multi-part identifier "LPLA_1.DataLabel" could not be bound.
The multi-part identifier "LPLA_1.MetaDataTypeId" could not be bound.
The multi-part identifier "LPLA_1.IsRequired" could not be bound.

Source Error: 


Line 424:       internal IList<CustomDataModel> GetAllAssetCustomData(int assetId, int clientPortalId)
Line 425:       {
Line 426:           var q = (from metaData in Repository.For<AssetCustomMetaDataEntity>()
Line 427:                    join customData in Repository.For<AssetCustomDataEntity>() on metaData.RowId equals customData.MetaDataId into customDataJoin
Line 428:                    from con in


Source File: C:\Users\rick.MATRIX6\Documents\Visual Studio 2010\Projects\GreatAmerican\gagroup\CAP\trunk\GA.CAP.Website\Services\ClientRepository.cs    Line: 426 

Stack Trace: 


[SqlException (0x80131904): The multi-part identifier "LPLA_1.ClientPortalId" could not be bound.
The multi-part identifier "LPLA_1.RowId" could not be bound.
The multi-part identifier "LPLA_1.DataLabel" could not be bound.
The multi-part identifier "LPLA_1.MetaDataTypeId" could not be bound.
The multi-part identifier "LPLA_1.IsRequired" could not be bound.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +2030802
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5009584
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
   System.Data.SqlClient.SqlDataReader.get_MetaData() +86
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +311
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
   System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10
   SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:94

[ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The multi-part identifier "LPLA_1.ClientPortalId" could not be bound.
The multi-part identifier "LPLA_1.RowId" could not be bound.
The multi-part identifier "LPLA_1.DataLabel" could not be bound.
The multi-part identifier "LPLA_1.MetaDataTypeId" could not be bound.
The multi-part identifier "LPLA_1.IsRequired" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.]
   SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:132
   SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:1576
   SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Dictionary`2 typeConvertersToRun) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:1713
   SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IEntityFields2 fields, IRelationPredicateBucket filter, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:1675
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProvider2.cs:178
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProviderBase.cs:264
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProviderBase.cs:93
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProviderBase.cs:696
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProQuery.cs:162
   System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +315
   System.Linq.Enumerable.ToList(IEnumerable`1 source) +58
   GA.CAP.Website.Services.ClientRepository.GetAllAssetCustomData(Int32 assetId, Int32 clientPortalId) in C:\Users\rick.MATRIX6\Documents\Visual Studio 2010\Projects\GreatAmerican\gagroup\CAP\trunk\GA.CAP.Website\Services\ClientRepository.cs:426
   GA.CAP.Website.Controllers.AdminAssetsController.EditAssetDetails(Nullable`1 id) in C:\Users\rick.MATRIX6\Documents\Visual Studio 2010\Projects\GreatAmerican\gagroup\CAP\trunk\GA.CAP.Website\Controllers\AdminAssetsController.cs:591
   lambda_method(Closure , ControllerBase , Object[] ) +108
   System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +51
   System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) +409
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +52
   System.Web.Mvc.<>c__DisplayClassd.<InvokeActionMethodWithFilters>b__a() +127
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation) +436
   System.Web.Mvc.<>c__DisplayClassf.<InvokeActionMethodWithFilters>b__c() +61
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation) +436
   System.Web.Mvc.<>c__DisplayClassf.<InvokeActionMethodWithFilters>b__c() +61
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation) +436
   System.Web.Mvc.<>c__DisplayClassf.<InvokeActionMethodWithFilters>b__c() +61
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList`1 filters, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +305
   System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName) +830
   System.Web.Mvc.Controller.ExecuteCore() +136
   System.Web.Mvc.ControllerBase.Execute(RequestContext requestContext) +111
   System.Web.Mvc.ControllerBase.System.Web.Mvc.IController.Execute(RequestContext requestContext) +39
   System.Web.Mvc.<>c__DisplayClass8.<BeginProcessRequest>b__4() +65
   System.Web.Mvc.Async.<>c__DisplayClass1.<MakeVoidDelegate>b__0() +44
   System.Web.Mvc.Async.<>c__DisplayClass8`1.<BeginSynchronous>b__7(IAsyncResult _) +42
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +141
   System.Web.Mvc.Async.AsyncResultWrapper.End(IAsyncResult asyncResult, Object tag) +54
   System.Web.Mvc.Async.AsyncResultWrapper.End(IAsyncResult asyncResult, Object tag) +40
   System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +52
   System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +38
   System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +8841105
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +184

My query:

            var q = (from metaData in Repository.For<AssetCustomMetaDataEntity>()
                     join customData in Repository.For<AssetCustomDataEntity>() on metaData.RowId equals customData.MetaDataId into customDataJoin
                     from con in
                         (from c in customDataJoin
                          where c.AssetId == assetId
                          select c).DefaultIfEmpty()
                     where metaData.ClientPortalId == clientPortalId
                     select new CustomDataModel
                     {
                         Data = con == null ? "" : con.Data,
                         RowId = con == null ? 0 : con.RowId,
                         MetaDataRowId = metaData.RowId,
                         DataLabel = metaData.DataLabel,
                         MetaDataTypeId = metaData.MetaDataTypeId,
                         IsRequired = metaData.IsRequired,
                     }).ToList();

What am I doing wrong?

Thanks,

Rick

What about this?

var q = (from metaData in Repository.For<AssetCustomMetaDataEntity>()
     join customData in 
          (
               from c in Repository.For<AssetCustomDataEntity>()
               where c.AssetId == assetId
               select c
          ) on metaData.RowId equals customData.MetaDataId into customDataJoin

     from v in customDataJoin.DefaultIfEmpty()
     where metaData.ClientPortalId == clientPortalId
    
     select new CustomDataModel
     {
          Data = con == null ? "" : v.Data,
          RowId = con == null ? 0 : v.RowId,
          MetaDataRowId = metaData.RowId,
          DataLabel = metaData.DataLabel,
          MetaDataTypeId = metaData.MetaDataTypeId,
          IsRequired = metaData.IsRequired,
     }).ToList();

Also, please update to the latest runtime library version. If you still have problems please post the generated sql, if any.

That seems to work. I had to tweak the projection a bit to get it to compile.

var q = (from metaData in Repository.For<AssetCustomMetaDataEntity>()
                     join customData in
                         (
                             from c in Repository.For<AssetCustomDataEntity>()
                             where c.AssetId == assetId
                             select c
                         ) on metaData.RowId equals customData.MetaDataId into customDataJoin

                     from v in customDataJoin.DefaultIfEmpty()
                     where metaData.ClientPortalId == clientPortalId

                     select new CustomDataModel
                     {
                         Data = v == null ? "" : v.Data,
                         RowId = v == null ? 0 : v.RowId,
                         MetaDataRowId = metaData.RowId,
                         DataLabel = metaData.DataLabel,
                         MetaDataTypeId = metaData.MetaDataTypeId,
                         IsRequired = metaData.IsRequired,
                     }).ToList();

Why didn't my original one work as expected?

Rick

It likely failed due to a bug somewhere. From (query).DefaultIfEmpty() is a true pain, and it could go wrong in some cases. We have a couple of them identified, but it's unclear how to fix them unfortunately. The logic to properly handle a join in all cases is extremely complex, as the tree has to be transformed a lot of cases. For example where clauses etc. have to be moved out of the join left/right side, which causes aliases to change, and as defaultifempty isn't a new join but simply affects the join defined by the from clause it is defined with, you have to track back that part, however it's in a different part of the tree (although not visible in the linq query)...

We have planned to rewrite our visitor over join / selectmany (the from clause after the join defines a select many) / groupjoin (the join into defines a groupjoin, which has no SQL equivalent... ) subtrees so it might fix the few queries we have so far which fail (5 or so), but it's a complex affair and therefore very time consuming. As linq already costed us 10 months full time development work, we try to focus on other things first as in 99% of the cases, it works properly.

Let me know if you need any more information from me to eventually fix this. Otherwise I'll consider this issue closed.

Thank you for your outstanding support as always!

Rick

Please follow this thread, it seems the same issue: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=19053

We'll at least have a look and see whether we can do something about it without rewriting big parts of the linq provider