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

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>()
                            })

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

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>()
    });

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 '''

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)));

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

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());

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

Glad it's solved!