Linq selecting object from relational list of object by search keyword

Question

I want to get the Principal of School using a search keyword. Schools can have list of users from which one is of a principal role.

Now my problem is I am implementing an auto search that takes a keyword and searches on the basis for School Name, Code, and principal name.

Code :

 public class School : AuditableDataEntity<int>
    {
        [Required]
        [MaxLength(200)]
        public string Name { get; set; }

        [MaxLength(150)]
        public string Code { get; set; }

        public District District { get; set; }

        public ICollection<UserProfile> Users { get; set; }


    }

public class UserProfile : AuditableDataEntity<Guid>
    {

        [Required]
        [MaxLength(100)]
        public string FirstName { get; set; }

        [Required]
        [MaxLength(100)]
        public string LastName { get; set; }

        [Required]
        [MaxLength(200)]
        public string Email { get; set; }

        [MaxLength(50)]
        public string PhoneWork { get; set; }

        [MaxLength(20)]
        public int PhoneWorkExt { get; set; }

        [MaxLength(50)]
        public string PhoneMobile { get; set; }       

        public UserLevel UserLevel { get; set; }

        public UserRole UserRole { get; set; }

        public UserDesignation UserDesignation { get; set; }

        public School School { get; set; }
        public int? SchoolId { get; set; }

        public string FullName => $"{FirstName} {LastName}";
    }

Task<Response<IEnumerable<SchooSearchDTO>>> ISchoolQueryService.GetSchoolAutoCompleteData(string searchKeyword)
        {
            return _schoolQueryRepository.WithRelatedEntities().Where(x => x.Name.Contains(searchKeyword)
            ||x.Code.Contains(searchKeyword)
            || x.Users.FirstOrDefault(y => y.DataEntityState == DataEntityState.Published && y.UserDesignation == UserDesignation.Principal).FullName.Contains(searchKeyword)).OrderBy(u => u.Name).Select(z => new SchooSearchDTO
            {
                PrincipalName = z.Name,
                CDSCode = z.Code

            }).ToResponseListAsync();
        }

Error :

The LINQ expression 'DbSet .Where(s => s.Name.Contains(__searchKeyword_0) || s.CDSCode.Contains(__searchKeyword_0) || DbSet .Where(u => EF.Property>(s, "Id") != null && EF.Property>(s, "Id") == EF.Property>(u, "SchoolId")) .Where(u => (int)u.DataEntityState == 1 && (int)u.UserDesignation == 1) .Select(u => u.FullName) .FirstOrDefault().Contains(__searchKeyword_0))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Selim Yildiz · Accepted Answer · 2020-04-30T13:11:53.703

The problem is here that EF can not translate this into SQL.

x.Users.FirstOrDefault(y => y.DataEntityState == DataEntityState.Published && y.UserDesignation == UserDesignation.Principal).FullName.Contains(searchKeyword)

So try to use Any instead of selecting user with FirstOrDefault in LINQ, so that EF be able to translate this into SQL.

Task<Response<IEnumerable<SchooSearchDTO>>> ISchoolQueryService.GetSchoolAutoCompleteData(string searchKeyword)
{
    return _schoolQueryRepository.WithRelatedEntities().Where(x => x.Name.Contains(searchKeyword)
        ||x.Code.Contains(searchKeyword)
        || x.Users.Any(y => y.DataEntityState == DataEntityState.Published && y.UserDesignation == UserDesignation.Principal && y.FirstName.Contains(searchKeyword))
    ).OrderBy(u => u.Name).Select(z => new SchooSearchDTO
    {
        PrincipalName = z.Name,
        CDSCode = z.Code

    }).ToResponseListAsync();
}

See: The LINQ expression could not be translated and will be evaluated locally

Edit:

You also need to use FirstName since FullName is property that you don't have in SQL.

It worked by a change to && y.FirstName.Contains(searchKeyword) problem was with FullName so it worked by changing to Any and changing FullName to FirstName. — , Apr 30 '20 at 13:08
Yes good point, I forgot to mentioned that.. I have updated my answer. — Selim Yildiz, Apr 30 '20 at 13:14

Linq selecting object from relational list of object by search keyword

1 Answers1