Generic QueryMultiple with Dapper

Question

I have a basic repository with a Generic Get method to return data using Dapper like

public T Get<T>(Func<IDbConnection, T> query) { using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDB"].ConnectionString)) { return query.Invoke(db); } } 

However, now I need to return some data. The DAL request is as follows:

 var multi = db.QueryMultiple(getCarDataSp , new { CustomerID = customerId, Year = year }, commandType: CommandType.StoredProcedure)); var cars = multi.Read<CarDTO>(); var options = multi.Read<CarOptionDTO>(); //wire the options to the cars foreach(var car in cars){ var carOptions = options.Where(w=>w.Car.CarID == car.CarID); //I would override Equals in general so you can write w.Car.Equals(car)...do this on a common DataModel class car.Options = carOptions.ToList(); } 

Would it be possible to have a common GetMultiple in my BaseRepository or would it be a matter of wrapping get multi in my Get method, and then cars and options where there are separate Get calls?

Answer 1

You can do something like this that will return a tuple containing IEnumerables of each type that you are after.

In the base repository

(This is basically a bunch of overloads ... you can add more overloads if you have more types).

 public Tuple<IEnumerable<T1>, IEnumerable<T2>> GetMultiple<T1, T2>(string sql, object parameters, Func<GridReader, IEnumerable<T1>> func1, Func<GridReader, IEnumerable<T2>> func2) { var objs = getMultiple(sql, parameters, func1, func2); return Tuple.Create(objs[0] as IEnumerable<T1>, objs[1] as IEnumerable<T2>); } public Tuple<IEnumerable<T1>, IEnumerable<T2>, IEnumerable<T3>> GetMultiple<T1, T2, T3>(string sql, object parameters, Func<GridReader, IEnumerable<T1>> func1, Func<GridReader, IEnumerable<T2>> func2, Func<GridReader, IEnumerable<T3>> func3) { var objs = getMultiple(sql, parameters, func1, func2, func3); return Tuple.Create(objs[0] as IEnumerable<T1>, objs[1] as IEnumerable<T2>, objs[2] as IEnumerable<T3>); } private List<object> getMultiple(string sql, object parameters,params Func<GridReader,object>[] readerFuncs ) { var returnResults = new List<object>(); using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDB"].ConnectionString)) { var gridReader = db.QueryMultiple(sql, parameters); foreach(var readerFunc in readerFuncs) { var obj = readerFunc(gridReader); returnResults.Add(obj); } } return returnResults; } 

In a derived repository

(quite clean and, more importantly, typed!)

 public class Foo { } public class Bar { } public void sample() { var sql = "select * from Foo; select * from Bar"; var foosAndBars = this.GetMultiple(sql, new { param = "baz" }, gr => gr.Read<Foo>(), gr => gr.Read<Bar>()); var foos = foosAndBars.Item1; var bars = foosAndBars.Item2; }