Dapper: 支持基元类型的映射方案？(why don't support the primitive type for the Command-Mapping-Cache?)

[NMSAzulX]

举例，For example:
Use:
string sql = "select * from A where name=@name and age=@age";
PrimitiveQuery(sql ，"Jim", 18);
Method:
PrimitiveQuery(string，params object[] parameters);

[mgravell]

can you clarify what PrimitiveQuery would do differently here? You can already do things like:

var dynamicRows = conn.Query(sql, new { name = "Jim", Age = 18 }); // IEnumerable<dynamic>

or

var typedRows = conn.Query<YourAType>(sql, new { name = "Jim", Age = 18 }); // IEnumerable<YourAType>

So; what would PrimitiveQuery do here? as for the parameters: the reason we can't really use object[] parameters is because they need to be named; otherwise how would it know to add a parameter named @name or @Age ?

[NMSAzulX]

@mgravell 谢谢老铁的回复(Thanks for your reply. ）

使用封装类型和直接使用内置类型是有所不同的。
实现以及优点：

1、在映射效率上，类与结构体需要通过反射、需要解析语法糖才能实现参数到Sql语句的映射.但我们知道Sql语句是固定的，其参数化顺序也是固定的，params object[] parameters 作为函数的参数也可以按照顺序加载，而且可以直接进行索引操作，比起类与结构体的方案，这样更加高效。

2、在使用方式上，相对于参数化较少的Sql语句来说，我觉得 “Jim”,18 比起 new { name="Jim", Age=18} 更加方便，更加直观。

3、另外在规范的编程上，开发者有必要知道自己传参的顺序，因此将sqlstring作为key, 然后将映射方法存储起来。实际上，我分为两种模式：
一种就是解析类与结构体的映射方法，GetEmitCommandGenericCache(string sql, T value).
另一种就是解析内置类型的GetEmitCommandCache(string sql, object[] values)。

4、值类型属于特殊类型，而且Core2.1提供了“in”关键字,限 值类型，当参数进入映射方法内部时只有读取操作，因此就基元类型而言甚至对此可以再优化出一种映射方案。

缺点：

正如老铁所说，涉及到方法重命名，降低开发者体验，而且这样做可能也只是带来微秒级的提升，大量开发以及兼容工作仅仅带来了很微弱的性能提升。

其他：

Dapper在性能和可靠性上做得十分出色，我十分喜欢Dapper这个项目，希望我的一些建议没有给团队带来困扰。

希望中国的老铁们能支持翻译/纠正，2级英文水平实在有限- -，还好Github是匿名的..
(search for translation.........)

It is different from using the object(class/struct) and built-in type directly.
Realization and advantages：

1. The efficiency of the mapping method. Dapper-Now need reflection and parsing some grammatical sugar to map the properties of one instance to the Sql-String for making the Sql-Command-Cache. Besides, I think there is an other Mapping-Cache also can be used. Actually, the Sql-String is fixed, the order of Parameterization is fixed. So, the Params-Object[]-Parameters can be loaded in sequence as the parameters of Command-method.

2. On use. If there is not a lot of work on parameterization, ' "Jim",18' is more intuiti and more convenient than using Anonymous-Class.

3. Programming Confidentiality. Coder know the parameters' order of any methods. So we can use Sql-String as the key of Commnad-Method. Actually, there are two kinds of methods for implementations.
   One: Parsing the class & struct.
   Such as "GetEmitCommandGenericCache(string sql, T value)".
   Another:Parsing the built-in type.
   Such as GetEmitCommandCache(string sql, object[] values).

4. C# new feature. The Core2.1........Emmmmm....C#7.2 provided "in" keyword. No copy on stack and readonly, only value types. And there is no write operation for the parameters in the function. So maybe some scheme can be optimized for the primitive type parameter.

Deficiencies：

LaoTie: You (Used for calling friends in China )
As LaoTie said. They need to be named. And will reducing the developer experience. A lot of Development/Compatible work just improve insensitive.（μs）

Other:
Dapper has done very well in performance and reliability, I like this project very much!
I hope it's getting better and better.

[mgravell]

1. efficiency here is a false concern; the work involved in parsing (either via simple means like regex, or as a full parser) a command-text to understand the parameter naming/order would be significantly more than the cost of reflection. Note also that the reflection strategy is cached and re-used: it doesn't happen per call. Additionally, dapper works against arbitrary ado.net providers, which means we do not have a specific SQL variant in mind (in theory the command text doesn't even need to be SQL, as long as the ado.net provider understands it). Parsing arbitrary ado.net commands is not a robust or reliable approach.

2. Now consider that your command takes three strings. Which is clearer? "foo", "bar", "blap"? Or { From = ""to", By="bar", To="blap" }? I think: the second. Note that it doesn't even need to be an anonymous type - regular classes etc work just fine too.

3. I don't know what you're saying there. Can you clarify?

4: this is an entirely separate topic; passing a composed struct is completely different to passing an object array, and there are several complicating factors:

• to be passed without boxing, the API would need to be generic in the args type; this cannot be conveniently hacked into the existing query API because C# does not support partial generic type inference - it is all-or-nothing. So you would have to specify Query<Customer, YourArgsType> which is ugly; the alternative would be a fluent API such as WithArgs(yourValType).Query<Customer>() (using generic type inference for the first) - achievable, but a large API change.
• don't be confused about what in is and isn't; it is essentially ref with some implied semantics. The main time that is useful is when the code inside is immediate and synchronous - data access is rarely immediate, and is increasingly asynchronous, which means that in many cases you'd absolutely end up with at least another stack copy, but more likely a heap copy (for an async continuation). While in is very interesting for some scenarios, it isn't actually useful here
• in is only optimised in the case of readonly struct; in other cases, the compiler actually does a stack copy before the call, then loads the managed reference to the copy.
• most people don't want to have to declare a custom type for their args; an anonymous type is perfect for their needs, and would find having to declare a read-only struct tedious
• it would have been nice if "value tuples" solved this, bit unfortunately "value tuples" don't expose the required metadata to downstream libraries (only to upstream callers). If "value tuples" solved this problem, I would probably have added the fluent API in some way; I will revisit this as-and-when better options arrive

Additionally, there are problems relating to whether such a parameter would be params, because params must be the last parameter, but we currently have additional optional parameters we would wish to convey.

So: there are lots of problems with the object[] suggestion, and I don't actually see any benefits, and I see lots of problems.

Maybe I'm overlooking something. If I have, please let me know, but : as it stands, this is not a change I would wish to make.

[NMSAzulX]

@mgravell

For second and third points, I describe the following:

For example:
```C#
string sql = "SELECT * FROM A WHERE Name=@Name and Age>@Age"
PrimitiveQuery(sql, InstanceA.Name, InstanceB.MinAge).

MappingMethodCache:

Runtime :
1. Get "@Name","@Age"  etc from Sql-String.（Regex）
2. CreateParameter  ( IDbCommand.CreateParameter())
3. Loop the MatchCollection (for(int index))
4. Get Element (object[index])
5. Get Sqltype And Fill the DbType (object[index].Type)
6. Fill the ParameterName 
7. If type is PrimitiveType，Assign directly.（loadarray[index]）
8. If type is not PrimitiveType, cheking null and assignment.
9. Add SqlParameter (IDbCommand.Parameters.Add(..))

Emit:
```C#
IDbCommand as the arg1.
object[] as the arg2.
SqlString we have known.

arg1.CommandText = SqlString.
TempParameter = arg1.CreateParameter().
TempParameter.DbType = Type (GotFromRuntime DbType.Int32).
TempParameter.ParameterName=String (GotFromRuntime like @Age).

//PrimitiveType like int,short,float,double etc.
TempParameter.Value = arg2[Index](GotFromRuntime like Ldelem Index).

//OtherType like string ...
if(arg2[Index]!=null){
    TempParameter.Value = arg2[Index] (GotFromRuntime like Ldelem Index).
}else{
    TempParameter.Value = DbNull.Value.
}
// And we can use the ‘dup’ to optimize the code above.
// It don't support the 'type?' 

[mgravell]

Yes it is theoretically possible to parse out ordered names via some pattern, but I don't see that it has any advantage of doing so versus the current approach, and IMO it has many disadvantages.

What clear advantages do you see?

[NMSAzulX]

I'm sorry to reply to you so late.

I did some webapi projects. There are a lot of simple SQL query in projects.

_Such as GetItemByCondition(type condition) :_
GetStudentsById(int id)
GetStudentsByScore(double score)
...etc

When I using dapper to code I must write { sid = id }、{ student_score = score } .
Like this: 'Query(sqlstring, {sid=id})';
It must be specify the name of the property.
And we must change the 'sid' if the name of the property is changed.

If we use 'PrimitiveQuery(sqlstring, id)'.
The code is very concise.
We would do nothing for the changing of the name.

Although you say performance is not a concern, it does improve performance.

[mgravell]

We would do nothing for the changing of the name.

Personally I think it is perfectly reasonable to expect to have to change
code if somebody renamed the parameters.

Although you say performance is not a concern, it does improve performance.

What makes you say that? Based on what?

(Edit: remove email footer junk)

[sgf]

@NMSAzulX
use Anonymous Type object is better to manage the parameters ,and the Dapper can be more easyly got the Object members name as the SQL variable name.

[sgf]

@NMSAzulX
PrimitiveQuery(string，params object[] parameters);
use this style,Dapper only can be get the type and value,cant get the variable name.

[NMSAzulX]

@mgravell You said:"efficiency here is a false concern; ". Maybe,my understanding is wrong.
And I will close this issue a few hours later .
Thanks for all.

[NMSAzulX]

@sgf Thanks :).
No variable name, just use the index of object[] array.

[mgravell]

When I said "efficiency is a false concern", what I mean is simply: both
approaches have overheads, and it is premature to claim that parsing put
names (which has problems of its own) is somehow more efficient than the
current approach which uses cached reflection.

On 20 Mar 2018 4:30 am, "Hu" [e-mail] wrote:

@mgravell https://github.com/mgravell You said:"efficiency here is a
false concern; ". Maybe,my understanding is wrong.
And I will close this issue a few hours later .
Thanks for all.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/StackExchange/Dapper/issues/971#issuecomment-374470889,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AABDsDzXg1OTgHswjpc2dXQyXCMUIY55ks5tgIXdgaJpZM4Sth3r
.