c# - 实体框架无对应列

问题描述

我一直在与这个问题作斗争，我从一台机器上修复它，合并到 GIT 并拉到另一台机器上（具有相同的数据库），错误再次出现。对于这种特殊情况，我无法弄清楚。

我有一个返回以下内容的存储过程：

当我运行我的代码并点击此端点时，我收到一个错误：

数据读取器与此处指定的我的复杂类型不兼容。'CUSTOMER_ID' 类型的成员在数据读取器中没有同名的对应列。

我不明白为什么它不起作用，我有其他非常相似的程序，并且一切正常。我的复杂类型设置为ADD_CUSTOMER_JSON_Result并且是一个名为 int32 的标量CUSTOMER_ID。我的存储过程的功能导入ADD_CUSTOMER_JSON设置为该复杂类型。

我的模型的这个存储过程的上下文 CS 是

public virtual ObjectResult<ADD_CUSTOMER_JSON_Result> ADD_CUSTOMER_JSON(Nullable<int> cOMPANY_ID, string cUSTOMER_NAME, string fIRST_NAME, string lAST_NAME, string aDDRESS_1, string aDDRESS_2, Nullable<int> cITY_ID, Nullable<int> sTATE_ID, Nullable<int> zIP_CODE_ID, string pRIMARY_PHONE_NUMBER, string aLTERNATE_PHONE_NUMBER, string sECONDARY_PHONE_NUMBER, string eMAIL_ADDRESS, string cUSTOMER_NOTES, string mODIFIED_BY, ObjectParameter cUSTOMER_ID)
{
    var cOMPANY_IDParameter = cOMPANY_ID.HasValue ?
                new ObjectParameter("COMPANY_ID", cOMPANY_ID) :
                new ObjectParameter("COMPANY_ID", typeof(int));
    
    var cUSTOMER_NAMEParameter = cUSTOMER_NAME != null ?
                new ObjectParameter("CUSTOMER_NAME", cUSTOMER_NAME) :
                new ObjectParameter("CUSTOMER_NAME", typeof(string));
    
    var fIRST_NAMEParameter = fIRST_NAME != null ?
                new ObjectParameter("FIRST_NAME", fIRST_NAME) :
                new ObjectParameter("FIRST_NAME", typeof(string));
    
    var lAST_NAMEParameter = lAST_NAME != null ?
                new ObjectParameter("LAST_NAME", lAST_NAME) :
                new ObjectParameter("LAST_NAME", typeof(string));
    
    var aDDRESS_1Parameter = aDDRESS_1 != null ?
                new ObjectParameter("ADDRESS_1", aDDRESS_1) :
                new ObjectParameter("ADDRESS_1", typeof(string));
    
    var aDDRESS_2Parameter = aDDRESS_2 != null ?
                new ObjectParameter("ADDRESS_2", aDDRESS_2) :
                new ObjectParameter("ADDRESS_2", typeof(string));
    
    var cITY_IDParameter = cITY_ID.HasValue ?
                new ObjectParameter("CITY_ID", cITY_ID) :
                new ObjectParameter("CITY_ID", typeof(int));
    
    var sTATE_IDParameter = sTATE_ID.HasValue ?
                new ObjectParameter("STATE_ID", sTATE_ID) :
                new ObjectParameter("STATE_ID", typeof(int));
    
    var zIP_CODE_IDParameter = zIP_CODE_ID.HasValue ?
                new ObjectParameter("ZIP_CODE_ID", zIP_CODE_ID) :
                new ObjectParameter("ZIP_CODE_ID", typeof(int));
    
    var pRIMARY_PHONE_NUMBERParameter = pRIMARY_PHONE_NUMBER != null ?
                new ObjectParameter("PRIMARY_PHONE_NUMBER", pRIMARY_PHONE_NUMBER) :
                new ObjectParameter("PRIMARY_PHONE_NUMBER", typeof(string));
    
    var aLTERNATE_PHONE_NUMBERParameter = aLTERNATE_PHONE_NUMBER != null ?
                new ObjectParameter("ALTERNATE_PHONE_NUMBER", aLTERNATE_PHONE_NUMBER) :
                new ObjectParameter("ALTERNATE_PHONE_NUMBER", typeof(string));
    
    var sECONDARY_PHONE_NUMBERParameter = sECONDARY_PHONE_NUMBER != null ?
                new ObjectParameter("SECONDARY_PHONE_NUMBER", sECONDARY_PHONE_NUMBER) :
                new ObjectParameter("SECONDARY_PHONE_NUMBER", typeof(string));
    
    var eMAIL_ADDRESSParameter = eMAIL_ADDRESS != null ?
                new ObjectParameter("EMAIL_ADDRESS", eMAIL_ADDRESS) :
                new ObjectParameter("EMAIL_ADDRESS", typeof(string));
    
    var cUSTOMER_NOTESParameter = cUSTOMER_NOTES != null ?
                new ObjectParameter("CUSTOMER_NOTES", cUSTOMER_NOTES) :
                new ObjectParameter("CUSTOMER_NOTES", typeof(string));
    
    var mODIFIED_BYParameter = mODIFIED_BY != null ?
                new ObjectParameter("MODIFIED_BY", mODIFIED_BY) :
                new ObjectParameter("MODIFIED_BY", typeof(string));
    
    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<ADD_CUSTOMER_JSON_Result>("ADD_CUSTOMER_JSON", cOMPANY_IDParameter, cUSTOMER_NAMEParameter, fIRST_NAMEParameter, lAST_NAMEParameter, aDDRESS_1Parameter, aDDRESS_2Parameter, cITY_IDParameter, sTATE_IDParameter, zIP_CODE_IDParameter, pRIMARY_PHONE_NUMBERParameter, aLTERNATE_PHONE_NUMBERParameter, sECONDARY_PHONE_NUMBERParameter, eMAIL_ADDRESSParameter, cUSTOMER_NOTESParameter, mODIFIED_BYParameter, cUSTOMER_ID);
}

