使用JOIN子句查询始终使用Dapper返回空结果集

Query with JOIN clause always returns a empty result set using Dapper
2020-10-17
  •  译文(汉语)
  •  原文(英语)

我试图使用dapper进行以下查询,但它始终返回空结果集.首先,我尝试删除该WHERE子句以隔离问题,但这没有用.之后,我向该子句中的C.NAME列添加了别名,SELECT但是也没有用.

private const string SelectClaims =
 @"SELECT C.NAME FROM CLAIMS C 
  INNER JOIN USERS_CLAIMS UC ON C.ID = UC.ID_CLAIM 
  WHERE UC.ID_USER = @Id";

using (var conn = new FbConnection(connectionString))
{
    var claims = conn.Query<string>(SelectClaims, new { user.Id });
    return claims;
}

如果我为此替换上面的查询,那么一切正常:

SELECT NAME FROM CLAIMS
速聊1:
传递特定参数时,此查询是否可以在SQL Management Studio中运行?
速聊2:
哈K 是的,它有效.我正在使用Firebird数据库和"MiTeC Interbase查询"直接访问和查询目标数据库.
解决过程1

老实说,我不确定您是否正确使用了Dapper,因为您正在选择命名列并将其映射为简单列string-我相信Dapper不会看到'Name'属性,因为它会静默失败.我猜您应该尝试Query<T>使用强类型对象或使用它Query<dynamic>来避免不必要的类创建.

速聊1:
哈amo再见!我直接使用该字符串是因为在当前上下文中它更有意义.但是即使使用动态对象或使用具有"名称"属性的自定义对象,我也无法使用它.
解决过程2

因此,我把它放在一边,去做其他事情,然后我回来尝试解决我的问题,一切都很好.我没有更改代码中的任何内容,令人惊讶的是它现在可以正常工作.

我不知道MiTeC Interbase查询中的挂起事务是否可能阻止我查看数据库中的当前记录.我尝试再次模拟它,现在它总是返回我需要的记录(最好,什么都没有,呵呵).

为了澄清起见,使用字符串作为返回数据类型,在sql参数中简单地联接到Query方法或根本不对返回列使用别名(仅当所有列都与您的C#属性名称匹配时才是)是非常好的选择或者您只有一列指向像我这样的字符串).

I trying to do the following query using dapper but its always returning a empty result set. First I tried to remove the WHERE clause in order to isolate the problem but that didn't work. After that I added a alias to the C.NAME column in the SELECT clause but didn't work either.

private const string SelectClaims =
 @"SELECT C.NAME FROM CLAIMS C 
  INNER JOIN USERS_CLAIMS UC ON C.ID = UC.ID_CLAIM 
  WHERE UC.ID_USER = @Id";

using (var conn = new FbConnection(connectionString))
{
    var claims = conn.Query<string>(SelectClaims, new { user.Id });
    return claims;
}

If I replace the query above for this right here everything works fine:

SELECT NAME FROM CLAIMS
Talk1:
Does this query work e.g. in SQL Management Studio when you pass specific parameter?
Talk2:
Hello @Kamo! Yes, it works. I'm using a Firebird database and the 'MiTeC Interbase Query' to directly access and query the target database.
Solutions1

To be honest I am not sure if you are using Dapper properly since you are selecting named column and mapping it to simple string - I believe Dapper doesn't see 'Name' property as fails silently. I guess you should try either Query<T> with strongly typed object or use Query<dynamic> to avoid unnecessary class creation.

Talk1:
Hello again Kamo! I'm using the string directly because it makes more sense in my current context. But even with a dynamic or using a custom object with a 'Name' property I can't work with that.
Solutions2

So, I put this aside and go do something else and after I came back to try to solve my problem everything was working fine. I didn't change anything in my code and surprisingly its working right now.

I don't know if is possible that a pending transaction in my MiTeC Interbase Query was blocking me to see the current records from the database. I try to simulate this again and now its always returning the records that I need (better that than nothing, hehe).

For clarification, its perfect fine to use a string as the returning data type, do a simple join in sql parameter to a Query method or don't use a alias for the returning column at all (only if all columns matches your C# property names or you just have a column directing to a string like me).

转载于:https://stackoverflow.com/questions/30838271/query-with-join-clause-always-returns-a-empty-result-set-using-dapper

本人是.net程序员,因为英语不行,使用工具翻译,希望对有需要的人有所帮助
如果本文质量不好,还请谅解,毕竟这些操作还是比较费时的,英语较好的可以看原文

留言回复
我们只提供高质量资源,素材,源码,坚持 下了就能用 原则,让客户花了钱觉得值
上班时间 : 周一至周五9:00-17:30 期待您的加入