How to execute LINQ with Dapper?

c# dapper expression linq predicate

Question

In an effort to boost the speed of data access, I'm attempting to switch from Entity Framework to Dapper.

My search terms take the form of predicates, such as "Expression>."

To use an illustration:

I need to adapt the following code to work with Dapper.

What I do at the moment:

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 want to do is:

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

I need help with Google, if anybody can help.

Edit:

I did found https://github.com/ryanwatson/Dapper.Extensions.Linq, take note.

But I can't seem to utilize it since I don't know how.

1
11
8/8/2016 11:07:23 AM

Accepted Answer

First off, one of the Dapper writers said that when

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.

(View zhttps://stackoverflow.com/a/27588877/3813189z).

In a sense, it does imply that the different extension packages for NuGet, as you proposed, would be helpful.

I've tried DapperExtensions, which makes creating query filters programmatically a little bit simpler - for example.

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 tested the program you recommended, Dapper.Extensions.Linq, which claims to

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

Unfortunately, I had little success with it as well. The QueryBuilder class, which is what seems to be the class to use to transform Linq Expressions into the Dapper Extensions predicates, is not well documented, and the tests don't seem to cover it (as suggested by the issue Boolean expressions can be analyzed using the QueryBuilder.). The IEntity interface needed to be added to my DTO, therefore I attempted the following:

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 ran into a runtime problem and failed.

Operator was not found for StackOverflowAnswer.Program+Products

I'm not clear why the QueryBuilder works but manually building the predicate (the first example) doesn't.

It seems, in my opinion, that the remarks made in response to your query are accurate and that you will need to rewrite your code to do away with the Entity Framework expressions you were using. Even if you were able to get this QueryBuilder class to operate, I would be worried that any problems you ran into would be challenging to obtain assistance for since it has been so difficult to discover any information about it (and bugs may go unfixed).

17
5/23/2017 12:34:23 PM

Popular Answer

I created a tool that use characteristics to integrate EF with Dapper. I transfer the predicate to SQL after processing it.

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

Simple question

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 you'll find it useful?

MicroOrm.Dapper.Repositories



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow