How to use Dapper with Linq

Question

I'm trying to convert from Entity Framework to Dapper to hopefully improve data access performance.

The queries I use are in the form of predicates like so Expression<Func<TModel, bool>>.

To give an example:

I have the following code which I need to convert to using Dapper.

What I currently do:

public async Task<List<TModel>> Get(Expression<Func<TModel, bool>> query)
{
    // this.Context is of type DbContext
    return await this.Context.Set<TModel>().Where(query).ToListAsync();
}

What I'd like to do:

public async Task<List<TModel>> Get(Expression<Func<TModel, bool>> query)
{
    using (IDbConnection cn = this.GetConnection)
    {
        return await cn.QueryAsync<TModel>(query);
    }
}

My google-fu is failing me, can someone please assist.

Edit:

Note that I did find: https://github.com/ryanwatson/Dapper.Extensions.Linq

but I can't seem to figure out how to use it.

Recliner

LINQ to EF constructs a SQL statement from an entity model and LINQ predicates. Dapper executes actual SQL statements and "simply" maps the results. You'll have to write the full SQL parameterized SQL statement. – Oberstone

To put it another way, are you sure you aren't solving the wrong problem? You can simply add yet-another-Where clause to whatever query you had, eg var query=context.SomeEntity.Where()....; query=query.Where(...); query=query.Select(...);. You don't need to work with expressions if you simply want to add filters to an existing query. Moving to a micro-ORM isn't going to make runtime construction of queries easier – Oberstone

for Dapper, cn.QueryAsync<TModel>(query) , query should be sql string not Expression<Func<TModel,bool>>. You have to do major change in converting from EF. to Dapper. – Modifier

Answer 1

Firstly, one of the authors of Dapper said, when someone asked

Is there a plan to make Dapper.net compatible with IQueryable interfaces?

that

there are no plans to do this. It is far far outside what dapper tries to do. So far that I would say it is antithetical. Dapper core tries to be the friend to those who love their SQL.

(see https://mcmap.net/q/436417/-dapper-net-and-iqueryable).

In a way, that does suggest that the various extension packages to NuGet may help, as you have suggested.

I have tried DapperExtensions, which makes writing the query filters in a programmatic way a little easier - eg.

using System.Data.SqlClient;
using DapperExtensions;

namespace StackOverflowAnswer
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var cn = new SqlConnection("Server=.;Database=NORTHWND;Trusted_Connection=True;"))
            {
                var list = cn.GetList<Products>(
                    Predicates.Field<Products>(f => f.Discontinued, Operator.Eq, false)
                );
            }
        }

        class Products
        {
            public int ProductId { get; set; }
            public string ProductName { get; set; }
            public bool Discontinued { get; set; }
        }
    }
}

I also tried Dapper.Extensions.Linq (the package you suggested), which promises to

builds on this providing advanced DB access through Linq queries. The fluid configuration makes setup simplistic and quick.

Unfortunately, I also couldn't get very far with it. There isn't much documentation and the tests don't seem to cover the QueryBuilder, which is what appears to be the class to use to translate Linq Expressions into the Dapper Extensions predicates (as suggested by the issue Parsing boolean expressions with the QueryBuilder). I tried the following, which required add the IEntity interface to my DTO -

using System;
using System.Data.SqlClient;
using System.Linq.Expressions;
using Dapper.Extensions.Linq.Builder;
using Dapper.Extensions.Linq.Core;
using DapperExtensions;

namespace StackOverflowAnswer
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var cn = new SqlConnection("Server=.;Database=NORTHWND;Trusted_Connection=True;"))
            {
                Expression<Func<Products, bool>> filter = p => !p.Discontinued;
                var queryFilter = QueryBuilder<Products>.FromExpression(filter);

                var list = cn.GetList<Products>(
                    queryFilter
                );
            }
        }

        class Products : IEntity
        {
            public int ProductId { get; set; }
            public string ProductName { get; set; }
            public bool Discontinued { get; set; }
        }
    }
}

.. but it failed at runtime with the error

Operator was not found for StackOverflowAnswer.Program+Products

I'm not sure why generating the Predicate manually (the first example) works but the QueryBuilder doesn't..

I would say that it's increasingly looking like the comments left on your question are correct, that you will need to re-work your code away from the expressions that you used with Entity Framework. Since it's been so difficult to find any information about this QueryBuilder class, I would be concerned that (even if you did get it working) any issues that you encountered would be difficult to get help for (and bugs may go unfixed).

Olympic

Thank you for taking the time to look into my question. I am happy ( and sad ) that someone else has come to the same conclusion I have. Thank you very much. – Recliner

Just to add to this. Even though the Dapper authors might consider something as LINQ extensions to Dapper "antithetical" to its intended purpose, I do believe there are good reasons and benefits to something like this. Firstly, such libraries provides built-in protection against sql-injection attacks. Secondly, you get the benefits of intellisense. You get all of this along with the stability and reliability of Dapper,without the stack of leaky abstractions, caveats and overhead of Entity Framework. Writing pure SQL by hand is not always the determining factor for choosing Dapper as ORM. – Fouts

Answer 2

I wrote a utility to work EF with Dapper using attributes. I parsing predicate and translate to SQL.

"Users" POCO:

[Table("Users")]
public class User
{
    [Key]
    [Identity]
    public int Id { get; set; }

    public string Login { get; set;}

    [Column("FName")]
    public string FirstName { get; set; }

    [Column("LName")]
    public string LastName { get; set; }

    public string Email { get; set; }

    [NotMapped]
    public string FullName
    {
        get
        {
            return string.Format("{0} {1}", FirstName, LastName);
        }
    }
}

And simple query:

using (var cn = new SqlConnection("..."))
{
    var usersRepository = new DapperRepository<User>(cn)
    var allUsers = await userRepository.FindAllAsync(x => x.AccountId == 3 && x.Status != UserStatus.Deleted);
}

Maybe it will be useful to you?

MicroOrm.Dapper.Repositories

Disannul

I understand that your reply is not an answer to the original question, because you are not using LINQ. However, your solution is the closest anyone will ever get to type-safe, out-of-process query expressions without using Entity Framework. Amazing! Good job! – Jakob

This one seems to be a valid answer. Indeed, you could had type var result = await new DapperRepository<TModel>(cn).FindAllAsync(query).ToListAsync(), which is exactly what he wants. – Signalize