我在我的控制器中调用这个存储过程：

[HttpPost, Authorize]
public ObjectResult<ADD_CUSTOMER_JSON_Result> CreateCustomer([FromBody]dynamic customerInput)
{
    var identity = (ClaimsIdentity)User.Identity;
    var claimRole = identity.Claims.ToList()[0].Value.ToInt();
    var claimCompany = identity.Claims.ToList()[3].Value.ToInt();
    var claimUsername = identity.Claims.ToList()[5].Value.ToString();
    int[] allowedRoles = new int[] { 1, 2, 3 , 8};

    if (Array.IndexOf(allowedRoles, claimRole) != -1)
    {
        if (claimRole == 3 && claimCompany != customerInput["companyID"].ToString().ToInt())
        {
             throw new HttpResponseException(new HttpResponseMessage(HttpStatusCode.Unauthorized));
        }

        var companyID = (int)customerInput["COMPANY_ID"];
        var customerName = customerInput["CUSTOMER_NAME"].ToString();
        var firstName = customerInput["FIRST_NAME"].ToString();
        var lastName = customerInput["LAST_NAME"].ToString();
        var address_1 = customerInput["ADDRESS_1"].ToString();
        var address_2 = customerInput["ADDRESS_2"].ToString();
        var cityID = (int)customerInput["CITY_ID"];
        var stateID = (int)customerInput["STATE_ID"];
        var zipID = (int)customerInput["ZIP_CODE_ID"];
        var primaryPhoneNumber = customerInput["PRIMARY_PHONE_NUMBER"].ToString();
        var alternatePhoneNumber = customerInput["ALTERNATE_PHONE_NUMBER"].ToString();
        var secondaryPhoneNumber = customerInput["SECONDARY_PHONE_NUMBER"].ToString();
        var emailAddress = customerInput["EMAIL_ADDRESS"].ToString();
        var customerNotes = customerInput["CUSTOMER_NOTES"].ToString();
        var modifiedBy = claimUsername;

        ObjectParameter output = new ObjectParameter("cUSTOMER_ID", typeof(int));
        var test =  database.ADD_CUSTOMER_JSON(companyID, customerName, firstName, lastName, address_1, address_2, cityID, stateID, zipID, primaryPhoneNumber, alternatePhoneNumber, secondaryPhoneNumber, emailAddress, customerNotes, modifiedBy, output);
        return test;
    }

    throw new HttpResponseException(new HttpResponseMessage(HttpStatusCode.Unauthorized));
}

我对 EF 不是超级好，但我对任何其他 SP 都没有这么多问题，所以我有点沮丧。

我只是不明白为什么当有一个列设置为输出时它说我没有相应的列，并返回它应该期望的名称。实际的复杂类型模型看起来也很正常，它只有一个属性，它是 int named CUSTOMER_ID。如果我将控制器函数更改为返回一个 int，并将功能导入更改为 none，它将起作用但始终返回 -1，如果我将功能导入更改为标量然后 int32，它会再次抱怨缺少列。

让我知道我可以在哪里澄清任何事情。此外，我尝试将 SP 更改为没有返回值，并且只返回 @CUSTOMER_ID 但是，这并没有改变任何东西。

编辑另外，另一个我无法回答的奇怪现象是，当我将大多数存储过程添加到实体时，如果它们有返回，它将自动创建一个复杂类型并通过功能导入引用它。这个特定的没有，当我“更新”我的存储过程并添加这个时，它会进行功能导入，但将其设置为无，我必须手动创建复杂类型。我认为这很有趣，但我对实体的了解在该领域缺乏一点，所以我不完全理解为什么会发生这种情况。

标签： c#entity-frameworkapistored-procedures

c# - 实体框架无对应列

问题描述

解决方案

推荐阅读