Hi,

I have a table structure like this:

VehicleOrganization VehicleId (pk) OrganizationId (pk) VehicleFullText VehicleId (pk) Content (FT index) VehicleFacet VehicleId (pk) FacetId (pk) FacetValueId (pk)

Now I have the following situation, I want to join those 3 tables together, group by the FacetId/FacetValueId, and retrieve the facet count for all vehicles that have the 3 predicates (organizationId, full text match, and 1 facetId). This means a join on 3 the 3 tables, plus a join for each facet we want to filter on.

Basically, I want a SQL query like this:

select vfv.FacetId, vfv.FacetValueId, count(vfv.VehicleId) as Amount
    from search.OrganizationVehicle ov 
    join search.VehicleFacet vfv on (ov.vehicleid = vfv.vehicleId)
    join search.VehicleFacet vfvCategory  on (ov.VehicleId = vfvCategory.VehicleId and vfvCategory.facetId=1 and vfvCategory.FacetValueId=20480)
    join search.VehicleFullText ft on (ov.VehicleId = ft.VehicleId and freetext (ft.Content, 'airbag' ))
    where ov.OrganizationId = 3110
    group by vfv.facetId, vfv.facetValueId 

I've made it to work, but there seem to be some SQL generation issues when trying to join multiple entities like this, and having a 'where' on the joined IQueryable<T>:

This works:

IQueryable<SearchVehicleFacetEntity> facets = linq.SearchVehicleFacet;

facets = from svf in facets 
            join o in linq.SearchOrganizationVehicle
            on svf.VehicleId equals o.VehicleId
            where o.OrganizationId == filter.OrganizationId
            select svf;

if (filter.FullTextSearch.HasData())
    facets = from svf in facets 
        join ft in linq.SearchVehicleFullText.Where(ft => ft.Content.ContainsFreeText(filter.FullTextSearch)) 
        on svf.VehicleId equals ft.VehicleId select svf;

var counts = facets.GroupBy(sv => new {sv.FacetId, sv.FacetValueId})
    .Select(svfg => new FacetCount {FacetId = svfg.Key.FacetId, FacetValueId = svfg.Key.FacetValueId, Count = svfg.Count()})
    .OrderBy(count => new {count.FacetId, count.FacetValueId})
    .ToList();

However when I change the second statement to this it fails:

facets = from svf in facets 
            join o in linq.SearchOrganizationVehicle.Where(o => o.OrganizationId == filter.OrganizationId)
            on svf.VehicleId equals o.VehicleId
            select svf;

And I would really like to be able to use the second syntax, so I can reuse the IQueryable<T> in different joins.

BTW the second syntax DOES work when leaving the full text search parameter empty, so it seems to be related to joining multiple times.

I'm using the latest version of the 2.6 runtime assemblies, adapter templates, SQL Server 2008 R2.

Generated error:

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT [LPA_L1].[FacetId], [LPA_L1].[FacetValueId], [LPA_L1].[Count] FROM (SELECT [LPA_L2].[FacetId], [LPA_L2].[FacetValueId], [LPA_L2].[LPAV_] AS [Count] FROM (SELECT [LPA_L3].[FacetId], [LPA_L3].[FacetValueId], COUNT(*) AS [LPAV_] FROM (SELECT [LPA_L4].[FacetId], [LPA_L4].[FacetValueId], [LPA_L4].[VehicleId] FROM ( (SELECT [LPA_L6].[FacetId], [LPA_L6].[FacetValueId], [LPA_L6].[VehicleId] FROM ( [search].[VehicleFacet] [LPA_L6]  INNER JOIN [search].[OrganizationVehicle] [LPA_L7]  ON  [LPA_L6].[VehicleId] = [LPA_L7].[VehicleId])) [LPA_L4]  INNER JOIN [search].[VehicleFullText] [LPA_L5]  ON  [LPA_L4].[VehicleId] = [LPA_L5].[VehicleId]) WHERE ( ( ( freetext ([LPA_L5].[Content], @LOe6e9f1a42))) AND ( [LPLA_2].[OrganizationId] = @OrganizationId3))) [LPA_L3] GROUP BY [LPA_L3].[FacetId], [LPA_L3].[FacetValueId]) [LPA_L2]) [LPA_L1] ORDER BY [LPA_L1].[FacetId] ASC,[LPA_L1].[FacetValueId] ASC
    Parameter: @LOe6e9f1a42 : String. Length: 6. Precision: 0. Scale: 0. Direction: Input. Value: "airbag".
    Parameter: @OrganizationId3 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 3110.

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException : An exception was caught during the execution of a retrieval query: The multi-part identifier "LPLA_2.OrganizationId" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
  ----> System.Data.SqlClient.SqlException : The multi-part identifier "LPLA_2.OrganizationId" could not be bound.
