Hi,

I am having problems with a Linq expression. This may very well be due to my lack of understanding about Linq, but I don't see why LLBLGen should have a problem executing it:

var matchingAnswerSets = from s in metaData.AnswerSet
                         join st in metaData.Staff on s.UserId equals st.Id
                         select new AnswerSet(s.Id, s.Questionnaire.Name, s.StartTime, s.OutletId, s.Outlet.Name, s.Outlet.Address1, s.Outlet.Address2, s.Outlet.Address3, s.Outlet.Address4, s.Outlet.Postcode, s.Outlet.Pos.Name, st.FullName, false);

return matchingMotorcycleAnswerSets.ToArray();

When run, I get the following exception:

Booleans are not supported in MySql databases.
   at SD.LLBLGen.Pro.DQE.MySql.MySqlSpecificCreator.CreateParameter(String name, ParameterDirection direction, Object value) in F:\Source code\LLBLGen Pro\2.6\Sourcecode\RuntimeLibraries\Net2.x\MySqlDQE\MySqlSpecificCreator.cs:line 144
   at SD.LLBLGen.Pro.ORMSupportClasses.DbFunctionCall.CreateParameterFragments(Int32& uniqueMarker, Boolean inHavingClause) in F:\Source code\LLBLGen Pro\2.6\Sourcecode\RuntimeLibraries\Net2.x\ORMSupportClasses\Expressions\DbFunctionCall.cs:line 226
   at SD.LLBLGen.Pro.ORMSupportClasses.DbFunctionCall.ToQueryText(Int32& uniqueMarker, Boolean inHavingClause) in F:\Source code\LLBLGen Pro\2.6\Sourcecode\RuntimeLibraries\Net2.x\ORMSupportClasses\Expressions\DbFunctionCall.cs:line 151
   at SD.LLBLGen.Pro.ORMSupportClasses.DbFunctionCall.SD.LLBLGen.Pro.ORMSupportClasses.IExpression.ToQueryText(Int32& uniqueMarker, Boolean inHavingClause) in F:\Source code\LLBLGen Pro\2.6\Sourcecode\RuntimeLibraries\Net2.x\ORMSupportClasses\Expressions\DbFunctionCall.cs:line 321
   at SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.ConvertFieldToRawName(IEntityFieldCore fieldCore, IFieldPersistenceInfo persistenceInfo, String fieldName, String objectAlias, Int32& uniqueMarker, Boolean applyAggregateFunction) in F:\Source code\LLBLGen Pro\2.6\Sourcecode\RuntimeLibraries\Net2.x\ORMSupportClasses\Persistence\DbSpecificCreatorBase.cs:line 775
   at SD.LLBLGen.Pro.DQE.MySql.MySqlSpecificCreator.CreateFieldName(IEntityFieldCore fieldCore, IFieldPersistenceInfo persistenceInfo, String fieldName, String objectAlias, Int32& uniqueMarker, Boolean applyAggregateFunction) in F:\Source code\LLBLGen Pro\2.6\Sourcecode\RuntimeLibraries\Net2.x\MySqlDQE\MySqlSpecificCreator.cs:line 238
   at SD.LLBLGen.Pro.DQE.MySql.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified) in F:\Source code\LLBLGen Pro\2.6\Sourcecode\RuntimeLibraries\Net2.x\MySqlDQE\DynamicQueryEngine.cs:line 485
   at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause) in F:\Source code\LLBLGen Pro\2.6\Sourcecode\RuntimeLibraries\Net2.x\ORMSupportClasses\Persistence\DynamicQueryEngineBase.cs:line 900
   at SD.LLBLGen.Pro.DQE.MySql.DynamicQueryEngine.CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) in F:\Source code\LLBLGen Pro\2.6\Sourcecode\RuntimeLibraries\Net2.x\MySqlDQE\DynamicQueryEngine.cs:line 631
   at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) in F:\Source code\LLBLGen Pro\2.6\Sourcecode\RuntimeLibraries\Net2.x\ORMSupportClasses\Persistence\DynamicQueryEngineBase.cs:line 1004
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) in F:\Source code\LLBLGen Pro\2.6\Sourcecode\RuntimeLibraries\Net2.x\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 4156
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateQueryFromElements(IEntityFields2 fieldCollectionToFetch, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize, IFieldPersistenceInfo[]& persistenceInfo, IRetrievalQuery& selectQuery) in F:\Source code\LLBLGen Pro\2.6\Sourcecode\RuntimeLibraries\Net2.x\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 4355
   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) in F:\Source code\LLBLGen Pro\2.6\Sourcecode\RuntimeLibraries\Net2.x\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 1608
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute) in F:\Source code\LLBLGen Pro\2.6\Sourcecode\RuntimeLibraries\Net2.x\LinqSupportClasses\LLBLGenProProvider2.cs:line 180
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) in F:\Source code\LLBLGen Pro\2.6\Sourcecode\RuntimeLibraries\Net2.x\LinqSupportClasses\LLBLGenProProviderBase.cs:line 258
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) in F:\Source code\LLBLGen Pro\2.6\Sourcecode\RuntimeLibraries\Net2.x\LinqSupportClasses\LLBLGenProProviderBase.cs:line 93
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) in F:\Source code\LLBLGen Pro\2.6\Sourcecode\RuntimeLibraries\Net2.x\LinqSupportClasses\LLBLGenProProviderBase.cs:line 705
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute() in F:\Source code\LLBLGen Pro\2.6\Sourcecode\RuntimeLibraries\Net2.x\LinqSupportClasses\LLBLGenProQuery.cs:line 86
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() in F:\Source code\LLBLGen Pro\2.6\Sourcecode\RuntimeLibraries\Net2.x\LinqSupportClasses\LLBLGenProQuery.cs:line 141
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at CallRecordingsService.GetAnswerSetsWithoutRecordings(String username, String passwordHash, DateTime minTime, DateTime maxTime, Nullable`1 posID, Nullable`1 outletID, Nullable`1 staffCallerID) in f:\Source code\EncircleDatabase\CallRecordingsWebService\App_Code\CallRecordingsService.cs:line 250

When I remove the last parameter from the AnswerSet constructor, so I'm not using a boolean, it works as expected. I understand that MySql doesn't support boolean parameters, but I don't really see why a MySql parameter is being created for this boolean 'false' since it is essential just a constant value I'm passing to an object's constructor.

If this is a limitation of Linq, then fair enough. It just seems odd! Apologies if this has been covered before in the forums, but your search page is not currently working (there's an ASP.net error page when I try to search).

I'm using LLBLGen 2.6 Final ( Oct 6th 2008 ), adapter, .Net 3.5

select new AnswerSet(s.Id, s.Questionnaire.Name, s.StartTime, s.OutletId, s.Outlet.Name, s.Outlet.Address1, s.Outlet.Address2, s.Outlet.Address3, s.Outlet.Address4, s.Outlet.Postcode, s.Outlet.Pos.Name, st.FullName, false);

As this should construct the select fields list, may I ask, why do you pass the last false?

Also why using the object CTor, instead of:

select new (s.Id, s.Questionnaire.Name, s.StartTime, ...);

return matchingMotorcycleAnswerSets.ToArray();

btw, does the above line have any relevance?

(EDIT)

but your search page is not currently working (there's an ASP.net error page when I try to search).

The search page is working from me, if it's still not working with you or ever happens again, please report it to us with the query you ran, thanks.

The query is handled by a projection. This projection receives a set of values and then projects them onto classes, etc. As the required # of values is set to a given number, including the 'false' parameter, the value is in the resultset. This is done by passing a constant parameter, so the query becomes something like:

Generated Sql query: 
    Query: SELECT [LPLA_1].[ProductID] AS [ProdId], [LPLA_1].[ProductName] AS [Name], @LO01 AS [LPFA_2] FROM [Northwind].[dbo].[Products] [LPLA_1] 
    Parameter: @LO01 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False.

The Linq provider doesn't know which database is targeted nor does it know the limitations, it just generates the projection with the constant. The DQE for mysql then runs into a problem because a boolean value requires a parameter and it can't create such parameter.

There are several workarounds in this case: - create a CTor overload which doesn't require you to pass constants. - use a trick, to create a boolean value using the database value. E.g.: var matchingAnswerSets = from s in metaData.AnswerSet join st in metaData.Staff on s.UserId equals st.Id select new AnswerSet(s.Id, s.Questionnaire.Name, s.StartTime, s.OutletId, s.Outlet.Name, s.Outlet.Address1, s.Outlet.Address2, s.Outlet.Address3, s.Outlet.Address4, s.Outlet.Postcode, s.Outlet.Pos.Name, st.FullName, (s.Id<0));

This creates a CASE statement for the s.id < 0 (which is always false as id is the pk I assume).

Personally I'd go for option one.

(about the search page: did you change the sort parameters? )

Thanks for the replies guys. It's no problem to work around. I just suspected it may be a bug or something needing your attention - your explanation has cleared it up.

Regarding the search - Searching for 'a not b' (no quotes) throws an error, as does my original query, 'Booleans are not supported in MySql databases'.

Edit: ps. no I didn't change the search parameters.

Thanks for the search parameters, will look into those

The 'not' causes a syntax error for the query. (a AND not AND b, which should have been a AND not b) Looking into fixing this.

Fixed. 'not' is a keyword for full text search so specifying: Booleans are not supported in MySql databases won't give any results. "Booleans are not supported in MySql databases" will give this thread.