SqlQuery Expression custom select include t0 before foreign table alias

[minhhungit]

This happen at .NET 5 version, .net core 3.1 version work correct

In my row, I have a field from foreign table using LeftJoin

and its expression: jReleaseFormat.[ReleaseFormatDescription]

When I write custom query it generated t0 before jReleaseFormat alias in TSQL

t0_jReleaseFormat.[ReleaseFormatDescription]

Or :

[VictorTomaili]

So what the error is you faced? It's join table field, and joins with t0 table. if you have more than one same table, how can you determine differences between "jReleaseFormat"? Also you are explicitly use T0 aliased fields with query customization.
You didn't point to a specific exception or bug.

[VictorTomaili]

Also if I use expression attribute it should use expression string without modifying it. There is no reason we have to modify expression, let expession does its work, expression does not need to care about alias I think

Imagine that, you have [dbo].[Category] table what joins self for category tree. and it's has join to other table like [dbo].[CategoryTags] with expression.
If joins didn't modify the expression prefix, how you get subcategory tags list or parent tags list?

• for self
  CategoryTags.Tag
• for join table
  T1_CategoryTags.Tag

it will be confusing when sometime we use or not use alias for expression

It's not confusing. When you need to modify query for custom result, you need to ensure main table fields and main table expression fields must be without alias. Or must include alias in From method as parameter. Other joins must have alias.

[minhhungit]

I am not sure which case you are refer to, JOIN on row class or create SqlQuery, I guess you want join on row.cs

I tried to create sample here, you can get tag list of parent cateory or child category

this is data

DECLARE @category table (
	ID INT,
	Name NVARCHAR(100),
	ParentCategoryId INT
)

INSERT INTO @category
SELECT 1, 'Abc', NULL

INSERT INTO @category
SELECT 2, 'Xyz', NULL

INSERT INTO @category
SELECT 3, 'abc sub 1', 1

DECLARE @categoryTags table (
	ID INT,
	CategoryId INT,
	Name NVARCHAR(100)
)

INSERT INTO @categoryTags
SELECT 1, 1, 'abc tag 1'

INSERT INTO @categoryTags
SELECT 1, 2, 'xyz tag 1'

INSERT INTO @categoryTags
SELECT 1, 3, 'abc sub tag 1'

INSERT INTO @categoryTags
SELECT 1, 3, 'abc sub tag 2'

SELECT 
  T0.Id AS [CategoryId],
  T0.[Name] AS [Name],
  T0.ParentCategoryId,
  jParent.[Name] AS [ParentCategoryName],
  CASE WHEN T0.ParentCategoryId IS NULL THEN pct.Name ELSE cct.Name END TagName
FROM @category T0 
LEFT JOIN @category jParent ON (jParent.Id = T0.ParentCategoryId) 
LEFT JOIN @categoryTags as pct on pct.CategoryId = t0.Id AND T0.ParentCategoryId IS NULL
LEFT JOIN @categoryTags as cct on cct.CategoryId = t0.Id AND T0.ParentCategoryId IS NOT NULL
ORDER BY T0.[Name], T0.Id
go

Because I am not sure how you want to show tag data on grid, this case is not clear so I don't have exactly answer, but by using JOIN like my image it should work, it just has problem about duplicate ID on slickgrid

Btw I guess for this case we normally need to use sql view instead

[minhhungit]

Here is real data I created using dbo.BusinessUnits (pretend it's category) and dbo.Contact (category tag)

Sorry too lazy to create new table and generate classes :)

and full code of row.cs

using Serenity.ComponentModel;
using Serenity.Data;
using Serenity.Data.Mapping;
using System;
using System.ComponentModel;


namespace StartSharpVer5.Organization.Entities
{
    [ConnectionKey("Default"), Module("Organization"), TableName("BusinessUnits")]
    [DisplayName("Business Units"), InstanceName("Business Unit")]
    [ReadPermission(PermissionKeys.BusinessUnits.Management)]
    [ModifyPermission(PermissionKeys.BusinessUnits.Management)]
    [LookupScript(Permission = PermissionKeys.General)]
    [LeftJoin("pc", "dbo.Contacts", "t0.UnitId = pc.IdentityNo AND T0.[ParentUnitId] IS NULL")]
    [LeftJoin("cc", "dbo.Contacts", "t0.UnitId = cc.IdentityNo AND T0.[ParentUnitId] IS NOT NULL")]
    public sealed class BusinessUnitRow : Row<BusinessUnitRow.RowFields>, IIdRow, INameRow
    {
        [DisplayName("Unit Id"), Identity, IdProperty]
        public Int32? UnitId
        {
            get => fields.UnitId[this];
            set => fields.UnitId[this] = value;
        }

        [DisplayName("Name"), Size(100), NotNull, QuickSearch, NameProperty]
        public String Name
        {
            get => fields.Name[this];
            set => fields.Name[this] = value;
        }

        [DisplayName("Parent Unit"), ForeignKey("BusinessUnits", "UnitId"), LeftJoin("jParentUnit"), TextualField("ParentUnitName")]
        [LookupInclude, BusinessUnitEditor]
        public Int32? ParentUnitId
        {
            get => fields.ParentUnitId[this];
            set => fields.ParentUnitId[this] = value;
        }

        [DisplayName("Parent Unit Name"), Expression("jParentUnit.[Name]")]
        public String ParentUnitName
        {
            get => fields.ParentUnitName[this];
            set => fields.ParentUnitName[this] = value;
        }

        [DisplayName("Parent Unit Parent Unit Id"), Expression("jParentUnit.[ParentUnitId]")]
        public Int32? ParentUnitParentUnitId
        {
            get => fields.ParentUnitParentUnitId[this];
            set => fields.ParentUnitParentUnitId[this] = value;
        }

        [DisplayName("Title"), Expression("CASE WHEN T0.[ParentUnitId] IS NULL THEN pc.Title ELSE cc.Title END")]
        public String Title
        {
            get => fields.Title[this];
            set => fields.Title[this] = value;
        }

        public BusinessUnitRow()
        {
        }

        public BusinessUnitRow(RowFields fields)
            : base(fields)
        {
        }

        public class RowFields : RowFieldsBase
        {
            public Int32Field UnitId;
            public StringField Name;
            public Int32Field ParentUnitId;

            public StringField ParentUnitName;
            public Int32Field ParentUnitParentUnitId;
            public StringField Title;
        }
    }
}

[minhhungit]

It's not confusing. When you need to modify query for custom result, you need to ensure main table fields and main table expression fields must be without alias. Or must include alias in From method as parameter. Other joins must have alias.

maybe you misunderstood, I mean with some Expression serenity will include custom alias before my expression, I see normally it is "explicit" field for example with [Expression(jProduct.MyFieldOnForignTable)] it will generate code:

xxx_jProduct.MyFieldOnForignTable

but for some expression like [Expression(1+ 1)] or [Expression(case when...))] it does not include custom xxx alias into these expression

That why I said it's quite confusing

[minhhungit]

Btw, back to this command var t0 = ProductReleaseRow.Fields.As("xxx");

I did not join with the alias, I just use it to get its field names and its expression I defined in row. I think if my query does not join with the alias, there is no reason Serenity use xxx and modify my expression right ?

So maybe we need to check join before using alias