Open Closed

Sorting on join query #7943


0
nitinrpatel created

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

  • 0
    nitinrpatel created

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

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

    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

    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

    Hello @ashgadala, Given link opens 404 page.

  • 0
    ryancyq created
    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
    Support Team

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