Default value for dapper multi mapped IEnumerable list when result is null

c# dapper


I have the following code that is querying a database by stored procedure. When ReturnObject.familymembers = multi.Read<InternalObjects.Person>().AsQueryable(); is not null, the return is fine; however I cannot find a way to handle a null return through Dapper / linq with a collection type.

My Stored Procedure:

SELECT as stateName,   fam.*
FROM            Family fam
WHERE        fam.idUserName=@username 

SELECT      person.*
FROM            person
INNER JOIN Family fam on fam.Id = person.idFamily
WHERE        fam.idUserName=@username

When the second SQL statement has records, setting ReturnObject.familymembers gives no issues, but I'm having trouble figuring out how to specify a default or handle a null return from Dapper. When debugging, ReturnObject.familymembers = multi.Read<InternalObjects.Person>().AsQueryable() throws a null exception if no rows were returned.

Here's What I thought would work, but doesn't:

ReturnObject.familymembers = multi.Read<InternalObjects.Person>().AsQueryable().DefaultIfEmpty<InternalObjects.Person>();

        public InternalObjects.FamilyDashboard GetDashboardInfo(string username)
        InternalObjects.FamilyDashboard ReturnObject = new InternalObjects.FamilyDashboard();

        using (var dbConnection = _dbConnectionFactory.CreateConnection())
            using (var multi = dbConnection.QueryMultiple("DashboardSP", new { username = username }, commandType: CommandType.StoredProcedure))
                ReturnObject  = multi.Read<InternalObjects.FamilyDashboard>().SingleOrDefault();
                ReturnObject.familymembers = multi.Read<InternalObjects.Person>().AsQueryable();

        return ReturnObject;


public class InternalObjects
    public class FamilyDashboard
        public string physicalAddress1 { get; set; }
        public string MailingAddress1 { get; set; }
        public string physicalAddressCity { get; set; }
        public string physicalAddressZip { get; set; }
        public string MailingAddressCity { get; set; }
        public string NumInHousehold { get; set; }
        public string capidCounty { get; set; }
        public string physicalUnit { get; set; }
        public string mailingUnit { get; set; }
        public string familyPhone { get; set; }
        public string capCharacteristics { get; set; }
        public IEnumerable<Person> familymembers {get; set;}

    public class Person
        public int Id { get; set; }
        public string firstName { get; set; }
        public string lastName { get; set; }
        public string middleName { get; set; }
        public int gender { get; set; }
        public DateTime birthdate { get; set; }
        public string emailPersonal { get; set; }
        public string SSN { get; set; }
        public string relationshipName { get; set; }
        public string primaryLanguageName { get; set; }

        public int age { get; set; }


Popular Answer

I've found the answer:

Basically, dapper doesn't return any values if no rows returned, which doesn't instantiate my object. My parent object, ReturnObject, wasn't being instantiated, but was still executing fine.

The next line ReturnObject.familymembers = multi.Read<InternalObjects.Person>().AsQueryable() tries to access the object, but the DTO doesn't exist.

A simple conditional saves the day:

if (ReturnObject != null)
    ReturnObject.familymembers = multi.Read<InternalObjects.Person>().ToList<InternalObjects.Person>();

References for anyone else who runs into the same!:

How to return null from a Dapper query rather than default(T)?

Getting Dapper to return an empty string instead of a null string

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why