Customizing SQL with Dapper Extensions

Question

I'm using Dapper Extensions for some of my types and it works really well for most use cases. I've run into a case where I have a many-many relationship, and I want to do something like:-

SELECT id,a,b,c FROM Foo WHERE Foo.id in (SELECT foo_id FROM foo-bar WHERE bar-id=@bar_id)

Obviously Dapper Extensions can handle "SELECT id,a,b,c FROM Foo" but not the latter part. I could do a select to get the list of Foo id's I want, and then pass that to Dapper Extensions but that's less efficient.

The part I can't do with plain Dapper is get the SELECT column list automatically, so what I'd really like is a way to:-

• Get the SELECT column list from Dapper Extension's internal mechanisms
• Get the basic "SELECT id,a,b,c FROM Foo" from Dapper Extension's internal mechanisms
• Hook Dapper Extensions Get code so that I can add a custom WHERE clause

I've looked at the code, but I can't spot how to do any of these things. Can anyone help? I've worked around by using plain Dapper and "SELECT * ..." at the moment, but I'm sure there's a better way.

Answer 1

I did not know this was not supported back in 2012. So about 1.7K views in two years and not much exposure. But in case someone new to Dapper landed here and wondering if it works, the answer is, it is working. Using the latest version of, as of this writing, Dapper v1.42 from nuget:

var sql = "SELECT id,a,b,c FROM Foo WHERE Foo.id in (
    SELECT foo_id FROM foo-bar WHERE bar-id=@bar_id)"
using (var cn = new SqlConnection(the_connection_string)) {
  cn.Open();
  var returnedObject = cn.Query<dynamic>(sql, new { bar_id = some_value });
}

Answer 2

Here is another option:

You can create a View:

select * from Foo 
join FooBar b
on a.foo_id = b.foo_id

Then use predicates to select with any where clause:

using (SqlConnection cn = new SqlConnection(_connectionString))
{
    cn.Open();
    var predicate = Predicates.Field<Foo>(f => f.foo_id, Operator.Eq, 1);
    IEnumerable<Foo> list = cn.GetList<Foo>(predicate);
    cn.Close();
}

Generated SQL should look something like:

SELECT 
   [Foo].[foo_id]
 , [Foo].[...]
 , [Foo].[...]
 , [Foo].[...]
 , [Foo].[...] 
FROM [ViewName] 
WHERE ([ViewName].[foo_id] = @foo_id_0)