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.

Nahshunn

So Player.IsProfessional should give the same result as Player.Team.IsProfessional? EF doesn't currently support properties that don't map to a simple database field, sorry, but perhaps someone will answer with a good alternative. – Bibbs

That's exactly what I need, except Player.IsProfessional should be read only. All alternatives I could find myself are not realy nice. – Nahshunn

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.

Lighterman

Great! Calculated field fits my needs the best: 1) I can query on computed field, 2) it is populated with other Player properties. Thanks a lot! – Nahshunn

Answer 2

What if you extend Player to have a property that pulls from Team?

public partial 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 { return Team.IsProfessional; } }
}

Of course, if you're worried about regenerating your EDMX, you can make it a partial:

public partial class Player
{
   public bool IsProfessional{ get { return Team.IsProfessional; } }
}

Hellbender

Yes, but this way Player.IsProfessional is not usable in linq queries (as I explained in question). Also, when property Player.IsProfessional is accessed it will do sql query just to get one boolean from db. I would prefer if that boolean is filled at same time as all other Player properties. – Nahshunn

Answer 3

You can use System.ComponentModel.DataAnnotations.Schema.NotMappedAttribute to prevent mapping of the IsProfessional property like these:

// Mapped part of 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; }
}

// Unmapped part of entity
using System.ComponentModel.DataAnnotations.Schema;
...
public partial class Player
{
    [NotMapped()]
    public bool IsProfessional { get { /* ... IsProfessional calculation logic comes here ... */ } }
}

I used this attribute in EF5's Model First approach and I queried over DbContext/DbSet and ObjectContext/ObjectQuery without any exceptions. (100% tested)

Entoil

OK, but this property doesn't show in the DataSource I create from this entity. – Crabby

I usually use ObjectDataSource and it show all public properties of my entity classes. I think EntityDataSource just read mapped properties of generated entities because of their schema declaration in .edmx file. – Entoil

Does this work? What about the Linq problems stated above – Sorrells

It is good enough to provide a calculated (virtual) data column along with Eager Loading approach, but there is no way to translate its logic to real data column(s) in WHERE clause of custom query. – Entoil