Retain Column name when using function in QuerySpec

Posts   
 
    
jovball
User
Posts: 434
Joined: 23-Jan-2005
# Posted on: 31-May-2018 08:10:15   

I view this as a bug but it might be a behavior change request instead. When I use a function in QuerySpec, the DQE is generating a "random" alias for that field instead of using the same alias as the original field.

This code used the ToUpper function on the ImportFileName column.



var qf = new QueryFactory();
            var query = qf.Create()
               .Select<DocumentSearchDto>(DocumentFileSearchFields.DocumentId, DocumentFileSearchFields.CreationTime, 
                                DocumentFileSearchFields.ImportFileName.ToUpper(),
                               DocumentFileSearchFields.AccountNumber, DocumentFileSearchFields.WorkerId)


Which results in this generated query with the "LLBLV_1" column alias for the ImportFileName column. As a result, the ImportFileName field in the DocumentSearchDto doesn't see a matching name and all the values are null for that field.


SELECT [MyDatabase].[dbo].[DocumentFileSearch].[DocumentID],
       [MyDatabase].[dbo].[DocumentFileSearch].[CreationTime],
       UPPER([MyDatabase].[dbo].[DocumentFileSearch].[ImportFileName]) AS [LLBLV_1],
       [MyDatabase].[dbo].[DocumentFileSearch].[AccountNumber],
       [MyDatabase].[dbo].[DocumentFileSearch].[WorkerID] 


I have to code it with an explicit alias like this to fix that problem.



var qf = new QueryFactory();
            var query = qf.Create()
               .Select<DocumentSearchDto>(DocumentFileSearchFields.DocumentId, DocumentFileSearchFields.CreationTime, 
                                DocumentFileSearchFields.ImportFileName.ToUpper().As("ImportFileName"),
                               DocumentFileSearchFields.AccountNumber, DocumentFileSearchFields.WorkerId)


I'm using runtime 5.2.1 and Designer 5.2 (5.2.1) RTM. (We're currently testing 5.4 and should be moving there before too long. My tests show the same results with 5.4 (5.4.0) RTM)

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 31-May-2018 08:43:16   

H Joel . This behavior is normal, since LLBLGen has to generate SQL and pick aliases that won't clash between them. In the end, those alias are not 'that' important. Do you want geneated sql your way? then use Queryspec simple_smile Or any other reason why you want this.

David Elizondo | LLBLGen Support Team
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 31-May-2018 09:15:56   

Additionally, it has to specify an alias for a function / aggregate column so it will generate one if there's not one specified, so if you want to have a column aliased as 'A' then you have to specify A as the column alias.

Frans Bouma | Lead developer LLBLGen Pro
jovball
User
Posts: 434
Joined: 23-Jan-2005
# Posted on: 31-May-2018 14:17:06   

I'm using QuerySpec already for this and there isn't any naming conflict between the columns in the query.

In this query, I have a field that is in the query twice. The DQE is smart enough to know when I have a conflict and will throw this exception at runtime.

"The name 'City' is present multiple times in the query's projection. Please specify an alias using '.As(alias)'."


var qf = new QueryFactory();

var query = qf.Create()
       .Select<CustomerModel>(CustomerFields.CompanyName, CustomerFields.CompanyName.ToUpper(),  
       CustomerFields.City, CustomerFields.City)
       .OrderBy(CustomerFields.CompanyName.Ascending());

I'm just trying to convert the database values to uppercase. I would expect the DQE to also be be smart enough to see when there is NOT any conflict and use the original column name. The automatic alias doesn't make sense here. How could I know what name to use at runtime for that column?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 31-May-2018 15:04:10   

The main goal is that every column in the resultset has a name. By default it pulls the name of the field, e.g. CustomerFields.CompanyName will lead to the name 'CompanyName'. If there's an expression or function on the field, it can't do that, as it's a function (.e.g UPPER(somefield)) so it will cook up a name of its own.

I think David referred to name conflicts wrt table aliases, not field aliases as is the case here.

So you didn't specify a name in this particular case, so it will create one by itself. If you want to have it a name like 'CompanyName', you have to specify it, i.e. CustomerFields.CompanyName.ToUpper().As("CompanyName").

