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.

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://stackoverflow.com/a/27588877/3813189).

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).

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