Question

I m Making a simplistic trivialsearch/a。我不敢确定(然后是如何确保)我能与EF4做以下工作:

我的表格结构如下。

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

......

因此,当一个人问一个独特的问题......所储存的胎盘能够说明哪一类答案(即)。回答Type领域......,因此对正确表格提出疑问。

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

    .......
END

现在。 i 不能确定如何与欧洲自由贸易区合作。首先,所储存的代用品现在可以返回MultorIPLE的产物类型,这样,如果真的坏,则不会确定。

因此,我认为,储存的程序可能应当归还多纪录片,除一个记录外,所有记录都包含结果......因为设计只能找到一个答案。

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

    .......
END

这将归还6份(多份)记录......但其中只有一份应当有某些数据。

现在,如果这是一个更好的解决办法,那么这能否用EF4和如何解决?

我试图避免进行技术妇女组织的回合旅行,也不得不从WHAT中看到,试图找回......却不希望将其说出。 i m 喜欢一些聪明的模型,这个系统仅够说:OH! u 这是正确的答案。 Sort of such as a Response Factory (ala Factory patterns) but with Sql服务器 + EF4.

ANone有任何想法?

Answer 1

在EF设计商中,你可以创建名为Answer的实体,然后创建由“回答的实体。 Geography Answer,EntertainmentAnswer, ...... using a discriminator AnswerTypein the Response entities. 添加<代码> 问题实体。将协会列入<代码>Answer,编号1:1。让欧洲论坛为你们的数据库制作DDL。

现在请上<条码>查询。您可以看<代码>Answer的类型,并显示适当的国际交易日志。

虽然这还使人想起问题,如果它在问答之间有1:1个函件,为什么没有一个单一实体<代码> 问答/代码>,并从中产生到<编码> 问答/编码>。现在,你可以轻松地选取 问题的特定类型:dataContext. QuestionAnswers.ofType< Question AnswerGeography>,例如。

Here s an Entity Diagram for the case where an answer may be shared by multiple questions:

这一模式将使你能够提出这样的问题:

  var geographyQuestions = objectContext.Answers.OfType<Geography>().SelectMany(answer => answer.Questions);

该模型产生的DDL是:

-- 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

Table: TrivialPursuitQuestion

Table: GeographyAnswer

Table: EntertainmentAnswer:

友情链接