Skip to content

Instantly share code, notes, and snippets.

@grandsilence
Created August 30, 2019 14:57
Show Gist options
  • Save grandsilence/e247f9ce8baac2c40adac9856564f2a5 to your computer and use it in GitHub Desktop.
Save grandsilence/e247f9ce8baac2c40adac9856564f2a5 to your computer and use it in GitHub Desktop.
INNER JOIN multiple tables in C# Dapper library
public override async Task<IEnumerable<City>> GetAllAsync()
{
var cities = new Dictionary<long, City>();
//var areas = new Dictionary<long, Area>();
const string sql =
@"SELECT c.id, c.name, a.id, a.name FROM cities c
INNER JOIN city_areas ca ON ca.city_id = c.id
INNER JOIN areas a ON a.id = ca.area_id";
await Db.QueryAsync<City, Area, City>(sql, (c, a) => {
// Get City (TryGetValue when Cities should be unique)
if (!cities.TryGetValue(c.Id, out var city))
cities.Add(c.Id, city = c);
// Get Area
// We aren't using because Areas isn't unique
//if (!areas.TryGetValue(a.Id, out var area))
//{
//areas.Add(a.Id, area = a);
city.Areas.Add(a);
//}
return city;
});
return cities.Values.ToArray();
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment