Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Error on join multiple entities
 

Pages: 1
LLBLGen Pro Runtime Framework
Error on join multiple entities
Page:1/1 

  Print all messages in this thread  
Poster Message
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 01-Jun-2011 12:30:36.  
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:
Code:

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:
Code:

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:
    
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.

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:
Code:

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
Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14588 posts
# Posted on: 01-Jun-2011 12:39:07.  
Quote:
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?


  Top
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 01-Jun-2011 12:42:56.  
Walaa wrote:
Quote:
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).

Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14588 posts
# Posted on: 01-Jun-2011 17:11:15.  
So please try to avoid re-using linq queries as it's not always safe to do so, especially in v.2.6.

  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37880 posts
# Posted on: 02-Jun-2011 11:05:11.  
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.

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 06-Jun-2011 18:23:25.  
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!





Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.