Sorting on join query #7943

nitinrpatel created 4 years ago

Hello,

There are Parent and Child table from which i fetch details and display on my Grid page. I Want to Sort my column which are not from my parent table but using belo code I got Some error.

public async Task<PagedResultDto<InvitationListDto>> GetInvitation(GetInvitationInput input)
        {
            var Invitation_List = _InstallerRepository
               .GetAll();

            var InvitationList = (from Inst in Invitation_List
                                  join Us in _userRepository.GetAll() on Inst.UserId equals Us.Id into Usjoined
                                  from Us in Usjoined.DefaultIfEmpty()
                                  where (Inst.UserId != 0 && Inst.IsApproved == 0)
                                  group Inst by Inst into instGrouped
                                  select instGrouped.Key);

            var resultCount = await InvitationList.CountAsync();
            var results = await InvitationList
                .AsNoTracking()
                .OrderBy(input.Sorting)
                .PageBy(input)
                .ToListAsync();

            var Invitation = results.MapTo<List<InvitationListDto>>();
            foreach (var item in Invitation)
            {
                item.CustName = _tenantRepository.GetAll().Where(p => p.Id == item.TenantId).Select(p => p.FullName).FirstOrDefault();
            }

            return new PagedResultDto<InvitationListDto>(resultCount, Invitation.MapTo<List<InvitationListDto>>());
        }

10 Answer(s)

0

maliming created 4 years ago
Support Team

I Want to Sort my column which are not from my parent table but using belo code I got Some error.

What is the specific error? Can you share the Invitation entity class?

nitinrpatel created 4 years ago

"No property or field 'custName' exists in type 'Installer'" this is the error I am getting while I am performing sorting on Customer Name(we use tenant as a Customer).

Below is the class, I am using in my code which I had given before.

[AutoMapFrom(typeof(Installer))]
    public class InvitationListDto : FullAuditedEntityDto
    {
        public virtual int TenantId { get; set; }

        public virtual int UserId { get; set; }

        public virtual string CustName { get; set; }

        public virtual string FullName { get; set; }

        public virtual Guid Guid { get; set; }
    }

Below is an Installer Entity class file from which I am fetching details, I have given reference to tenant table and also want to give sorting on dashboard by tenant name and also with installer name.

[Table("Installer")]
    public class Installer : FullAuditedEntity, IMustHaveTenant
    {
        public const int MaxFullNameLength = 200;
        public const int MaxPhoneLength = 10;
        public const int MaxMobileLength = 10;
        public const int MaxEmailLength = 255;
        public const int MaxCompanyNameLength = 100;
        public const int MaxABNLength = 50;
        public const int MaxCompanyPhoneLength = 10;
        public const int MaxFaxLength = 20;
        public const int MaxPostalDelevaryNoLength = 50;
        public const int MaxlogoLength = 500;
        public const int MaxStreetAddressLength = 300;
        public const int MaxUnitNumberLength = 50;
        public const int MaxUnitTypeLength = 50;
        public const int MaxStreetNumberLength = 50;
        public const int MaxStreetNameLength = 100;
        public const int MaxStreetTypeLength = 50;
        public const int MaxStreetCityLength = 100;
        public const int MaxStreetStateLength = 50;
        public const int MaxStreetPostCodeLength = 50;
        public const int MaxInstallerExpiryLength = 100;
        public const int MaxElectricalExpiryLength = 100;
        public const int MaxDesignerExpiryLength = 100;
        public const int MaxAccreditationAccPhotoLength = 200;
        public const int MaxLicencePhotoLength = 200;
        public const int MaxInstallerSignatureLength = 50;
        public const int MaxElectricianSignatureLength = 50;
        public const int MaxDesignerSignatureLength = 50;

        public int TenantId { get; set; }

        public virtual int UserId { get; set; }

        public virtual string UserName { get; set; }

        [Required]
        [MaxLength(MaxFullNameLength)]
        public virtual string FullName { get; set; }

        [Required]
        [MaxLength(MaxPhoneLength)]
        public virtual string Phone { get; set; }

        [MaxLength(MaxMobileLength)]
        public virtual string Mobile { get; set; }

        [Required]
        [MaxLength(MaxEmailLength)]
        public virtual string EmailId { get; set; }

        [Required]
        [MaxLength(MaxCompanyNameLength)]
        public virtual string CompanyName { get; set; }

        [Required]
        [MaxLength(MaxABNLength)]
        public virtual string ABN { get; set; }

        [MaxLength(MaxCompanyPhoneLength)]
        public virtual string CompanyPhone { get; set; }

        [MaxLength(MaxFaxLength)]
        public virtual string Fax { get; set; }

        public virtual int AddressType { get; set; }

        [MaxLength(MaxPostalDelevaryNoLength)]
        public virtual string PostalDelNo { get; set; }

        public virtual int PostalDelType { get; set; }

        [Required]
        public virtual bool IsGST { get; set; }

        [Required]
        [MaxLength(MaxlogoLength)]
        public virtual string Logo { get; set; }

        [MaxLength(MaxStreetAddressLength)]
        public virtual string StreetAddress { get; set; }

        [MaxLength(MaxUnitNumberLength)]
        public virtual string UnitNo { get; set; }

        [MaxLength(MaxUnitTypeLength)]
        public virtual string UnitType { get; set; }

        [Required]
        [MaxLength(MaxStreetNumberLength)]
        public virtual string StreetNumber { get; set; }

        [Required]
        [MaxLength(MaxStreetNameLength)]
        public virtual string StreetName { get; set; }

        [Required]
        [MaxLength(MaxStreetTypeLength)]
        public virtual string StreetType { get; set; }

        [Required]
        [MaxLength(MaxStreetCityLength)]
        public virtual string Suburb { get; set; }

        [Required]
        [MaxLength(MaxStreetStateLength)]
        public virtual string State { get; set; }

        [Required]
        [MaxLength(MaxStreetPostCodeLength)]
        public virtual string PostCode { get; set; }

        public virtual bool? IsInst { get; set; }

        [MaxLength(MaxInstallerExpiryLength)]
        public virtual string InstallerExpiry { get; set; }

        public virtual DateTime? InsAccreExDate { get; set; }

        public virtual bool? IsElec { get; set; }

        [MaxLength(MaxElectricalExpiryLength)]
        public virtual string ElectricalExpiry { get; set; }

        public virtual DateTime? EleLicenceExDate { get; set; }

        public virtual bool? IsDesc { get; set; }

        [MaxLength(MaxDesignerExpiryLength)]
        public virtual string DesignerExpiry { get; set; }

        public virtual DateTime? DesignerAccreExDate { get; set; }

        [Required]
        [MaxLength(MaxAccreditationAccPhotoLength)]
        public virtual string AccreditationAccPhoto { get; set; }

        [Required]
        [MaxLength(MaxLicencePhotoLength)]
        public virtual string LicencePhoto { get; set; }

        public virtual Guid Guid { get; set; }

        [MaxLength(MaxInstallerSignatureLength)]
        public virtual string InstallerSignature { get; set; }

        [MaxLength(MaxElectricianSignatureLength)]
        public virtual string ElectricianSignature { get; set; }

        [MaxLength(MaxDesignerSignatureLength)]
        public virtual string DesignerSignature { get; set; }

        public virtual int IsApproved { get; set; }

        public virtual string RejectReason { get; set; }
    }

