I'm trying to simulate in my join multiple conditions. When I run the following code, I get an ORM error saying LP4.SlmName column does not exist. However, if I remove the where clause it works.

Any ideas?

                return from u in query
                       join d in MetaData.AssignedDealers on u.Fips equals d.Fips into dealers
                       from x in dealers.Where(x =>
                           x.Brand == SearchDealerBrand &&
                           x.ProductType == SearchProductType).DefaultIfEmpty()
                       select new UCCRecord
                       {
                           DmConstantKey = u.DmConstantKey,
                           CompanyName = u.CompanyName,
                           FullName = u.FullName,
                           FirstName = u.FirstName,
                           LastName = u.LastName,
                           UccDate = u.UccDate,
                           BuyerSicCode = u.BuyerSicCode,
                           UccStatus = u.UccStatus,
                           SlmName = x.SlmName,
                           CreditRep = x.CreditRep,
                           Brand = x.Brand,
                           ProductType = x.ProductType
                       };

In this code, What is 'query' ? (post the linq for the query variable) Also post the generated sql, and the full exception message and stack trace. It's recommended to download the latest runtime library version.

daelmo wrote:

In this code, What is 'query' ? (post the linq for the query variable) Also post the generated sql, and the full exception message and stack trace. It's recommended to download the latest runtime library version.

Here is the query:

            var query = MetaData.Ucc
                .Where(Predicate)
                .Where(x => x.DoNotContact != DoNotContact.Yes || x.DoNotContact == DoNotContact.Empty)
                .OrderBy(SortExpressions, x => x.DmConstantKey);

I cannot post the generated sql, it does not get that far.

stack trace:

System.Exception: Unhandled Error: ---> DotNetNuke.Services.Exceptions.PageLoadException: An exception was caught during the execution of a retrieval query: Unknown column 'LPLA_4.SLM_NAME' in 'field list'. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---> SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: Unknown column 'LPLA_4.SLM_NAME' in 'field list'. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---> Devart.Data.MySql.MySqlException: Unknown column 'LPLA_4.SLM_NAME' in 'field list' at Devart.Data.MySql.y.p() at Devart.Data.MySql.y.c() at Devart.Data.MySql.an.a(x[]& A_0, Int32& A_1) at Devart.Data.MySql.an.a(Byte[] A_0, Int32 A_1, Boolean A_2) at Devart.Data.MySql.f.e() at Devart.Data.MySql.f.o() at Devart.Data.MySql.MySqlCommand.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords) at Devart.Common.DbCommandBase.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords, Boolean nonQuery) at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery) at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) --- End of inner exception stack trace --- at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Dictionary`2 typeConvertersToRun) at 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) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at CNH.Core.PagedDataSource`2.GetData() in C:\Development\4.0\CNH_CDC\src\CNH.Core\PagedDataSource.cs:line 47 at CNH.Web.Views.Reporting.UCCPresenter.ExportAssignedDealers() in C:\Development\4.0\CNH_CDC\src\Website\DesktopModules\CNH.Web\Views\Reporting\UCCPresenter.cs:line 46 at CNH.Web.Views.Reporting.UCC.btnExportAssignedDealers_Click(Object sender, EventArgs e) in C:\Development\4.0\CNH_CDC\src\Website\DesktopModules\CNH.Web\Views\Reporting\UCC.ascx.cs:line 81 at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) --- End of inner exception stack trace --- at DotNetNuke.Services.Exceptions.Exceptions.ProcessPageLoadException(Exception exc, String URL) at DotNetNuke.Framework.PageBase.OnError(EventArgs e) at System.Web.UI.Page.HandleError(Exception e) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest() at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context) at System.Web.UI.Page.ProcessRequest(HttpContext context) at ASP.default_aspx.ProcessRequest(HttpContext context) at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) 

I am not able to upgrade right from with 2.6.

DefaultIfEmpty is most likely the cause of this problem. As it's hard to implement that nasty method, it might be 2.6 (3.0 does a better job) crashes at some point.

To get around that, could you move the where from: from x in dealers.Where(x => x.Brand == SearchDealerBrand && x.ProductType == SearchProductType).DefaultIfEmpty()

to the side in the join? Or to a place AFTER the DefaultIfEmpty() ? (as it doesn't matter, the where isn't going to end up in an ON clause)

