Why EF core tell me 'specified member is unmapped'?

Question

I want to filter users by name and return a new DTO by using the projection query (by Select):

var result = context.Users
    .Where(user => user.FullName == search)
    .Select(u => new UserPagingViewModel
    {
        Id = u.Id,
        IsSearchable = u.IsSearchable,
        FirstName = u.FirstName,
        LastName = u.LastName,
        FullName = u.FullName,
        Photo200 = u.Photo200,
        City = u.City,
        About = u.About,
        Skills = u.UserSkills.Select(us => us.Skill.Name),
        IsTeamMember = u.UserTeams.Any(ut => ut.UserAction == UserActionEnum.JoinedTeam)
    })
    .ToList();

User class:

public class User : IHasId
{
    public long Id { get; set; }
    public string FirstName { get; set; }
    public string SecondName { get; set; }
    public string LastName { get; set; }
    public string City { get; set; }
    public string About { get; set; }
    public string Telegram { get; set; }
    public string Email { get; set; }
    public string Mobile { get; set; }
    public string FullName => FirstName + " " + SecondName + " " + LastName;
    public string Photo100 { get; set; }
    public string Photo200 { get; set; }
    public bool IsModerator { get; set; }
    public List<UserTeam> UserTeams { get; set; }
    public List<UserSkill> UserSkills { get; set; }

    [NotMapped]
    public List<Team> TeamsToRecruit { get; set; }
    [NotMapped]
    public bool AnyTeamOwner { get; set; }
}

Data Base is PostgreSql. Data provider PostgreSQL/Npgsql provider for Entity Framework Core

But when I try to execute this request, I get an exception with the following message:

The LINQ expression 'DbSet<User>() .Where(u => user.FullName == search)' could not be translated. Additional information: Translation of member 'FullName' on entity type 'User' failed. This commonly occurs when the specified member is unmapped. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

I don't want use client evaluation. If I compare for example the FirstName property everything works fine.

Please help me figure out how to solve this problem.

Answer 1

You have FullName in three places. Since you don't have a full name column (or computed column) you can't use that in dbcontext queries. It will generate sql like below.

SELECT FULLNAME,... FROM USERS WHERE FULLNAME = N"some value".

This is why you are getting an error.

You need to remove all FullName from dbcontext query and do where filter like below.

var result = context.Users
    .Where(x => (x.FirstName + " " + x.SecondName + " " + x.LastName) == search)
    .Select(u => new UserPagingViewModel
    {
        Id = u.Id,
        IsSearchable = u.IsSearchable,
        FirstName = u.FirstName,
        LastName = u.LastName,
        //FullName = u.FullName,
        Photo200 = u.Photo200,
        City = u.City,
        About = u.About,
        Skills = u.UserSkills.Select(us => us.Skill.Name),
        IsTeamMember = u.UserTeams.Any(ut => ut.UserAction == UserActionEnum.JoinedTeam)
    })
    .ToList();

This will you something like below

FROM [Users] AS [t]
WHERE ([t].[FirstName] + N' ' + [t].[SecondName] + N' ' + [t].[LastName]) = N'some value' 

Then remove FullName from User object.

public class User : IHasId
{
    public long Id { get; set; }
    public string FirstName { get; set; }
    public string SecondName { get; set; }
    public string LastName { get; set; }
    public string City { get; set; }
    public string About { get; set; }
    public string Telegram { get; set; }
    public string Email { get; set; }
    public string Mobile { get; set; }
    // public string FullName => FirstName + " " + SecondName + " " + LastName;
    public string Photo100 { get; set; }
    public string Photo200 { get; set; }
    public bool IsModerator { get; set; }
    public List<UserTeam> UserTeams { get; set; }
    public List<UserSkill> UserSkills { get; set; }

    [NotMapped]
    public List<Team> TeamsToRecruit { get; set; }
    [NotMapped]
    public bool AnyTeamOwner { get; set; }
}

Add FullName to UserPagingViewModel.

public class UserPagingViewModel{
    ...
    public string FirstName { get; set; }
    public string SecondName { get; set; }
    public string LastName { get; set; }
    public string FullName => $"{FirstName} {SecondName} {LastName}";
}

If you want FullName as part of user object, then add as notmapped or use HasComputedColumnSql