Edit: I just updated to the latest builds and I am still have the issue: LLBLGen.Pro.LinqSupportClasses = 2.6.8.710

I have successfully built this query and have used it in non-LLBL Linq. Figured it would transfer over to the LLBL Linq... however I keep getting and error. I know I am missing something, just not sure where to look.

The exception thrown: An exception was caught during the execution of a retrieval query: Failed to convert parameter value from a ScalarQueryExpression to a DateTime

LLBL Linq

var user = (from l in db.ClientUserLogin
                        join u in db.ClientUser on l.UserLoginID equals u.UserLoginID
                        join s in db.ClientUserStatus on u.UserID equals s.UserId
                        where l.Username == userName
                        where l.Password == password
                        where u.ClientID == clientId && u.Active == true
                        where s.Entered == 
                        (
                             from s0 in db.ClientUserStatus
                             where s0.UserId == s.UserId
                             select s0
                         ).Max(o => o.Entered)
                        select new BusinessEntities.User.Information {
                            UserId = u.UserID,
                            LoginId = l.UserLoginID,
                            Status = s.StatusCode.EnumParse<BusinessEntities.User.UserStatus>(true)
                        }
                        ).First<BusinessEntities.User.Information>();

Stack Trace

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled by user code
  Message="An exception was caught during the execution of a retrieval query: Failed to convert parameter value from a ScalarQueryExpression to a DateTime.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."
  Source="SD.LLBLGen.Pro.ORMSupportClasses.NET20"
  RuntimeBuild="06242008"
  RuntimeVersion="2.6.0.0"
  QueryExecuted="\r\n\tQuery: SELECT TOP 1 [LPA_L2].[UserID] AS [UserId], [LPA_L1].[UserLoginID] AS [LoginId], [LPA_L3].[StatusCode] AS [Status] FROM (( [FlexBase].[dbo].[ClientUserLogin] [LPA_L1]  INNER JOIN [FlexBase].[dbo].[ClientUser] [LPA_L2]  ON  [LPA_L1].[UserLoginID] = [LPA_L2].[UserLoginID]) INNER JOIN [FlexBase].[dbo].[ClientUserStatus] [LPA_L3]  ON  [LPA_L2].[UserID] = [LPA_L3].[UserId]) WHERE ( ( ( ( ( ( ( ( [LPA_L1].[Username] = @Username1)) AND ( [LPA_L1].[Password] = @Password2)) AND ( ( [LPA_L2].[ClientID] = @ClientID3) AND ( [LPA_L2].[Active] = @Active4))) AND ( [LPA_L3].[Entered] = @Entered5)))))\r\n\tParameter: @Username1 : AnsiString. Length: 150. Precision: 0. Scale: 0. Direction: Input. Value: \"username\".\r\n\tParameter: @Password2 : AnsiString. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: \"xxxxxx\".\r\n\tParameter: @ClientID3 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 911111.\r\n\tParameter: @Active4 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: True.\r\n\tParameter: @Entered5 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: SD.LLBLGen.Pro.ORMSupportClasses.ScalarQueryExpression.\r\n"
  StackTrace:
       at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
       at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsDataReader(ITransaction transactionToUse, IRetrievalQuery queryToExecute, CommandBehavior readerBehavior)
       at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsProjection(List`1 valueProjectors, IGeneralDataProjector projector, ITransaction transactionToUse, IRetrievalQuery queryToExecute, Dictionary`2 typeConvertersToRun)
       at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsProjection(List`1 valueProjectors, IGeneralDataProjector projector, ITransaction transactionToUse, IEntityFields fields, IPredicateExpression filter, IRelationCollection relations, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize)
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider.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[TResult](Expression expression)
       at System.Linq.Queryable.First[TSource](IQueryable`1 source)
       at WebConnect.DataAccess.UserRepository.Authenticate(String userName, String password, Int32 clientId, String ipAddress, Guid& id) in C:\Projects\WebConnect.DataAccess\UserRepository.cs:line 38
       at WebConnect.BusinessLogic.Managers.AuthenticationManager.Authenticate(String userName, String password, Int32 clientId, String ipAddress, Guid& userId) in C:\Projects\WebConnect\Source\Business Logic\WebConnect.BusinessLogic\Managers\AuthenticationManager.cs:line 28
       at WebConnect.Login.btnLogin_Click(Object sender, EventArgs e) in C:\Projects\WebConnect\Source\Host\WebConnect\Login.aspx.cs:line 22
       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)
  InnerException: System.InvalidCastException
       Message="Failed to convert parameter value from a ScalarQueryExpression to a DateTime."
       Source="System.Data"
       StackTrace:
            at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)
            at System.Data.SqlClient.SqlParameter.GetCoercedValue()
            at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
            at System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters)
            at System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)
            at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
            at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
            at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
            at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
            at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
            at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
            at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
       InnerException: System.InvalidCastException
            Message="Object must implement IConvertible."
            Source="mscorlib"
            StackTrace:
                 at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
                 at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)
            InnerException: 

LLBLGen Pro Linq DLL is version 2.6.08.0616

Please let me know if you need additional information.

Reproduced

var q = from c in metaData.Customer
        join o in metaData.Order on c.CustomerId equals o.CustomerId
        where o.EmployeeId == (from e in metaData.Employee
                              where e.EmployeeId == o.EmployeeId
                              select e).Max(e => e.EmployeeId)
        select o;

Looking into it. The error is caused by the fact that the scalarqueryexpression isn't converted to a normal scalar query for LLBLGen Pro's API, so it's seen as an object, a value, and is then passed as-is, of course this leads to problems.

(edit) Fixed in next build.

Thanks Otis for fixing this. Any idea when the new build will be out? I have a project hinging on this very logic.

jgombala wrote:

Thanks Otis for fixing this. Any idea when the new build will be out? I have a project hinging on this very logic.

See attached dll. The final build will be later today.

Worked like a charm. Thanks!