0

maliming created 4 years ago
Support Team

"No property or field 'custName' exists in type 'Installer'"

What is the specific code that caused the exception? Please share the complete error stack.
0

nitinrpatel created 4 years ago

Hear is an error box and Stack Trace of my code

at System.Linq.Dynamic.ExpressionParser.ParseMemberAccess(Type type, Expression instance) at System.Linq.Dynamic.ExpressionParser.ParseIdentifier() at System.Linq.Dynamic.ExpressionParser.ParsePrimaryStart() at System.Linq.Dynamic.ExpressionParser.ParsePrimary() at System.Linq.Dynamic.ExpressionParser.ParseUnary() at System.Linq.Dynamic.ExpressionParser.ParseMultiplicative() at System.Linq.Dynamic.ExpressionParser.ParseAdditive() at System.Linq.Dynamic.ExpressionParser.ParseComparison() at System.Linq.Dynamic.ExpressionParser.ParseLogicalAnd() at System.Linq.Dynamic.ExpressionParser.ParseLogicalOr() at System.Linq.Dynamic.ExpressionParser.ParseExpression() at System.Linq.Dynamic.ExpressionParser.ParseOrdering() at System.Linq.Dynamic.DynamicQueryable.OrderBy(IQueryable source, String ordering, Object[] values) at System.Linq.Dynamic.DynamicQueryable.OrderBy[T](IQueryable`1 source, String ordering, Object[] values) at TechnoForms.NewInvitation.NewInvitationAppService.
0
maliming created 4 years ago
Support Team
hi

Obviously this problem is caused by Dynamic Linq. When you sort InvitationList(IQueryable<Installer>), the property custName that does not exist in the Installer class is used. It only exists in InvitationListDto.

var results = await InvitationList .AsNoTracking() .OrderBy(input.Sorting) .PageBy(input) .ToListAsync();
0

ashgadala created 4 years ago

Similar issue. I have the property in the classes but error is thrown.

I did not make any changes other than code generated using the RADTool.
1

ashgadala created 4 years ago

I see that we have already fixed this issue.

You need to apply this commit to your project: https://github.com/aspnetzero/aspnet-zero-core/commit/097461f7249e772c20a9ab5306a4c59b14c9ec90
0

nitinrpatel created 4 years ago

Hello @ashgadala, Given link opens 404 page.
0

ryancyq created 4 years ago
Support Team

Hi @nitinrpatel, you will need to login as the github account associated with your ANZ license to view the link.
0

ismcagdas created 4 years ago
Support Team

This issue is closed because it has not had recent activity for a long time.

Have an answer to this question? Log in and write your answer.