Entity Framework - Entity read-only property mapped to a column of related table

Question

I have interesting problem to solve but, although common, it looks like it's not easily achievable with Entity Framework. There are two tables:

Player(Id,TeamId,FirstName,LastName)
Team(Id, Name, IsProfessional)

Player can belong only to one team. Using TPT (DB first), we have two classes mapped to those tables:

public class Player
{
   public int Id{get;set;}
   public int TeamId{get;set;}
   public string FirstName{get; set;}
   public string LastName{get; set;}
   public Team Team{get;set;}
}

public class Team
{ 
   public int Id{get; set;}
   public string Name{get;set;}
   public bool IsProfessional{get;set;}
   public IEnumerable<Player> Players{get;}
}

What I would like to achieve is property IsProfessional on Player entity:

public class Player
    {
       public int Id{get;set;}
       public int TeamId{get;set;}
       public string FirstName{get; set;}
       public string LastName{get; set;}
       public Team Team{get;set;}
       **public bool IsProfessional{get;}** should be read-only
    }

Is it possible to configure mapping that way IsProfessional property can be used in linq queries?

var result= db.Players.Where(p=>p.IsProfessional==true);

and to have that field populated every time Player entity is materialized?

Player pl = db.Players.Where(p=>p.FirstName="Lionel").FirstOrDefault();
if(pl.IsProfessional)
{
//do something...
}

Already tried with:

• Entity Splitting. Not possible because I want to keep Team mapping and because relationship is not 1:1)
• Mapping Player entity to a db view. Didn't like it because there are other relationships Player entity has that I need. I know it is possible to create them manually, but updating edmx from database will reset ssdl.

Thanks

Solution

Based on second option in Gert Arnold answer, solution that fits my needs is as follows:

1. I create function GetIsProfessional (had to do it because computed fields normally can be made only from own table fields)

   CREATE FUNCTION [dbo].[GetIsProfessional](@teamId as INT)
   RETURNS bit
   
   BEGIN
   
   DECLARE @isProfi AS bit
   
   SELECT @isProfi = IsProfessional
   FROM Teams
   WHERE Id = @teamId
   
   RETURN @isProfi
   
   END
   

2. I created computed field on Player table

   ALTER TABLE Players ADD [IsProfessional] AS dbo.GetIsProfessional(TeamId)
   

3. As I'm using db first approach, I just update model from database and that's it, I can query on that field and it's pre populated when Player object is materialized.

Answer 1

This can't be done with EF. There are some options that don't do exactly what you want, but get close more or less:

1. Create a property TeamPlayers in your context that returns the players with the team included, so that you can always do player.Team.IsProfessional even when the context has already been diposed.

   public IQueryable<Player> TeamPlayers
   {
       get { return this.Players.Include("Team"); }
   }
   

2. Create a calculated field in the database table and map to it with DatabaseGeneratedOption.Computed.

3. Create a static property in Player that returns the expression that accesses Team.IsProfessional (requires a living context or team included):

   public static Expression<Func<Player, bool>> IsProfessional
   {
       get { return p => p.Team.IsProfessional; }
   }
   ...
   db.Players.Where( p=> p.FirstName="Lionel").Where(Player.IsProfessional)....
   

I would prefer the calculated field, because it is always populated, so you can use it inside and outside the scope of a context.