c# - 我应该在这里使用什么而不是 ExecuteScalar？

Question

我正在尝试返回 DbSet<Survey>哪里Survey定义为

public class Survey
{
    public int Id { get; set; }
    [StringLength(100)]
    public string Title { get; set; }
}

我收到的错误是

An exception of type 'System.InvalidCastException' occurred in Survey.dll but was not handled in user code

Additional information: Unable to cast object of type 'System.Int32' to type 'System.Data.Entity.DbSet`1[Survey.Models.Survey]'.

关于DbSet<Survey> AllSurveys = (DbSet<Survey>)cmd.ExecuteScalar();行

    public DbSet<Survey> GetAllSurveys ( )
    {
        SqlCommand cmd = new SqlCommand("GetAllSurveys", this._Conn);
        cmd.CommandType = CommandType.StoredProcedure;
        this._Conn.Open();
        DbSet<Survey> AllSurveys = (DbSet<Survey>)cmd.ExecuteScalar();
        this._Conn.Close();
        return AllSurveys;
    }

数据库中的表和我正在使用的存储过程是由生成的

CREATE TABLE Surveys (
    id INT IDENTITY(1,1),
    title NVARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
);
GO
-- Create sprocs for adding, deleting, getting surveys
CREATE PROCEDURE AddSurvey
    @title NVARCHAR(100)
AS
    INSERT INTO Surveys (title) VALUES (@title)
GO
CREATE PROCEDURE DeleteSurvey
    @id INT
AS
    DELETE FROM Surveys WHERE id=@id
GO
-- Seed the surveys table with 1 sample survey
EXEC AddSurvey @title = "Survey Numero Uno";

GO
CREATE PROCEDURE GetAllSurveys
AS
    SELECT * FROM SURVEYS 
        ORDER BY title ASC;
GO

知道我哪里出错了吗？

Answer 1

这一行DbSet<Survey> AllSurveys = (DbSet<Survey>)cmd.ExecuteScalar();将返回一个 int。您可以尝试以下操作:

编辑基于您对老式 .NET 的评论。您的方法中还有一件事可以更改。

您的完整方法可能如下所示:

  public DbSet<Survey> GetAllSurveys ( )
    {
        using (SqlCommand cmd = new SqlCommand("GetAllSurveys", this._Conn)
        {
           cmd.CommandType = CommandType.StoredProcedure;
           this._Conn.Open();
           DbSet<Survey> AllSurveys = new DbSet<Survey>();
           using (SqlDataReader dataReader = cmd.ExecuteReader())
                        {
                            while (dataReader.Read())
                            {
                                Survey srv=new Survey {
                                  Id = dataReader.IsDBNull(0)?Default(int): dataReader.GetInt32(0),
                                  Title =dataReader.IsDBNull(1)?String.Empty: dataReader.GetString(1)};
                                AllSurveys.Add(srv);
                            }
                        }
           this._Conn.Close();
           return AllSurveys;
    }
}