MultiMap does not return null object in left-outer-join if splitting on a surrogate key that is not a property on the target object.

[BlackjacketMack]

This issue is really for left-outer-joins where we would expect a null object if there is no match. Unfortunately, the object is not coming back null even though the 'spliton' column is null.

Class structure
public class Post[PostID,Name,CategoryID?] //category is optional (left-outer joined)
public class Category[CategoryID,Name]

Query using aliases to split on 'id'

SELECT p.*, c.CategoryID AS id, //surrogate key helps mark the split...will be null if no matching category c.* FROM Posts p LEFT OUTER JOIN Categories c ON c.CategoryID = p.CategoryID

If there is no category, the expected behavior is that because the 'id' column is null the entire object would be null. However, because 'id' does not exist as a property on the Category object we get an instantiated object.

Note that we're splitting this way because p.* already has CategoryID so we can't explicitly split on that.

I've gone through the source code for a bit but it's a little beyond me (for now at least). I'll keep trying to pinpoint why that is happening and get a test in there to prove it.

[ajbeaven]

+1

var animals = Connection.Query<Animal, Owner, Animal>(@"
    SELECT Animal.*, NULL as split, Owner.*
    FROM Animal
    LEFT JOIN Owner ON Animal.OwnerId = Owner.OwnerId", (a, o) => { a.Owner = o; return a; }, splitOn: "split").ToList();

I would expect Owner would be NULL in this case.

[jmzagorski]

any update on this or workarounds? I am using the Read method from this request #308. Thanks.

[paveldayneko]

Is there any update regarding this issue?

[BlueInt32]

I was just confronted with this annoying issue (which apparently has a fix that got never merged?) but managed to work around it by replacing the NULL values with whatever magic value you could detect using the SQL coalesce operator.
This may not be the best solution but a workaround is better than nothing I guess!

SELECT p.*, 
    coalesce(c.CategoryID, 0) AS id, 
    coalesce(c.Name, '') as categoryName
OUTER JOIN Categories c ON c.CategoryID = p.CategoryID