Otis wrote:

DefaultIfEmpty is most likely the cause of this problem. As it's hard to implement that nasty method, it might be 2.6 (3.0 does a better job) crashes at some point.

To get around that, could you move the where from: from x in dealers.Where(x => x.Brand == SearchDealerBrand && x.ProductType == SearchProductType).DefaultIfEmpty()

to the side in the join? Or to a place AFTER the DefaultIfEmpty() ? (as it doesn't matter, the where isn't going to end up in an ON clause)

Do you/could you show me the syntax to move it to the Join? I've been trying but no look (syntax errors).

return from u in query
                     join d in MetaData.AssignedDealers on u.Fips equals d.Fips into dealers
                     from x in dealers.DefaultIfEmpty()

                     where x.Brand == SearchDealerBrand &&
                         && x.ProductType == SearchProductType

                     select new UCCRecord
                     {
                         DmConstantKey = u.DmConstantKey,
                         CompanyName = u.CompanyName,
                         FullName = u.FullName,
                         FirstName = u.FirstName,
                         LastName = u.LastName,
                         UccDate = u.UccDate,
                         BuyerSicCode = u.BuyerSicCode,
                         UccStatus = u.UccStatus,
                         SlmName = x.SlmName,
                         CreditRep = x.CreditRep,
                         Brand = x.Brand,
                         ProductType = x.ProductType
                     };

daelmo wrote:

return from u in query
                     join d in MetaData.AssignedDealers on u.Fips equals d.Fips into dealers
                     from x in dealers.DefaultIfEmpty()

                     where x.Brand == SearchDealerBrand &&
                         && x.ProductType == SearchProductType

                     select new UCCRecord
                     {
                         DmConstantKey = u.DmConstantKey,
                         CompanyName = u.CompanyName,
                         FullName = u.FullName,
                         FirstName = u.FirstName,
                         LastName = u.LastName,
                         UccDate = u.UccDate,
                         BuyerSicCode = u.BuyerSicCode,
                         UccStatus = u.UccStatus,
                         SlmName = x.SlmName,
                         CreditRep = x.CreditRep,
                         Brand = x.Brand,
                         ProductType = x.ProductType
                     };

Do you know how to add it to the join clause (or is that what frans was saying is not possible)?

Do you mean the 'where' clause inside the join predicate? Did you try this?:

var q1 = from d in MetaData.AssignedDealers 
            where d.Brand == SearchDealerBrand && d.ProductType == SearchProductType
            select d;

return from u in query
                     join d in q1 on u.Fips equals d.Fips into dealers
                     from x in dealers.DefaultIfEmpty()

                     select new UCCRecord
                     {
                         DmConstantKey = u.DmConstantKey,
                         CompanyName = u.CompanyName,
                         FullName = u.FullName,
                         FirstName = u.FirstName,
                         LastName = u.LastName,
                         UccDate = u.UccDate,
                         BuyerSicCode = u.BuyerSicCode,
                         UccStatus = u.UccStatus,
                         SlmName = x.SlmName,
                         CreditRep = x.CreditRep,
                         Brand = x.Brand,
                         ProductType = x.ProductType
                     };

daelmo wrote:

Do you mean the 'where' clause inside the join predicate? Did you try this?:

var q1 = from d in MetaData.AssignedDealers 
            where d.Brand == SearchDealerBrand && d.ProductType == SearchProductType
            select d;

return from u in query
                     join d in q1 on u.Fips equals d.Fips into dealers
                     from x in dealers.DefaultIfEmpty()

                     select new UCCRecord
                     {
                         DmConstantKey = u.DmConstantKey,
                         CompanyName = u.CompanyName,
                         FullName = u.FullName,
                         FirstName = u.FirstName,
                         LastName = u.LastName,
                         UccDate = u.UccDate,
                         BuyerSicCode = u.BuyerSicCode,
                         UccStatus = u.UccStatus,
                         SlmName = x.SlmName,
                         CreditRep = x.CreditRep,
                         Brand = x.Brand,
                         ProductType = x.ProductType
                     };

How to add multiple conditions on a join, for example here:

join d in q1 on u.Fips equals d.Fips into dealers

In linq you can't. This has to be done with a where clause.