This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hi,
I initially tried to do a linq union but got an error that this isnt supported in the current version of lightspeed (version 4) so I started looking at the Query object and doing a union, I couldnt find too much documentation on doing a union using the query object so hopefully I am doing something incorrect
var query = new Query(typeof(GlTransactionPrimary)); query.QueryExpression = Entity.Attribute("GlCodeId") == glCodeId && Entity.Attribute("FundingCompanyId") == fundingCompanyId && Entity.Attribute("GlPeriodId").In(ray); query.Projection.Add("Amount"); query.Projection.Add("Comments"); query.Projection.Add("EffectiveDate"); query.Projection.Add("GlCodeId"); query.Projection.Add("FundingCompanyId"); query.Projection.Add("GlPeriodId");
var query1 = new Query(typeof(GlTransactionArchive)); query1.QueryExpression = Entity.Attribute("GlCodeId") == glCodeId && Entity.Attribute("FundingCompanyId") == fundingCompanyId && Entity.Attribute("GlPeriodId").In(ray); query1.Projection.Add("Amount"); query1.Projection.Add("Comments"); query1.Projection.Add("EffectiveDate"); query1.Projection.Add("GlCodeId"); query1.Projection.Add("FundingCompanyId"); query1.Projection.Add("GlPeriodId"); query1.ComposedQueries.Add(query); query1.ComposeMethod = "UNION"; UnitOfWorkScope.Current.Project(query1) If I run the queries individuall they work fine but adding in the union above it doesnt work at all, I tried putting in some garbage text in the ComposeMethod property and got the same result, it appears to ignore that field, so hopefully I havent wired something up correctly. Thanks, Jay
|
|
|
You actually need to have 3 queries to make this work. q1 and q2 which would be the two queries you are unioning togethor and then q3 which returns the same entity type as q1 & q2 and adds the other two queries to the composed query collection and then sets the compose method to be UNION. e.g.
var query = new Query(typeof(GlTransactionPrimary));
query.QueryExpression = Entity.Attribute("GlCodeId") == glCodeId && Entity.Attribute("FundingCompanyId") == fundingCompanyId && Entity.Attribute("GlPeriodId").In(ray);
query.Projection.Add("Amount"); query.Projection.Add("Comments"); query.Projection.Add("EffectiveDate"); query.Projection.Add("GlCodeId"); query.Projection.Add("FundingCompanyId"); query.Projection.Add("GlPeriodId");
var query1 = new Query(typeof(GlTransactionArchive));
query1.QueryExpression = Entity.Attribute("GlCodeId") == glCodeId && Entity.Attribute("FundingCompanyId") == fundingCompanyId && Entity.Attribute("GlPeriodId").In(ray);
query1.Projection.Add("Amount"); query1.Projection.Add("Comments"); query1.Projection.Add("EffectiveDate"); query1.Projection.Add("GlCodeId"); query1.Projection.Add("FundingCompanyId"); query1.Projection.Add("GlPeriodId");
var query2 = new Query(typeof(GlTransactionArchive));
query2.ComposedQueries.Add(query); query2.ComposedQueries.Add(query1);
query2.ComposeMethod = "UNION";
UnitOfWorkScope.Current.Project(query2)
Jeremy |
|
|
Thanks Jeremy, The issue I has is query and query 1 are different types, one is GLTransactionPrimary and one is GLTransactionArchive, can lightspeed do a union across entites? This is the sql I am getting
SELECT [t0].[[Amount]] AS [t0.[Amount]], [t0].[[Comments]] AS [t0.[Comments]], [t0].[[EffectiveDate]] AS [t0.[EffectiveDate]], [t0].[[GlCodeId]] AS [t0.[GlCodeId]], [t0].[[FundingCompanyId]] AS [t0.[FundingCompanyId]], [t0].[[GlPeriodId]] AS [t0.[GlPeriodId]] FROM ( SELECT [GlTransactionArchive].[Amount] AS [Amount], [GlTransactionArchive].[Comments] AS [Comments], [GlTransactionArchive].[EffectiveDate] AS [EffectiveDate], [GlTransactionArchive].[GlCodeId] AS [GlCodeId], [GlTransactionArchive].[FundingCompanyId] AS [FundingCompanyId], [GlTransactionArchive].[GlPeriodId] AS [GlPeriodId] FROM [GlTransactionArchive] WHERE (([GlTransactionArchive].[GlCodeId] = 1 AND [GlTransactionArchive].[FundingCompanyId] = 1) AND [GlTransactionArchive].[GlPeriodId] IN (1, 2, 127)) UNION SELECT [GlTransactionArchive].[Amount] AS [Amount], [GlTransactionArchive].[Comments] AS [Comments], [GlTransactionArchive].[EffectiveDate] AS [EffectiveDate], [GlTransactionArchive].[GlCodeId] AS [GlCodeId], [GlTransactionArchive].[FundingCompanyId] AS [FundingCompanyId], [GlTransactionArchive].[GlPeriodId] AS [GlPeriodId] FROM [GlTransactionArchive] WHERE (([GlTransactionArchive].[GlCodeId] = 1 AND [GlTransactionArchive].[FundingCompanyId] = 1) AND [GlTransactionArchive].[GlPeriodId] IN (1, 2, 127)) ) [GlTransactionArchive]
A side note is its producing a malformed query with incorrect syntax and sql throws an error, but this point is mute if I can union across entites.
Thanks,
Jay |
|
|
No, composed queries expect that all of the queries share the same entity type. Ill pop a ticket on our backlog to investigate if we can lift this restriction in a future release though as that would be fairly useful in projection scenarios.
Jeremy |
|
|
Hi Jeremy, Has this been fixed in recent releases? I need to do a UNION of two different tables. Ta. |
|
|
No there has been no change here, composed queries still expect the same entity type to be used.
|
|