Can this be done with Entity Framework 4 ? If not, what can do this?

Question

Hi folks,

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
=> DiedOn
=> 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?

Answer 1

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