at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
at InMotiv.Vos.DataLayer.DatabaseSpecific.DataAccessAdapter.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) in DataAccessAdapter.cs: line 292
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.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(IEnumerable`1 source)
at InMotiv.Vos.DataLayer.VehicleFacetedSearchRepository.GetVehicleFacetCounts(VehicleFilter filter) in VehicleFacetedSearchRepository.cs: line 62
at InMotiv.Vos.Shared.Test.FacetedSearchTests.TestGetCountsFullTextSearch() in FacetedSearchTests.cs: line 67
--SqlException
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
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 HibernatingRhinos.Profiler.Appender.ProfiledDataAccess.ProfiledCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
        

If you like I can attach create scripts...

Thanks!

Wiebe

However when I change the second statement to this it fails: Code: facets = from svf in facets join o in linq.SearchOrganizationVehicle.Where(o => o.OrganizationId == filter.OrganizationId) on svf.VehicleId equals o.VehicleId select svf;

And I would really like to be able to use the second syntax, so I can reuse the IQueryable<T> in different joins.

Are you speaking about the second statement or the first one?

Which exact runtime library version (build number) are you using?

Walaa wrote:

However when I change the second statement to this it fails: Code: facets = from svf in facets join o in linq.SearchOrganizationVehicle.Where(o => o.OrganizationId == filter.OrganizationId) on svf.VehicleId equals o.VehicleId select svf;

And I would really like to be able to use the second syntax, so I can reuse the IQueryable<T> in different joins.

Are you speaking about the second statement or the first one?

Which exact runtime library version (build number) are you using?

Hi Walaa,

Yes, I would like to use the syntax you quoted here (with the 'Where' lambda expression syntax on the joined table instead of in the LINQ statement). I'm using the most recently released runtime libraries (downloaded this morning).

So please try to avoid re-using linq queries as it's not always safe to do so, especially in v.2.6.

To be clear: without your scripts we can't test it fully whether it works on v3 or not, however from the looks of your query, it seems to be related to re-aliasing members, something which is sometimes going wrong in v2.6 and which has been addressed (except for some corner cases) in v3. Re-using variables in linq queries isn't advised in some cases as it might run into re-aliasing issues as well (member is assigned an alias, and the same member is re-appearing in another sub-scope of the query, there it gets a new alias assigned, overwriting the older one, causing problems.

Otis wrote:

To be clear: without your scripts we can't test it fully whether it works on v3 or not, however from the looks of your query, it seems to be related to re-aliasing members, something which is sometimes going wrong in v2.6 and which has been addressed (except for some corner cases) in v3. Re-using variables in linq queries isn't advised in some cases as it might run into re-aliasing issues as well (member is assigned an alias, and the same member is re-appearing in another sub-scope of the query, there it gets a new alias assigned, overwriting the older one, causing problems.

OK, I've did a test update to LLBLGen v3.1, and it works perfectly there, I thought most of the 2.6 runtime were still updated with the 3.1 code base, but I was wrong there.

Note that I wasn't reusing the expressions in a way to fire multiple queries at the DB, but more to use the same IQueryable to join different tables in different queries.

Thanks guys!