我有一个有趣的问题需要解决,但是虽然很常见,但看起来使用Entity Framework并不容易实现。有两个表:
Player(Id,TeamId,FirstName,LastName)
Team(Id, Name, IsProfessional)
玩家只能属于一个团队。使用TPT(数据库优先),我们将两个类映射到这些表:
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;}
}
我希望实现的是在Player实体上有一个IsProfessional属性:
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
}
是否可以配置映射,以便在linq查询中使用IsProfessional属性?
var result= db.Players.Where(p=>p.IsProfessional==true);
并且希望每当Player实体被实例化时,该字段都能被填充?
Player pl = db.Players.Where(p=>p.FirstName="Lionel").FirstOrDefault();
if(pl.IsProfessional)
{
//do something...
}
已经尝试过以下方法:
- 实体拆分。不可行,因为我想保留Team映射,并且关系不是1:1。
- 将Player实体映射到数据库视图。不喜欢这种方法,因为Player实体还有其他关系需要使用。我知道可以手动创建它们,但从数据库更新edmx将重置ssdl。
谢谢
解决方案
基于Gert Arnold答案中的第二个选项,适合我的解决方案如下:
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
I created computed field on
Player
tableALTER TABLE Players ADD [IsProfessional] AS dbo.GetIsProfessional(TeamId)
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.
Player.IsProfessional
应该和Player.Team.IsProfessional
得到相同的结果吗?EF 目前不支持不映射到简单数据库字段的属性,很抱歉,但也许有人会回答一个好的替代方案。 - user743382