Entity Framework - Entity read-only property mapped to column of linked table

Question

I have an interesting problem to solve, but although it is common, it seems like it is not easily reachable with the Entity Framework. There are two tables:

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

A player may belong to only one team. Using TPT (first DB), we have two classes mapped to these 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 get is the IsProfessional property on the Player object:

 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 } 

Can the mapping be configured this way IsProfessional property can be used in linq queries?

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

and so that this field is populated every time the Person object materializes?

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

Already tried with:

• Separation of objects . Impossible, because I want to save the mapping of the Team and because the ratio is not 1: 1)
• Matching a Player object with a db view . I did not like it, because there are other relationships that I have with the subject. I know that they can be created manually, but updating edmx from the database will reset ssdl.

thanks

Decision

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

• I create the GetIsProfessional function (she had to do this because the calculated fields can usually be done only from the table’s own 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 

• I created a computed field in the Player table

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

• Since I use the first db approach, I just update the model from the database and what I can request in this field and pre-populate it when the Player object materializes.

Answer 1

This cannot be done with EF. There are several options that do not do exactly what you want, but come nearer more or less:

1. Create a TeamPlayers property in your context that returns players with the team enabled so you can always execute player.Team.IsProfessional even if the context has already been omitted.

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

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

3. Create a static property in Player that returns an expression that accesses Team.IsProfessional (requires a live context or command):

    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 a computed field because it is always populated, so you can use it inside and out of context.

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 are worried about rebuilding your EDMX, you can do it in part:

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

Answer 3

You can use System.ComponentModel.DataAnnotations.Schema.NotMappedAttribute to prevent IsProfessional from displaying such as:

 // 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 the EF5 Model First , and I requested more than DbContext/DbSet and ObjectContext/ObjectQuery without any exceptions. (100% tested)