实体框架在使用临时表调用存储过程时返回null，即使在设置了datacontract之后也是如此

Question

我有一个存储过程，如下所示。请参阅第1=2行用于设置数据上下文。我从这里的一篇帖子中读到了这篇文章。在我的C#代码中，我调用这个存储过程如下。我可以在数据库中很好地看到存储过程的结果。我可以在数据库中看到2条结果记录。

但是从下面的C#代码调用返回计数2，但两条记录都为null和0。请帮忙。

public class UnreadAlerts
{
        public string alertName { get; set; }
        public int alertValue { get; set; }
}
public List<UnreadAlerts> AllUnreadAlertsCount(int userId)
{
    List<UnreadAlerts> Sdetails = new List<UnreadAlerts>();
    using (var context1 = new AltusEntities())
    {
        Sdetails = context1.Database.SqlQuery<UnreadAlerts>("[dbo].[sp_UnreadAlertsCount] @userid",
                new SqlParameter("@userid", userId)
                ).ToList();
    }
    return Sdetails;
}

存储过程代码：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[sp_UnreadAlertsCount]  
    @userId int  
AS
BEGIN
    IF 1 = 2 
    BEGIN
        SELECT
            cast(null as nvarchar(50))  as Aname
            ,cast(null as int)    as Avalue
        WHERE
            1 = 2  
    END
    CREATE TABLE #Results  
    (  
        Aname nvarchar(50),  
        Avalue int  
    )  
    INSERT INTO #Results (Aname, Avalue)  
        SELECT
            'ProximityAlerts',
            COUNT(DISTINCT ProximityMeetingAlertID) 
        FROM
            AltusRt.dbo.ProximityMeetingAlerts pma  
        JOIN
            AltusRt.dbo.ProximityMeetings pm ON pma.ProximityMeetingID = pm.ProximityMeetingID  
        WHERE
            pm.RequestID IN (SELECT r.RequestId 
                             FROM Users u 
                             JOIN Requests r ON u.UserId = r.CreatedByUserId
                             WHERE u.UserId = @userId 
                               AND r.CreatedByUserId = @userId)  
        INSERT INTO #Results (Aname,Avalue)  
            SELECT
                'MonitorAlerts',
                COUNT(*) 
            FROM 
                dbo.TargetGeoFenceResult 
            WHERE
                ActiveStatus = 1  
        SELECT Aname, Avalue 
        FROM #Results
    END

Answer 1

列名和属性名不匹配。因此，这些值从未复制到UnreadAlerts实例中，并且您将看到默认值int和string。

现在您可以通过更改属性名称来修复它：

public class UnreadAlerts
{
    public string Aname{ get; set; }
    public int Avalue { get; set; }
}

或者，通过与[ColumnAttribute]:混叠

public class UnreadAlerts
{
    [Column(Name = "Aname")]
    public string alertName { get; set; }
    [Column(Name = "Avalue")]
    public int alertValue { get; set; }
}