How to use Dapper with Linq

Question

I am trying to convert from Entity Framework to Dapper to reliably improve data access performance.

The queries I use are represented as predicates like Expression>.

To give an example:

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

What am I doing now:

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 would 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, maybe someone can help.

Edit:

Notice what I found: https://github.com/ryanwatson/Dapper.Extensions.Linq

but I can’t figure out how to use it.

Answer 1

First, one of the Dapper authors said when someone asked

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

what

no plans to do this. This is far beyond what dapper is trying to do. So far, I would say that this contradicts. The Kernel Dapper is trying to become a friend for those who love their SQL.

(see fooobar.com/questions/382743 / ... ).

In a sense, this means that various extension packages for NuGet can help, as you said.

I tried DapperExtensions , which makes it easy to write query filters programmatically - 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 tried Dapper.Extensions.Linq (the package you suggested), which promises to

builds on this, providing enhanced access to the database through Linq queries. Fluid configuration simplifies installation and speeds up setup.

Unfortunately, I also could not go very far. The documentation is not enough, and the tests do not seem to cover QueryBuilder, which seems to be the class used to translate Linq expressions to Dapper Extensions predicates (as suggested in the Boolean Expression Parsing with QueryBuilder release ). I tried the following, which required adding 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 failed at runtime with an error

Operator not found for StackOverflowAnswer.Program + Products

I'm not sure why creating Predicate manually (the first example) works, but QueryBuilder does not.

I would say that it is more and more like the comments left on your question are correct, that you will need to redo the code from the expressions that you used with the Entity Framework. Since it was so hard to find any information about this QueryBuilder class, I would be worried that (even if you earned it), any problems you encountered would be difficult to get help (and errors might not be fixed).

Answer 2

I wrote a utility for working EF with Dapper using attributes. I am analyzing a predicate and translating into 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 a simple request:

 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 for you?

MicroOrm.Dapper.Repositories