Return array from db?

Question

Return array from db?

c# dapper sql-server

Question

I have a stored procedure with a left join that returns something like this:

name1  car1
name1  car2
name1  car3
name2  car1
name3  null
name4  null

I need to store this result in an object like this:

class Person 
{
    string Name;
    List<string> Cars;
}

What is the best way to do this? Either directly in the stored procedure or in code after retrieving the query result.

That I do now is once I have the result from db to iterate over it and add the name and its list of cars c#to another object if name is not already there.

Is there a better solution to this?

Andrea

View more on Stack Overflow

Steve · Accepted Answer · 9/10/2015 9:21:02 PM

Suppose you have two tables in your database. Person and Cars. For simplicity assume Person to have two columns PersonID, Name, while Cars has three columns CarID, Name, PersonID. The field PersonID in the Cars table is the foreing key that links a car to a person.

A simple query that associates your Person to your Car is

string query = @"
SELECT p.PersonID, p.Name, c.CarID, c.Name 
FROM Person p LEFT JOIN Cars c on p.PersonID = c.PersonID";

and this could be your Model

public class Person
{
    public int PersonID {get;set;}
    public string Name { get; set; }
    public List<Car> Cars {get;set;}
}
public class Car
{
    public int CarID { get; set; }
    public string Name { get; set; }
    public int PersonID {get;set;}
}

Now you could write this to work with Dapper

var lookup = new Dictionary<int, Person>();
using (IDbConnection cnn = GetOpenConnection())
{
    var people = cnn.Query <Person, Car, Person>(query, (per, car) =>
    {
        Person found;
        if (!lookup.TryGetValue(per.PersonID, out found))
        {
            found = per;
            lookup.Add(per.PersonID, found);
            found.Cars = new List<Car>();
        }
        found.Cars.Add(car);
        return found;
    }, splitOn: "CarID").Distinct();
}

Return array from db?

Question

Fastest Dapper Plus Extensions

Popular Answer

Performance

Expression Evaluator