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?
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();
}