当我在动态过程名后使用ToList时出现错误

本文关键字：ToList 错误 动态 过程 | 更新日期: 2023-09-27 18:12:42

我总是使用返回行数，使用foreach循环。在项目的一部分，我需要得到动态数据的过程。程序代码:

ALTER procedure [dbo].[abcde]
@fldStatusIdString nvarchar(max)
as
begin    
    declare @Query nvarchar(max)
    set @Query = '
    SELECT a.fldEstateId, a.fldEstateTitle, a.fldThumbnail,a.fldPrice,fldAcceptDate
    FROM    (
                SELECT dbo.tblImages.fldEstateId, dbo.tblImages.fldThumbnail, dbo.tblEstates.fldEstateTitle,
                 dbo.tblEstates.fldPrice, dbo.tblEstates.fldAcceptDate,  ROW_NUMBER() OVER (PARTITION BY dbo.tblImages.fldEstateId 
                ORDER BY dbo.tblEstates.fldAcceptDate) AS RowNumber
                FROM    dbo.tblImages INNER JOIN    dbo.tblEstates ON dbo.tblEstates.fldEstateId = dbo.tblImages.fldEstateId
                WHERE   (dbo.tblEstates.fldStatusOfAccept = 1)'
                set @Query +='and (fldStatusId in('+@fldStatusIdString+'))
            ) AS a
    WHERE   a.RowNumber = 1
    order by fldEstateId        
    '        
    execute sp_executesql  @Query
end

使用上述过程时，不能在foreach循环中使用ToList。如果正常过程没有问题，但如果我使用上述方式(输入具有变量数量的数据)，则会出现错误。c#代码:

List<Join_Estates_Images_Model> ListAll = new List<Join_Estates_Images_Model>();
foreach (var item in db.abcde(fldStatusIdString).ToList())
{
    Join_Estates_Images_Model _entity = new Join_Estates_Images_Model();
    _entity.fldEstateId = item.fldEstateId;
    _entity.fldEstateTitle = item.fldEstateTitle;
    _entity.fldThumbnail = item.fldThumbnail;
    _entity.fldPrice = Convert.ToDecimal(item.fldPrice);
    _entity.fldAcceptDate = item.fldAcceptDate;
    ListAll.Add(_entity);
}
return ListAll;

错误信息:

Error    14    'int' does not contain a definition for 'ToList' and no extension method 'ToList' accepting a first argument of type 'int' could be found (are you missing a using directive or an assembly reference?)

我用的是using System。Linq的

当我在动态过程名后使用ToList时出现错误

另一种方法是将逗号分隔的列表传递给变量，并在过程中处理它，例如......

这里也不需要动态sql .....

ALTER procedure [dbo].[abcde]
@fldStatusIdString nvarchar(max)
as
begin    
  DECLARE @xml XML;
  SET @xml = N'<t>' + REPLACE(@fldStatusIdString,',','</t><t>') + '</t>';
    SELECT a.fldEstateId, a.fldEstateTitle, a.fldThumbnail,a.fldPrice,fldAcceptDate
    FROM    (
                SELECT dbo.tblImages.fldEstateId
                    , dbo.tblImages.fldThumbnail
                    , dbo.tblEstates.fldEstateTitle
                    , dbo.tblEstates.fldPrice
                    , dbo.tblEstates.fldAcceptDate
                    ,  ROW_NUMBER() OVER (PARTITION BY dbo.tblImages.fldEstateId 
                                          ORDER BY dbo.tblEstates.fldAcceptDate) AS RowNumber
                FROM    dbo.tblImages 
                INNER JOIN    dbo.tblEstates 
                ON dbo.tblEstates.fldEstateId = dbo.tblImages.fldEstateId
                WHERE   (dbo.tblEstates.fldStatusOfAccept = 1)
               and (fldStatusId in(
                                   SELECT  r.value('.','varchar(MAX)') as item
                                   FROM  @xml.nodes('/t') as records(r)
                                   )
                    )
            ) AS a
    WHERE   a.RowNumber = 1
    order by fldEstateId        
end

我在实体框架中遇到了同样的问题。实体框架总是给出一个带有动态查询的整数结果集。请按照以下步骤操作

更新你的模型

  1. 删除.edmx.
  2. 删除结果集和相关功能。
  3. 更新您的程序。使用非动态查询生成结果集。
  4. 再次在实体中添加过程。
相关文章：