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.

`FullName` is unmapped (and possibly other things), end of story... Either add them as a calculated fields in your db and add it to your model builder as such, or don't use it in queries — TheGeneral, Feb 22 '21 at 08:41
`FullName` is a computed `get` property, not a member of the database table itself, this means it can't be translated into a SQL query as the column, presumably, doesn't exist there. Either change the property to have a `get` and a `set`, or query by other columns that do exist in the database table. — Martin Costello, Feb 22 '21 at 08:45
In fact, instead of going down the rabbit hole I propose, just comment out this `// FullName = u.FullName,` — TheGeneral, Feb 22 '21 at 08:45
@00110001, Oh really there is no column in the table, everything turned out to be very simple :) Thank you so much! but the option with commenting `// FullName = u.FullName` didn't work and I don't really understand why it should have worked — Daniel, Feb 22 '21 at 08:57

cdev · Accepted Answer · 2021-02-24T03:39:55.827

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

yeap the same error, this query still tries to execute on the DB side, and there is no FullName field — Daniel, Feb 23 '21 at 16:48
This will be a terrible running query because it isn't a [sargable query](https://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable) — Erik Philips, May 06 '22 at 21:44

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

1 Answers1