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

c# dapper

Question

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    states.name 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;
    }

My DTO:

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