This is a trimmed down example of the basic schema I'm working with. I realize this example is convoluted, but there are reasons that this schema is in use which are unimportant to describing the problem I'm seeing.
Also worth noting: I am using Database First.
Team (Table)
Id
Name
Player (Table)
Id
TeamId
Year
Name
Position
Game (Table)
Id
Date
HomeTeamId
AwayTeamId
GameHomePlayer_VW (View)
GameId (This is defined as the PK in the EDMX)
HomePlayerId
The View GameHomePlayer_VW is defined as:
SELECT
Game.Id,
Player.Id
FROM Game
LEFT OUTER JOIN Player
ON
Game.HomeTeamId = Player.TeamId AND
YEAR(Game.Date) = Player.Year
(If you're curious there is also a GameAwayPlayer_VW :))
EDMX Association between Game and GameHomePlayer_VW:
Referential Constraint Game -> GameHomePlayer_VW
End1 Multiplicity 0..1 (Zero or One of GameHomePlayer_VW)
End1 Navigation Property Game
End1 OnDelete None
End1 Role Name GameHomePlayer_VW
End2 Multiplicity 1 (One of Game)
End2 Navigation Property GameHomePlayer_VW
End2 OnDelete None
End2 Role Name Game
Name GameHomePlayer_VWGame
Game and GameHomePlayer_VW classes generated by EF:
public partial class Game
{
public int Id { get; set; }
public DateTime Date { get; set; }
public Nullable<int> HomeTeamId { get; set; }
public Nullable<int> AwayTeamId { get; set; }
public virtual Team HomeTeam { get; set; }
public virtual Team AwayTeam { get; set; }
public virtual GameHomePlayer_VW GameHomePlayer_VW { get; set; }
public virtual GameAwayPlayer_VW GameAwayPlayer_VW { get; set; }
}
public partial class GameHomePlayer_VW
{
public int GameId { get; set; }
public Nullable<int> PlayerId { get; set; }
public virtual Game Game { get; set; }
public virtual Player Player { get; set; }
}
This setup allows me to query through the view objects as so:
private async Task<Game> GetGame(int id)
{
return await Context.Games
.Include(i => i.GameHomePlayer_VW.Player)
.Include(i => i.GameAwayPlayer_VW.Player)
.FirstOrDefaultAsync(i => i.Id == id);
}
The code I'm using to delete the game is as follows:
private async Task DeleteGame(int id)
{
var game = await Context.Games.FirstOrDefaultAsync(i => i.Id == id);
Context.Games.Remove(game);
await Context.SaveChangesAsync();
}
The problem I'm running into is that when I try to delete a Game entity using EF I receive the following error:
Unable to update the EntitySet 'GameHomePlayer_VW' because it has a DefiningQuery and no
<DeleteFunction>element exists in the<ModificationFunctionMapping>element to support the current operation.
There are other entities that reference game and as using cascade deletes to be removed.
I tried unsuccessfully to use the solution in this post: Unable to update the EntitySet - because it has a DefiningQuery and no <UpdateFunction> element exist
I also tried wiring a stored proc that took the PK as defined by the EDMX and returned 0.
I tried initializing a new instance of my DbContext class to delete it like below and still run into the same problem.
private async Task DeleteGame(int id)
{
using (var context = new MyDbContext)
{
var game = await context.Games.FirstOrDefaultAsync(i => i.Id == id);
context.Games.Remove(game);
await context.SaveChangesAsync();
}
}
Does anyone have any suggestions on maintaining a dynamic link based on the Game's Date while allowing the entities that reference it to be Added/Updated/Deleted?