This might sound silly but it doesn't know what's inside the function call, nor what the function does, so it can't determine that this is an uppercase function or a custom function call which does a lot of other things. So we don't look inside the function call object but require you to specify an alias IF you want it to have a specific name.

In that light, I'm not really sure I understand your last question. simple_smile

Frans Bouma | Lead developer LLBLGen Pro
jovball
User
Posts: 434
Joined: 23-Jan-2005
# Posted on: 01-Jun-2018 03:11:29   

I was in a hurry and didn't do a good job of explaining this. Also, I made a copy/paste error in the previous code example and that didn't help things. flushed

Here's what I meant to post for the code example.


var qf = new QueryFactory();

var query = qf.Create()
     .Select<CustomerModel>(CustomerFields.CompanyName.ToUpper(),
     CustomerFields.City, CustomerFields.City)
     .OrderBy(CustomerFields.CompanyName.Ascending());

Before I go any further, I'm going to define the term "DQE alias" to mean any alias that the DQE assigns (like "AS [LLBLV_1]") as opposed to one that I specifically assign.

Taking the above query as an example. There are two ambiguous parts to that query and the DQE doesn't treat them the same way. I think they should either behave the same way or else be opposite of the current behavior.

1) The City field is listed twice without an .As(). At runtime, the DQE will NOT give the second one a random column alias ("AS [LLBLV_1]"). Instead, it will throw an exception even though giving the second City field a DQE alias would be a more tolerant approach and would be unlikely to cause anything to fail.

2)The CompanyName.ToUpper() also does not have an .As(). At runtime, the DQE WILL assign the DQE alias. This is actually a less tolerant approach because my code will fail silently for things like model mapping.

I get your point that the DQE doesn't know what the function call does. However, the column needs an alias no matter what the function call returns. That is why you are having the DQE assign an alias ("AS [LLBLV_1]").

I'd prefer that since the DQE knows it has got to assign an alias, why can't it check to see if the original column name is in use already? If it is in use, either assign a DQE alias or throw an exception as it currently does when the same field is defined more than once without an alias.

Hoping that the third time is the charm for this thread. smile

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 01-Jun-2018 10:34:27   

You mention two things: 1) the alias assignment to a column without an alias and 2) the acceptance of duplicated names.

These aren't the same thing, so I'll address them separately.

ad 1) It assigns an alias to the column to be able to refer to it in an outer query. It doesn't know whether the query it's handling is a nested query or an outer query. So it will assign an alias to the column if there's no alias specified, which is the case if the column is an aggregate function call or an expression and no alias has been explicitly set.

In linq the values of these columns are stored in properties which are known inside the expression tree and are used for this (you don't explicitly specify aliases in linq). In queryspec however this isn't the case (even though you use a projection, this isn't used for query construction), so the developer has to either specify the alias manually or leave it to the framework if it's not important what the alias will be (e.g. you use a projection to an object array).

I recon you want the framework to throw an exception in the case when there's no alias present instead of assigning an artificial one. As I said above, there are situations where the artificial alias is easy to work with and you then run into a problem as you get an exception which is unnecessary. I understand you want this because the value wasn't added to the object as the aliased field name wasn't the same as the class property. So the actual problem is that the resultset doesn't match the property set to project to, or better: there is a column which isn't projected.

I think the latter is a better aspect to throw an exception on if that happens (optionally)?

ad 2) If there are duplicated names in the query projection, it doesn't really matter if the framework throws an exception or the database, so we left it to the database. We don't want to assign an artificial alias here as it's clearly a user error and we shouldn't correct a user error here, as it's up to the user what to do.

This isn't the same as 1) as with 1) there are situations where it's ok to have the field artificially aliased and therefore it's not a user error, but in 2) it's always a user error, hence no artificial aliasing.

Frans Bouma | Lead developer LLBLGen Pro
jovball
User
Posts: 434
Joined: 23-Jan-2005
# Posted on: 01-Jun-2018 14:05:20   

Ah yes, I hadn't thought about the nested query situation. This is why you can write an great ORM and I can't. smile

Let me think about this a bit more now that I understand the big picture better. I might decide that things are better just the way they are.