Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Cant get the right field with projection (maybe alias issue?)
 

Pages: 1
LLBLGen Pro Runtime Framework
Cant get the right field with projection (maybe alias issue?)
Page:1/1 

  Print all messages in this thread  
Poster Message
19252
User



Location:

Joined on:
12-Jul-2019 18:24:08
Posted:
10 posts
# Posted on: 24-Oct-2019 15:04:21.  
Hi guys,

i'm using code generated by LLBLGen Pro 5.5

The sql statement i want to excute is this:

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

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
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14529 posts
# Posted on: 24-Oct-2019 17:42:24.  
I think it is.

Why are you using two selects?

Either use the following select
Code:
                            .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:
Code:
                            .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>()
                            })



  Top
19252
User



Location:

Joined on:
12-Jul-2019 18:24:08
Posted:
10 posts
# Posted on: 24-Oct-2019 19:26:06.  
Walaa wrote:
I think it is.

Why are you using two selects?
use this one if you want to project the results:
Code:
                            .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 Dissapointed

I tried in a simpler code to use your hint, as this

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

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

Code:
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".
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14529 posts
# Posted on: 24-Oct-2019 19:57:54.  
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:

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


  Top
19252
User



Location:

Joined on:
12-Jul-2019 18:24:08
Posted:
10 posts
# Posted on: 25-Oct-2019 09:54:18.  
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:

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

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

Code:
SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException: 'No projection defined for DynamicQuery with alias '''
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37638 posts
# Posted on: 25-Oct-2019 10:25:49.  
Shouldn't it be this:

Code:

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


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



Location:

Joined on:
12-Jul-2019 18:24:08
Posted:
10 posts
# Posted on: 25-Oct-2019 10:42:59.  
Otis wrote:
Shouldn't it be this:

omissis



Well, it started when i couldnt get the right field out of a query

Quote:
I'm using this code:

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
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37638 posts
# Posted on: 25-Oct-2019 11:20:30.  
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:
Code:

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



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



Location:

Joined on:
12-Jul-2019 18:24:08
Posted:
10 posts
# Posted on: 25-Oct-2019 12:12:00.  
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

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

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

Code:
var result = adapter.FetchQuery(qf.Create().SelectFrom(q).Page(search.Page, search.PageSize));


I changed it into the following

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

  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37638 posts
# Posted on: 25-Oct-2019 14:44:18.  
Glad it's solved! Regular Smiley

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


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

Version: 2.1.12172008 Final.