CODEWITHSUNDEEP
c#ormdappermicro-orm
JohnCC
JohnCC

Reputation: 615

Customizing SQL with Dapper Extensions

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:-

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.

Upvotes: 1

Views: 5086

Answers (2)

von v.
von v.

Reputation: 17108

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

Upvotes: 1

Void Ray
Void Ray

Reputation: 10199

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)

Upvotes: 2

Related Questions