V2: What should the API look like for horizontally mapped data via tuples?

[mgravell]

Historically, we have the multi-generic Query<T1,T2, ... ,TResult> API for this which is ugly as sin and clunky to use.

We have an opportunity to improve things using tuples, with the nice advantage that since tuples are only one T (for any reasonable number of partitions), it won't cause overload explosion. Long term, this might even be seen as a replacement to the Query<T1,T2, ..., TResult> API (in a breaking major, etc).

I've thrown some early ideas into a branch; I want to invite feedback, or other original ideas - it is entirely possible that I'm missing an obvious + better way of doing this!

Again, the target scenario here is when you're selecting from multiple tables in one horizontal piece, and want to access the pieces into separate DTOs - in SQL terms:

select c.*, a.*
from Customer c
inner join CustomerAddresses ca on ca.CustomerId = c.Id
inner join Address a on a.Id = ca.AddressId

which is going to return a bunch of columns that we want to map (for each row) into a Customer object and an Address object.

Note: the discussion of what to do for duplicated Address records or Customer records (same Id, multiple instances vs single instance) is separate - please don't get distracted by that aspect (although that's also something we'd like to improve at a later data)

Here are a lot of examples in that test branch

[NickCraver]

The very related API here (that I think sucks) is splitOn in these multi-map scenarios, e.g. splitOn: "Id, UserId" (defaults to splitOn: "Id"). It's string based and error-prone. Many people get it wrong once you leave the realm of Id being your PK column (or in the query). Some (IMO, bad) ideas:

• We could have an array as the arg, but people are likely to allocate that on every call, likely resulting in: splitOn: new[] { nameof(Foo.Id), nameof(User.UserId) }
• The Column/Table proposal in V2: [Column] and [Table] Attributes #722 could be used as the default. So instead of "Id" (still the default), it'd look for, effectively: splitOn: $"{Foo.PrimaryKeyField},{Bar.PrimaryKeyField}"
  • This would be determined by the [Column] attributes on those types (or the mapping function, if it handles PK recognition).
  • Con: obviously doesn't work well for multi-column PKs
  • Very "heavy" compared to a string

While these are more "safe" than a string, they're also by-simplest-usage more expensive. It'd be fantastic if we can pull a trick with tuples or something else that doesn't allocate so much and could get some of the safety features overall. Anyone have any fancy ideas for what they'd like that parameter to look like?

[mgravell]

Radical alternative - fluent API that separates the different aspects (command details, mapping details, etc)

var tuples = conn.Query(sql, args).Map<(tuple shape)>().ToList();
var orders = conn.Query(sql, arts).ToList<Order>();

Etc. Nothing would execute until the final ToList / AsEnumerable / Single etc - everything else is composition into structs. Note these are custom methods that just look like the familiar LINQ ones.

Discuss...

[rossipedia]

The fluent API looks really nice. What would the return type of Query(sql, args) be in that example?

[gbjbaanb]

Now C#7 has a syntactic sugary coating for tuples, it should look the same.

So a query would look like:

var resultrow = conn.Query<(string, int, int)>("select aString, anInt, anotherInt from aTable").ToList();

named items in the tuple would be specified in the tuple definition between the <>, or the default Item1, Item2 would be used otherwise.

I assume the compiler would do a lot of the work here if it knew where to see the tuple definition, but given functions that can now look like: (string first, string middle, string last) LookupName(long id) then the compiler happily handles this.

It might be possible to dynamically create the tuple from the query, but that won;t help the compiler, so I'm quite happy to strongly type it as I would if I was returning a POCO class, but in the case where you are returning a row of all columns (eg select *) then having each item in the tuple defined as a dynamically defined object sounds OK.

I don't like the idea of the fluent version - I want to call some SQL in 1 line of code and have some results back into a variable. Defining it in one place and then running the same SQL or defining the SQL in a string and passing it into multiple lines that all look the same doesn't feel right. I want the equivalent of "dapper, gimme data now" not setting up definitions, setting up control options, and then running the query.

Simple and consistent is good, so using the same mechanism C#7 now provides should be the default way, surely?!

[mgravell]

Important: tuple names are only passed *outwards*, not *inwards*. Generics here is an inwards. So the names would be ignored and it would be purely positional.

…

On 23 Oct 2017 11:06 p.m., "Andy" ***@***.***> wrote: Now C#7 has a syntactic sugary coating for tuples <https://blogs.msdn.microsoft.com/dotnet/2017/03/09/new-features-in-c-7-0/>, it should look the same. So a query would look like: var resultrow = conn.Query<(string, int, int)>("select aString, anInt, anotherInt from aTable").ToList(); named items in the tuple would be specified in the tuple definition between the <>, or the default Item1, Item2 would be used otherwise. I assume the compiler would do a lot of the work here if it knew where to see the tuple definition, but given functions that can now look like: (string first, string middle, string last) LookupName(long id) then the compiler happily handles this. It might be possible to dynamically create the tuple from the query, but that won;t help the compiler, so I'm quite happy to strongly type it as I would if I was returning a POCO class, but in the case where you are returning a row of all columns (eg select *) then having each item in the tuple defined as a dynamically defined object sounds OK. I don't like the idea of the fluent version - I want to call some SQL in 1 line of code and have some results back into a variable. Defining it in one place and then running the same SQL or defining the SQL in a string and passing it into multiple lines that all look the same doesn't feel right. I want the equivalent of "dapper, gimme data now" not setting up definitions, setting up control options, and then running the query. Simple and consistent is good, so using the same mechanism C#7 now provides should be the default way, surely?! — You are receiving this because you were assigned. Reply to this email directly, view it on GitHub <#745 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AABDsI8f0KnYF7skMbCSqHlM8AaAwKr-ks5svQ32gaJpZM4NEVQk> .

[TroySchmidt]

The improvement to "lose" splitOn can be dropped when using Dapper.Contrib in a future implementation. When adding the relationships and columns via LINQ or expression predicates the SQL generated will be required to keep the table alias prefix for all the columns. The internal mechanisms of adding those to the generated SQL can go so far as to keep them grouped together internally before using SqlBuilder. At that point the column to splitOn for all the generated queries using Dapper.Contrib in this manner are known.
This may not be the final implementation, but it does provide a nice layer of abstraction for those using Dapper.Contrib to not worry about defining splitOn.