Can this be done with Entity Framework 4 ? If not, what can do this?
I'm making a simplistic trivial pursuit game. I'm not sure if (and then how) I can do the following with EF4 :-
I have a table structure as follows.
Table: TrivialPursuitQuestion
=> ID
=> Unique Question
=> AnswerId
=> AnswerType (ie. Geography, Entertainment, etc).
Table: GeographyAnswer
=> ID
=> Place Name
=> LatLong
Table: EntertainmentAnswer:
=> ID
=> Name
=> BordOn
=> Died开发者_如何学PythonOn
=> Nationality .. and other meta data
... etc ..
So when a person asks a unique question ... the stored proc is able to figure out what type of answer it is (ie. AnswerType field) ... and therefore query against the correct table.
EG.
SELECT @AnswerId = AnswerId, @AnswerType = AnswerType
FROM TrivialPursuitQuestions
WHERE UniqueQuestion = @Question
IF @AnswerId > 0 AND @AnswerType > 0 BEGIN
IF @AnswerType = 1
SELECT *
FROM GeographicAnswers
WHERE AnswerId = @AnswerID
IF @AnswerType = 2
SELECT *
FROM EntertainmentAnswer
WHERE AnswerId = @AnswerId
... etc ...
END
Now .. i'm not sure how to do this with EF. First of all, the stored proc can now return MULTIPLE result types .. so i'm not sure if that's really really bad.
So then I thought, maybe the stored procedure should return Multiple Recordsets .. with all but one recordset containing result(s) ... because by design, only one answer type will ever be found...
EG.
-- Same SELECT as above...
IF @AnswerId > 0 BEGIN
SELECT *
FROM GeographicAnswers
WHERE AnswerId = CASE @AnswerId WHEN 1 THEN @AnswerID ELSE 0 END
SELECT *
FROM EntertainmentAnswer
WHERE AnswerId = CASE @AnswerId WHEN 2 THEN @AnswerID ELSE 0 END
... etc ...
END
and this will return 6 (multiple) recordsets ... but only one of these should ever have some data.
Now, if this is a better solution ... is this possible with EF4 and how?
I'm trying to avoid doing TWO round trips to the db AND also having to figure out WHAT to try and retrieve ... i don't want to have to figure it out .. i'm hoping with some smart modelling the system is just smart enough to say 'OH! u this is the right answer'. Sort of like a Answer Factory (ala Factory Pattern) but with Sql Server + EF4.
ANyone have any ideas?
In the EF Designer you could create an entity called Answer
and then create entities that derive from answer called GeographyAnswer
, EntertainmentAnswer
, ... using a discriminator AnswerType
in the Answer entity. Add the Question
entity. Add the association from question to Answer
, mark it 1:1. Let EF generate the DDL for your database.
Now you can do question.Answer
to get the Answer
for a Question
. You can look at the Type of Answer
and show the appropriate UI.
Though, this also begs the question, if it's a 1:1 correspondence between question and answer, why not have a single entity QuestionAnswer
and derive from that to create QuestionAnswerGeography
, QuestionAnswerEntertainment
, ... Now you can pick Questions
of a specific type easily: dataContext.QuestionAnswers.ofType<QuestionAnswerGeography>()
for example.
Here's an Entity Diagram for the case where an answer may be shared by multiple questions:
This model will allow you do a query like this:
var geographyQuestions = objectContext.Answers.OfType<Geography>().SelectMany(answer => answer.Questions);
The DDL generate by this model is:-
-- Creating table 'Answers'
CREATE TABLE [dbo].[Answers] (
[Id] int IDENTITY(1,1) NOT NULL
);
GO
-- Creating table 'Questions'
CREATE TABLE [dbo].[Questions] (
[Id] int IDENTITY(1,1) NOT NULL,
[AnswerId] int NOT NULL,
[QuestionText] nvarchar(max) NOT NULL
);
GO
-- Creating table 'Answers_Geography'
CREATE TABLE [dbo].[Answers_Geography] (
[PlaceName] nvarchar(max) NOT NULL,
[LatLong] nvarchar(max) NOT NULL,
[Id] int NOT NULL
);
GO
-- Creating table 'Answers_Entertainment'
CREATE TABLE [dbo].[Answers_Entertainment] (
[Name] nvarchar(max) NOT NULL,
[BornOn] datetime NULL,
[DiedOn] datetime NULL,
[Id] int NOT NULL
);
GO
-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------
-- Creating primary key on [Id] in table 'Answers'
ALTER TABLE [dbo].[Answers]
ADD CONSTRAINT [PK_Answers]
PRIMARY KEY CLUSTERED ([Id] ASC);
GO
-- Creating primary key on [Id] in table 'Questions'
ALTER TABLE [dbo].[Questions]
ADD CONSTRAINT [PK_Questions]
PRIMARY KEY CLUSTERED ([Id] ASC);
GO
-- Creating primary key on [Id] in table 'Answers_Geography'
ALTER TABLE [dbo].[Answers_Geography]
ADD CONSTRAINT [PK_Answers_Geography]
PRIMARY KEY CLUSTERED ([Id] ASC);
GO
-- Creating primary key on [Id] in table 'Answers_Entertainment'
ALTER TABLE [dbo].[Answers_Entertainment]
ADD CONSTRAINT [PK_Answers_Entertainment]
PRIMARY KEY CLUSTERED ([Id] ASC);
GO
-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------
-- Creating foreign key on [AnswerId] in table 'Questions'
ALTER TABLE [dbo].[Questions]
ADD CONSTRAINT [FK_QuestionAnswer]
FOREIGN KEY ([AnswerId])
REFERENCES [dbo].[Answers]
([Id])
ON DELETE NO ACTION ON UPDATE NO ACTION;
-- Creating non-clustered index for FOREIGN KEY 'FK_QuestionAnswer'
CREATE INDEX [IX_FK_QuestionAnswer]
ON [dbo].[Questions]
([AnswerId]);
GO
-- Creating foreign key on [Id] in table 'Answers_Geography'
ALTER TABLE [dbo].[Answers_Geography]
ADD CONSTRAINT [FK_Geography_inherits_Answer]
FOREIGN KEY ([Id])
REFERENCES [dbo].[Answers]
([Id])
ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
-- Creating foreign key on [Id] in table 'Answers_Entertainment'
ALTER TABLE [dbo].[Answers_Entertainment]
ADD CONSTRAINT [FK_Entertainment_inherits_Answer]
FOREIGN KEY ([Id])
REFERENCES [dbo].[Answers]
([Id])
ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
精彩评论