DapperLib
splitOn can't handle NULL value
Hi,
I noticed splitOn can't handle NULL value. for example: if I use sth like select c.*, c1.* from ..., when xxx_id is the first column of table c1 and its value is null, dapper will fail to convert it into the mapping type, instead return a null.
My work around is manual inserting a id field like select c.*, 1 as id, c1.* from ....
and dapper works well.
I tried to clean up the issue...but I'm still not sure exactly what's happening here. Can you include a full example? I'm not sure if the splitOn is keyed on a null return but I can likely take a look next week - a full example would be easier to repro so we're on the same page and I can fix it accordingly.
Hi Nick, Can you tell me what is the fix ? And how to overcome the issue so that i can make necessary code changes.
I believe the issue is described in more detail here, and appear not to be resolved at the moment: http://stackoverflow.com/questions/10744728/dapper-multimap-doesnt-work-with-spliton-with-null-value
An allowSplitOnNull: true setting would be usable here, that would inspect all columns for values and not just the splitOn column. This is for scenarios where you have an object composed of several others, some of which are value objects without a meaningful id of their own.
@wezzix thanks for adding context here - we'll take a look at scanning all columns so no new options are even necessary.
I opened a similar issue ( https://github.com/StackExchange/dapper-dot-net/issues/222) but have come to appreciate the simplicity of Dapper's existing behavior: if the spliton value is null, the mapped object is null. Dapper needs a flag to say 'create this object', and that flag is the spliton value.
We use the pattern below quite a bit (because our PK's are not 'Id'). Sure, we get hit with the cost of a slightly larger resultset with duplicate columns, but that just isn't our bottleneck. Everything cruises, objects are fully hydrated, with no unexpected behavior. In regards to the issue in this thread, the user could simply tack on the aliased spliton column and know confidently if the returned object should be null or not.
(p = products table, c = optional categories table)
p.*,
c.CategoryID AS Id,
c.*
On Fri, Mar 25, 2016 at 2:19 PM, Nick Craver [email protected] wrote:
@wezzix https://github.com/wezzix thanks for adding context here - we'll take a look at scanning all columns so no new options are even necessary.
— You are receiving this because you are subscribed to this thread. Reply to this email directly or view it on GitHub https://github.com/StackExchange/dapper-dot-net/issues/266#issuecomment-201401951
In your use case with LEFT JOIN I can fully understand that this is not an issue. However the scenario I hinted above is quite distinct, where an object is composed of several other value objects (see DDD). Maybe an order with a billing and shipping address, or a contact with telephone number and prefix, all from a single table. As you can imagine, every field of these value objects can be null and there is no such thing as an Id. In addition, if the data source is a temp table created by SELECT ... INTO followed by yet more INSERT for convenience, then the whole situation becomes quite unmanageable and random fields become riddled magic constants with comments about "must be not null due to Dapper splitOn issue". Flattening the object structure to fit the SQL table would be a step back from object oriented design, so here, this is a real issue. Thanks for the great work so far! Glad to hear you are taking a look at it.
Wezzix, We don't split on trivial value objects such as a 'DateRange' object (with StartDate/EndDate properties). We flatten them out in private properties on the object in question so they don't effect anything else and directly wire their gets/sets to the public property. So, in the case of an Employee class, there might be a private EmploymentStartDate{get{return this.EmploymentDateRange.StartDate}{set{this.EmploymentDateRange.StartDate = value;}}.
I'll grant you that it's slightly quirky. But it's totally transparent, private to the class, unit-testable, and easy that at this point it's second-nature to us. The object is totally composed of the objects you want. The 'flattening' aspect of any value objects happens in private properties (which Dapper knows about).
So the general rule of thumb for us is: split on tables (e.g. Employees), flatten (privately) on value objects (e.g. DateRange, Currency, Telephone, etc.). When you call Query<Employee>() everything just gets wired in perfectly.
On Tue, Mar 29, 2016 at 8:38 AM, wezzix [email protected] wrote:
In your use case with LEFT JOIN I can fully understand that this is not an issue. However the scenario I hinted above is quite distinct, where an object is composed of several other value objects (see DDD). Maybe an order with a billing and shipping address, or a contact with telephone number and prefix, all from a single table. As you can imagine, every field of these value objects can be null and there is no such thing as an Id. In addition, if the data source is a temp table created by SELECT ... INTO followed by yet more INSERT for convenience, then the whole situation becomes quite unmanageable and random fields become riddled magic constants with comments about "must be not null due to Dapper splitOn issue". Flattening the object structure to fit the SQL table would be a step back from object oriented design, so here, this is a real issue. Thanks for the great work so far! Glad to hear you are taking a look at it.
— You are receiving this because you commented. Reply to this email directly or view it on GitHub https://github.com/StackExchange/dapper-dot-net/issues/266#issuecomment-202874582
Hi, I'm using dapper 1.50.2 but the problem still appears. Is there already a better fix available than adding a surrogate column?
The problem is still present under Dapper 1.50.5 (I tried under SQL Server 2017, if that matters).
Adding a dedicated non-NULL split column such as...
SELECT
-- first object columns
0 SPLIT,
-- second object columns
FROM
...
...solves it, but this behavior is unexpected and it took me quite a while to figure out the problem (under a ton of other code). I don't think the current behavior should be the default.
i still have the same problem in 1.50.5 I also commented the stackoverflow topic https://stackoverflow.com/questions/10744728/dapper-multimap-doesnt-work-with-spliton-with-null-value/52279842#52279842
Any update on this? Is this going to be fixed in the near future release?
The OP started this thread in 2015.
Still an issue, specifically for splitting keyless data returned from a stored procedure into multiple objects. If the splitOn column is null, the object won't get mapped at all even if other fields in the object have values.
