c# - 在 sql 中运行但不在 .Net 中运行的查询

Question

我正在尝试在返回单个记录的 c# .net 代码中编写 sql 查询。当我使用提供的参数在 sql 中执行时它运行正确，但从.Net 代码执行时不返回任何行。不确定是什么问题 ？下面的代码解释了控制流程

.Net 代码

 public class FedExciseExpense
{
    public float DomicileId { get; set; }
    public float CoveragePolicyTypeId { get; set; }
    public bool Is953D { get; set; }
    public decimal? Value { get; set; }
}


public List<FedExciseExpense> GetFedExciseTaxExpenses(int industryId, int domicileId,int coveragePolicyTypeId, bool is953D)
        {
            var sql = @"select distinct gm.geographyBaseId as DomicileId, fedt.is953d as Is953D, fedt.coveragePolicyTypeId as CoveragePolicyTypeId, fedt.value as Value
                                from fedExciseTax fedt
                                join GeographyGroup gg on gg.geographyGroupId = fedt.geographyGroupId
                                join GeographyName gn on gn.geographyGroupId = gg.geographyGroupId and gn.geographyNameId = fedt.geographyNameId
                                join GeographyMap gm on gm.geographyNameId = gn.geographyNameId
                                join industryMap iMatch on iMatch.industryNameId = fedt.industryNameId
                                join industryMap iInput on iInput.industryBaseId = iMatch.industryBaseId
                                     where gm.geographyBaseId = @domicileId and iInput.industryNameId = @industryId and fedt.coveragePolicyTypeId = @coveragePolicyTypeId and fedt.is953d = @is953D";

            var param = new { domicileId, industryId, coveragePolicyTypeId , is953D };

            return WithCache(sql, param, () =>
            {
                using (var conn = ConnectionFactory.GetConnection())
                {
                    conn.Open();

                    var data = conn.Query(sql, param).Select(dyn => new FedExciseExpense()
                    {
                     DomicileId = (int)dyn.domicileId,
                    CoveragePolicyTypeId = (int)dyn.coveragePolicyTypeId,
                    Is953D = (int)dyn.Is953d ,
                    Value = (decimal?)dyn.Value
                    }).ToList();

                    conn.Close();
                    return data;
                }
            });
        }



  private static T WithCache<T>(string query, object parameters, Func<T> execution)
        {
            var key = query;
            if (parameters != null)
            {
                key += "@" + JsonConvert.SerializeObject(parameters, Formatting.None);
            }
            return (T)QueryCache.GetOrAdd(key, _ => execution());
        }


Actual query 

    declare @domicileId int = 69,
            @industryId int = 52,
            @coveragePolicyTypeId int = 1,
            @is953D int = 1

select distinct gm.geographyBaseId as DomicileId, fedt.is953d as Is953D, fedt.coveragePolicyTypeId as CoveragePolicyTypeId, fedt.value as Value                                  from fedExciseTax fedt                                  join GeographyGroup gg on gg.geographyGroupId = fedt.geographyGroupId                                  join GeographyName gn on gn.geographyGroupId = gg.geographyGroupId and gn.geographyNameId = fedt.geographyNameId                                  join GeographyMap gm on gm.geographyNameId = gn.geographyNameId          join industryMap iMatch on iMatch.industryNameId = fedt.industryNameId                                  join industryMap iInput on iInput.industryBaseId = iMatch.industryBaseId                                       
where gm.geographyBaseId = @domicileId and iInput.industryNameId = @industryId and fedt.coveragePolicyTypeId = @coveragePolicyTypeId and fedt.is953d = @is953D

核心价值

下面是在WithCache方法中初始化的key的值

select distinct gm.geographyBaseId as DomicileId, fedt.is953d as Is953D, fedt.coveragePolicyTypeId as CoveragePolicyTypeId, fedt.value as Value
                                from fedExciseTax fedt
                                join GeographyGroup gg on gg.geographyGroupId = fedt.geographyGroupId
                                join GeographyName gn on gn.geographyGroupId = gg.geographyGroupId and gn.geographyNameId = fedt.geographyNameId
                                join GeographyMap gm on gm.geographyNameId = gn.geographyNameId
                                join industryMap iMatch on iMatch.industryNameId = fedt.industryNameId
                                join industryMap iInput on iInput.industryBaseId = iMatch.industryBaseId
                                     where gm.geographyBaseId = @domicileId and iInput.industryNameId = @industryId and fedt.coveragePolicyTypeId = @coveragePolicyTypeId and fedt.is953d = @is953D@{"domicileId":69,"industryId":52,"coveragePolicyTypeId":0,"is953D":1}

只接受不带参数的查询的查询

private static List<T> RunQuery<T>(string query, object parameters = null)
        {
            return WithCache(query, parameters, () => RunNoCacheQuery<T>(query, parameters));
        }


        private static List<T> RunNoCacheQuery<T>(string query, object parameters = null)
        {
            using (var conn = ConnectionFactory.GetConnection())
            {
                conn.Open();
                var data = conn.Query<T>(query, parameters).ToList();
                conn.Close();
                return data;
            }
        }

标签： c#sql.net