- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
Cant get the right field with projection (maybe alias issue?)
Joined: 12-Jul-2019
Hi guys,
i'm using code generated by LLBLGen Pro 5.5
The sql statement i want to excute is this:
SELECT dbo.T_S_DocumentiNew.IDDocumento, dbo.T_S_DocumentiNew.IDAccUtente, dbo.T_R_DocumentiTipologiaProgetti.IDProgetto,
dbo.T_R_DocumentiTipologiaProgetti.IDDocumentoTipologia, dbo.T_R_DocumentiTipologiaProgetti.IDDocumentoTipologiaProgetti,
dbo.T_S_DocumentiNew.Descrizione, dbo.T_S_DocumentiNew.DataInserimento, dbo.T_R_DocumentiTipologia.Descrizione AS Tipologia,
dbo.T_R_DocumentiTipologiaProgetti.Nome
FROM dbo.T_S_DocumentiNew
INNER JOIN dbo.T_R_DocumentiTipologiaProgetti ON dbo.T_S_DocumentiNew.IDDocumentoTipologiaProgetti = dbo.T_R_DocumentiTipologiaProgetti.IDDocumentoTipologiaProgetti
INNER JOIN dbo.T_R_DocumentiTipologia ON dbo.T_R_DocumentiTipologiaProgetti.IDDocumentoTipologia = dbo.T_R_DocumentiTipologia.IDDocumentoTipologia
ORDER BY dbo.T_S_DocumentiNew.IDDocumento DESC
I'm using this code:
var qf = new QueryFactory();
var q = qf.Create()
.Select(() => new
{
IdDocumento = TSDocumentiNewFields.Iddocumento.Source("a").ToValue<int>(),
IdAccDocumento = TSDocumentiNewFields.IdaccUtente.Source("a").ToValue<int?>(),
IdProgetto = TRDocumentiTipologiaProgettiFields.Idprogetto.Source("a").ToValue<int>(),
IdDocumentoTipologia = TRDocumentiTipologiumFields.IddocumentoTipologia.Source("a").ToValue<int>(),
IdDocumentoTipologiaProgetti = TRDocumentiTipologiaProgettiFields.IddocumentoTipologiaProgetti.Source("a").ToValue<int>(),
Descrizione = TSDocumentiNewFields.Descrizione.Source("a").ToValue<string>(),
DataInserimento = TSDocumentiNewFields.DataInserimento.Source("a").ToValue<DateTime>(),
Tipologia = TRDocumentiTipologiumFields.Descrizione.As("Tipologia").Source("a").ToValue<string>(),
TipologiaSpecifica = TRDocumentiTipologiaProgettiFields.Nome.Source("a").ToValue<string>()
})
.From(qf.TSDocumentiNew
.From(QueryTarget.InnerJoin(TRDocumentiTipologiaProgettiEntity.Relations.TSDocumentiNewEntityUsingIddocumentoTipologiaProgetti)
.InnerJoin(TRDocumentiTipologiumEntity.Relations.TRDocumentiTipologiaProgettiEntityUsingIddocumentoTipologia))
.Select(TSDocumentiNewFields.Iddocumento,
TSDocumentiNewFields.IdaccUtente,
TRDocumentiTipologiaProgettiFields.Idprogetto,
TRDocumentiTipologiumFields.IddocumentoTipologia,
TRDocumentiTipologiaProgettiFields.IddocumentoTipologiaProgetti,
TSDocumentiNewFields.Descrizione,
TSDocumentiNewFields.DataInserimento,
TRDocumentiTipologiumFields.Descrizione.As("Tipologia"),
TRDocumentiTipologiaProgettiFields.Nome).As("a"))
.OrderBy(new SortExpression(TSDocumentiNewFields.Iddocumento | SortOperator.Descending));
When i execute it the field TRDocumentiTipologiumFields.Descrizione.As("Tipologia") contains the data in the field TSDocumentiNewFields.Descrizione ...
Could it be an issue with aliases i'm not understanding? Thanks
Joined: 21-Aug-2005
I think it is.
Why are you using two selects?
Either use the following select
.Select(TSDocumentiNewFields.Iddocumento,
TSDocumentiNewFields.IdaccUtente,
TRDocumentiTipologiaProgettiFields.Idprogetto,
TRDocumentiTipologiumFields.IddocumentoTipologia,
TRDocumentiTipologiaProgettiFields.IddocumentoTipologiaProgetti,
TSDocumentiNewFields.Descrizione,
TSDocumentiNewFields.DataInserimento,
TRDocumentiTipologiumFields.Descrizione.As("Tipologia"),
TRDocumentiTipologiaProgettiFields.Nome)
Or use this one if you want to project the results:
.Select(() => new
{
IdDocumento = TSDocumentiNewFields.Iddocumento.ToValue<int>(),
IdAccDocumento = TSDocumentiNewFields.IdaccUtente.ToValue<int?>(),
IdProgetto = TRDocumentiTipologiaProgettiFields.Idprogetto.ToValue<int>(),
IdDocumentoTipologia = TRDocumentiTipologiumFields.IddocumentoTipologia.ToValue<int>(),
IdDocumentoTipologiaProgetti = TRDocumentiTipologiaProgettiFields.IddocumentoTipologiaProgetti.ToValue<int>(),
Descrizione = TSDocumentiNewFields.Descrizione.ToValue<string>(),
DataInserimento = TSDocumentiNewFields.DataInserimento.ToValue<DateTime>(),
Tipologia = TRDocumentiTipologiumFields.Descrizione.As("Tipologia").ToValue<string>(),
TipologiaSpecifica = TRDocumentiTipologiaProgettiFields.Nome.ToValue<string>()
})
Joined: 12-Jul-2019
Walaa wrote:
I think it is.
Why are you using two selects? use this one if you want to project the results:
.Select(() => new { IdDocumento = TSDocumentiNewFields.Iddocumento.ToValue<int>(), IdAccDocumento = TSDocumentiNewFields.IdaccUtente.ToValue<int?>(), IdProgetto = TRDocumentiTipologiaProgettiFields.Idprogetto.ToValue<int>(), IdDocumentoTipologia = TRDocumentiTipologiumFields.IddocumentoTipologia.ToValue<int>(), IdDocumentoTipologiaProgetti = TRDocumentiTipologiaProgettiFields.IddocumentoTipologiaProgetti.ToValue<int>(), Descrizione = TSDocumentiNewFields.Descrizione.ToValue<string>(), DataInserimento = TSDocumentiNewFields.DataInserimento.ToValue<DateTime>(), Tipologia = TRDocumentiTipologiumFields.Descrizione.As("Tipologia").ToValue<string>(), TipologiaSpecifica = TRDocumentiTipologiaProgettiFields.Nome.ToValue<string>() })
Well, i supposed it had to work that way
I tried in a simpler code to use your hint, as this
var qf = new QueryFactory();
var q = qf.Create()
.Select(() => new
{
IdProgetto = TAProgettiFields.Idprogetto.ToValue<int>(),
Descrizione = TAProgettiFields.Descrizione.ToValue<string>()
}).Distinct()
.From(qf.TAProgetti
.From(QueryTarget.InnerJoin(TRDocumentiTipologiaProgettiEntity.Relations.TAProgettiEntityUsingIdprogetto)
.InnerJoin(TRDocumentiTipologiumEntity.Relations.TRDocumentiTipologiaProgettiEntityUsingIddocumentoTipologia))
.OrderBy(new SortExpression(TAProgettiFields.Descrizione.Source("a") | SortOperator.Ascending)));
var result = adapter.FetchQuery(q);
and it says
SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: 'An exception was caught during the execution of a retrieval query: Impossibile associare l'identificatore in più parti "intranet2014.dbo.T_A_Progetti.IDProgetto".
Impossibile associare l'identificatore in più parti "intranet2014.dbo.T_A_Progetti.descrizione".. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.'
and the inner is:
SqlException: Impossibile associare l'identificatore in più parti "intranet2014.dbo.T_A_Progetti.IDProgetto".
Impossibile associare l'identificatore in più parti "intranet2014.dbo.T_A_Progetti.descrizione".
Joined: 21-Aug-2005
Ok, you just need to set an alias for the field with a similar name to another one in the select list. You don't need to alias the source.
The following test works:
var qf = new QueryFactory();
var q = qf.Create()
.From(qf.Order
.InnerJoin(OrderEntity.Relations.OrderDetailEntityUsingOrderId))
.Select(() => new
{
OID1 = OrderFields.OrderId.As("OID").ToValue<int>(),
OID2 = OrderDetailFields.OrderId.ToValue<int>()
});
Joined: 12-Jul-2019
Walaa wrote:
Ok, you just need to set an alias for the field with a similar name to another one in the select list. You don't need to alias the source.
The following test works:
var qf = new QueryFactory(); var q = qf.Create() .From(qf.Order .InnerJoin(OrderEntity.Relations.OrderDetailEntityUsingOrderId)) .Select(() => new { OID1 = OrderFields.OrderId.As("OID").ToValue<int>(), OID2 = OrderDetailFields.OrderId.ToValue<int>() });
Thanks for your help, i wrote the code as you suggested:
var qf = new QueryFactory();
var q = qf.Create()
.From(qf.TSDocumentiNew
.From(QueryTarget.InnerJoin(TRDocumentiTipologiaProgettiEntity.Relations.TSDocumentiNewEntityUsingIddocumentoTipologiaProgetti)
.InnerJoin(TRDocumentiTipologiumEntity.Relations.TRDocumentiTipologiaProgettiEntityUsingIddocumentoTipologia))
.Select(() => new
{
IdDocumento = TSDocumentiNewFields.Iddocumento.ToValue<int>(),
IdAccDocumento = TSDocumentiNewFields.IdaccUtente.ToValue<int?>(),
IdProgetto = TRDocumentiTipologiaProgettiFields.Idprogetto.ToValue<int>(),
IdDocumentoTipologia = TRDocumentiTipologiumFields.IddocumentoTipologia.ToValue<int>(),
IdDocumentoTipologiaProgetti = TRDocumentiTipologiaProgettiFields.IddocumentoTipologiaProgetti.ToValue<int>(),
Descrizione = TSDocumentiNewFields.Descrizione.ToValue<string>(),
DataInserimento = TSDocumentiNewFields.DataInserimento.ToValue<DateTime>(),
Tipologia = TRDocumentiTipologiumFields.Descrizione.As("Tipologia").ToValue<string>(),
TipologiaSpecifica = TRDocumentiTipologiaProgettiFields.Nome.ToValue<string>()
})
.OrderBy(new SortExpression(TSDocumentiNewFields.Iddocumento | SortOperator.Descending)));
but when i execute it the following error is returned
SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException: 'No projection defined for DynamicQuery with alias '''
Joined: 17-Aug-2003
Shouldn't it be this:
var qf = new QueryFactory();
var q = qf.TSDocumentiNew
.From(QueryTarget.InnerJoin(TRDocumentiTipologiaProgettiEntity.Relations.TSDocumentiNewEntityUsingIddocumentoTipologiaProgetti)
.InnerJoin(TRDocumentiTipologiumEntity.Relations.TRDocumentiTipologiaProgettiEntityUsingIddocumentoTipologia))
.Select(() => new
{
IdDocumento = TSDocumentiNewFields.Iddocumento.ToValue<int>(),
IdAccDocumento = TSDocumentiNewFields.IdaccUtente.ToValue<int?>(),
IdProgetto = TRDocumentiTipologiaProgettiFields.Idprogetto.ToValue<int>(),
IdDocumentoTipologia = TRDocumentiTipologiumFields.IddocumentoTipologia.ToValue<int>(),
IdDocumentoTipologiaProgetti = TRDocumentiTipologiaProgettiFields.IddocumentoTipologiaProgetti.ToValue<int>(),
Descrizione = TSDocumentiNewFields.Descrizione.ToValue<string>(),
DataInserimento = TSDocumentiNewFields.DataInserimento.ToValue<DateTime>(),
Tipologia = TRDocumentiTipologiumFields.Descrizione.As("Tipologia").ToValue<string>(),
TipologiaSpecifica = TRDocumentiTipologiaProgettiFields.Nome.ToValue<string>()
})
.OrderBy(new SortExpression(TSDocumentiNewFields.Iddocumento | SortOperator.Descending)));
Joined: 12-Jul-2019
Otis wrote:
Shouldn't it be this:
omissis
Well, it started when i couldnt get the right field out of a query
I'm using this code:
var qf = new QueryFactory(); var q = qf.Create() .Select(() => new { IdDocumento = TSDocumentiNewFields.Iddocumento.Source("a").ToValue<int>(), IdAccDocumento = TSDocumentiNewFields.IdaccUtente.Source("a").ToValue<int?>(), IdProgetto = TRDocumentiTipologiaProgettiFields.Idprogetto.Source("a").ToValue<int>(), IdDocumentoTipologia = TRDocumentiTipologiumFields.IddocumentoTipologia.Source("a").ToValue<int>(), IdDocumentoTipologiaProgetti = TRDocumentiTipologiaProgettiFields.IddocumentoTipologiaProgetti.Source("a").ToValue<int>(), Descrizione = TSDocumentiNewFields.Descrizione.Source("a").ToValue<string>(), DataInserimento = TSDocumentiNewFields.DataInserimento.Source("a").ToValue<DateTime>(), Tipologia = TRDocumentiTipologiumFields.Descrizione.As("Tipologia").Source("a").ToValue<string>(), TipologiaSpecifica = TRDocumentiTipologiaProgettiFields.Nome.Source("a").ToValue<string>() }) .From(qf.TSDocumentiNew .From(QueryTarget.InnerJoin(TRDocumentiTipologiaProgettiEntity.Relations.TSDocumentiNewEntityUsingIddocumentoTipologiaProgetti) .InnerJoin(TRDocumentiTipologiumEntity.Relations.TRDocumentiTipologiaProgettiEntityUsingIddocumentoTipologia)) .Select(TSDocumentiNewFields.Iddocumento, TSDocumentiNewFields.IdaccUtente, TRDocumentiTipologiaProgettiFields.Idprogetto, TRDocumentiTipologiumFields.IddocumentoTipologia, TRDocumentiTipologiaProgettiFields.IddocumentoTipologiaProgetti, TSDocumentiNewFields.Descrizione, TSDocumentiNewFields.DataInserimento, TRDocumentiTipologiumFields.Descrizione.As("Tipologia"), TRDocumentiTipologiaProgettiFields.Nome).As("a")) .OrderBy(new SortExpression(TSDocumentiNewFields.Iddocumento | SortOperator.Descending));
When i execute it the field TRDocumentiTipologiumFields.Descrizione.As("Tipologia") contains the data in the field TSDocumentiNewFields.Descrizione ...
Could it be an issue with aliases i'm not understanding? Thanks
Then Walaa suggested some changes about the query structure, and i thought that i could try to make the code thinner (and maybe resolve the initial problem i had): now i'm wondering ... is the code i'm using weird? The suggested code anyway ends in error
Thanks
Joined: 17-Aug-2003
The QueryTarget.From is a problem in your initial query, as you create a dynamic query which initially is empty. So you can directly use qf.Entity.InnerJoin() in the From clause (see below)
I'll look at your initial query in your start post.
I also noticed something weird in your projection in the initial query:
IdDocumentoTipologia = TRDocumentiTipologiumFields.IddocumentoTipologia.Source("a").ToValue<int>(),
but in the SQL query you posted in the initial post, you have: dbo.T_R_DocumentiTipologiaProgetti.IDDocumentoTipologia
not sure if that's the same entity, as right after it you do: TRDocumentiTipologiaProgettiFields.IddocumentoTipologiaProgetti
which is the entity you were looking for I think.
And for: Tipologia = TRDocumentiTipologiumFields.Descrizione.As("Tipologia").Source("a").ToValue<string>(),
you have in the initial SQL query: dbo.T_R_DocumentiTipologia.Descrizione AS Tipologia so not sure if these are the same, but I guess they are (ium is the singular variant, ia is the plural variant)
You don't join an entity twice so you don't have to alias them nor specify a target alias via 'Source'. Your initial query first projected all fields out and then projected the fields into a type using the lambda, but you don't need to do that, 1 select is enough, see: https://www.llblgen.com/Documentation/5.6/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/QuerySpec/gencode_queryspec_projections.htm#typed-projections
Ok, back to your query and what I think it should be. It's the easiest if you use a join, to start with the entity, then use From(QueryTarget.... ) to append the joins further (see: https://www.llblgen.com/Documentation/5.6/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/QuerySpec/gencode_queryspec_joins.htm)
The full query then becomes:
var qf = new QueryFactory();
var q = qf.TSDocumentiNew
.From(QueryTarget.InnerJoin(TRDocumentiTipologiaProgettiEntity.Relations.TSDocumentiNewEntityUsingIddocumentoTipologiaProgetti)
.InnerJoin(TRDocumentiTipologiumEntity.Relations.TRDocumentiTipologiaProgettiEntityUsingIddocumentoTipologia))
.Select(() => new
{
IdDocumento = TSDocumentiNewFields.Iddocumento.ToValue<int>(),
IdAccDocumento = TSDocumentiNewFields.IdaccUtente.ToValue<int?>(),
IdProgetto = TRDocumentiTipologiaProgettiFields.Idprogetto.ToValue<int>(),
IdDocumentoTipologia = TRDocumentiTipologiaProgettiFields.IddocumentoTipologia.ToValue<int>(),
IdDocumentoTipologiaProgetti = TRDocumentiTipologiaProgettiFields.IddocumentoTipologiaProgetti.ToValue<int>(),
Descrizione = TSDocumentiNewFields.Descrizione.ToValue<string>(),
DataInserimento = TSDocumentiNewFields.DataInserimento.ToValue<DateTime>(),
Tipologia = TRDocumentiTipologiumFields.Descrizione.As("Tipologia").ToValue<string>(),
TipologiaSpecifica = TRDocumentiTipologiaProgettiFields.Nome.ToValue<string>()
})
.OrderBy(TSDocumentiNewFields.Iddocumento.Descending());
Joined: 12-Jul-2019
Otis wrote:
The QueryTarget.From is a problem in your initial query, as you create a dynamic query which initially is empty. So you can directly use qf.Entity.InnerJoin() in the From clause (see below)
...
I also noticed something weird in your projection in the initial query:
not sure if these are the same, but I guess they are (ium is the singular variant, ia is the plural variant)
Yes they are, their name is different from dbsql but its ok
You don't join an entity twice so you don't have to alias them nor specify a target alias via 'Source'. Your initial query first projected all fields out and then projected the fields into a type using the lambda, but you don't need to do that, 1 select is enough
OK!! Thanks
Ok, back to your query and what I think it should be. It's the easiest if you use a join, to start with the entity, then use From(QueryTarget.... )
The full query then becomes: ....
ok, now i understand and its really simpler!!
Following your hints, i noticed i was doing this:
var result = adapter.FetchQuery(qf.Create().SelectFrom(q).Page(search.Page, search.PageSize));
I changed it into the following
var result = adapter.FetchQuery(q.Page(search.Page, search.PageSize));
it resolved the initial issue (a different field was returned) .. down know why but it works and now the code is better
Thanks, your support is so smart