LINQ的存储过程返回发出─一个int [英] Linq Stored Procedure Issue- Returning an int

问题描述

我试图调用存储过程使用LINQ，存储过程返回SQL中的价值很好，但是当我把它拖到我的DBML文件，并尝试和c它返回从我的$ C $称之为

I am trying to call a stored procedure with Linq, the stored procedure returns the value fine in SQL, but when I drag it onto my DBML file and try and call it from my code it is returning the

的找不到源类型'诠释'的查询模式的实现。 选择未找到。的

我看着其他线程和其他存储过程我有，由于某种原因，而不是使用 ISingleResult 这是不同的，我似乎无法改变的返回类型无论是。

I've looked at other threads and other stored procedures I have and for some reason rather than using an ISingleResult this is different and I cant seem to change the Return Type either.

这是背后

[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.displayDetails")]
public int displayDetails([global::System.Data.Linq.Mapping.ParameterAttribute(DbType="VarChar(1)")] string sex, [global::System.Data.Linq.Mapping.ParameterAttribute(DbType="Int")] System.Nullable<int> day, [global::System.Data.Linq.Mapping.ParameterAttribute(DbType="Int")] System.Nullable<int> month, [global::System.Data.Linq.Mapping.ParameterAttribute(DbType="Int")] System.Nullable<int> year, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="PostCode", DbType="VarChar(10)")] string postCode, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="AppTime", DbType="DateTime")] System.Nullable<System.DateTime> appTime, [global::System.Data.Linq.Mapping.ParameterAttribute(DbType="Int")] System.Nullable<int> filter)
{
        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), sex, day, month, year, postCode, appTime, filter);
        return ((int)(result.ReturnValue));
}

我的页面code这是越来越误差

My page code which is getting the error

    var person = from p in db.displayDetails(sex.ToString(),
                  Convert.ToInt32(dayOfBirth),
                  Convert.ToInt32(monthOfBirth),
                  Convert.ToInt32(yearOfBirth),
                  postCode.ToString(),
                  Convert.ToDateTime(appointmentTime),
                  Convert.ToInt32(resultType))
                  select person;
        {
            p.Forename,
            p.Surname,
            p.AppointmentTime,
            p.Location
        };

        foreach (var record in person)
        {
            lblName.Text = record.Forename + " " + record.Surname;
            lblAppointmentTime.Text = record.AppointmentTime.ToString();
            lblWaitIn.Text = record.Location;
        }   

这是如何得到这个解决将是AP preciated任何帮助。

Any help on how to get this resolved would be appreciated.

更新 - 这里是我的SP：

Update - Hi, here is my SP:

@sex varchar (1),
@day int,
@month int,
@year int,
@PostCode varchar (10),
@AppTime DateTime,
@filter int

AS
BEGIN

declare @sql nvarchar(max)
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here

Select @Sql = 'SELECT DAY(DateOfBirth) AS DayOfBirth, MONTH(DateOfBirth) AS MonthOfBirth, 
YEAR(DateOfBirth) AS YearOfBirth, DateOfBirth, Sex, AppointmentTime, SchdlRefno, Forename, 
Surname, ClinicCode, Ur, PostCode, Specialty, Location
FROM dbo.tbl_Appointments'


if @filter  = 1
-- show search by just sex, day and month as no more needed for match
Select @sql = @Sql + ' WHERE (DAY(DateOfBirth) = ' + convert(varchar, @day, 103) + ') AND (MONTH(DateOfBirth) = ' + convert(varchar, @month, 103) + ') AND (Sex = ''' + @sex + ''')' 

if @filter  = 2

-- show search by sex, day and month and postcode

Select @sql = @Sql + ' WHERE (DAY(DateOfBirth) = ' + convert(varchar, @day, 103) + ') AND (MONTH(DateOfBirth) = ' + convert(varchar, @month, 103) + ') and (YEAR(DateOfBirth) = ' + convert(varchar, @year, 103) + ') AND (Sex = ''' + @sex + ''') and (postcode = ''' + @postcode + ''')' 

if @filter  = 3

-- show search by sex, day and month, postcode and appointment time

Select @sql = @Sql + ' WHERE (DAY(DateOfBirth) = ' + convert(varchar, @day, 103) + ') AND (MONTH(DateOfBirth) = ' + convert(varchar, @month, 103) + ') and (YEAR(DateOfBirth) = ' + convert(varchar, @year, 103) + ') AND (Sex = ''' + @sex + ''') and (postcode = ''' + @postcode + ''') and (AppointmentTime = ''' + convert(varchar, @AppTime, 121) + ''')' 

print @sql  

Exec sp_executesql  @sql

END

所以，通过即时消息传递一个过滤器带回不同的搜索结果。

So, im passing a filter through to bring back different search results.

感谢

推荐答案

问题是你的函数的结果是不是一个集合，它是一个标值（ INT ），因此不能投影

The problem is the result of your function isn't a collection, it's a scalar value (int) therefore can't be projected.

这篇关于LINQ的存储过程返回发出─一个int的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！