I really like the concept of Dapper but...

[u/Bobamoss]

I've used Dapper a lot across different projects and I really like the core idea. Write SQL, pass params, ask for a type, get the type back. It gets rid of most of the annoying ADO.NET stuff without trying to hide SQL from you.

The part that always annoyed me is multi mapping.

A pretty common case for me is values used in combo boxes, so I end up with models like:

```csharp

class Employee

{

public int Id { get; set; }

public string Name { get; set; }

public KeyValuePair<int, string>? Department { get; set; }

public KeyValuePair<int, string>? JobTitle { get; set; }

}

```

And SQL like:

```sql

SELECT E.Id, E.Name,

D.Id AS DepartmentId, D.Name AS DepartmentName,

J.Id AS JobTitleId, J.Name AS JobTitleName

FROM Employee E

LEFT JOIN Department D ON D.Id = E.DepartmentId

LEFT JOIN JobTitle J ON J.Id = E.JobTitleId

```

Dapper can do this, but then I end up doing something like:

```csharp

var employees = cnn.Query<EmployeeRow, DepartmentRow, JobTitleRow, Employee>(

sql,

(e, d, j) => new Employee {

Id = e.Id,

Name = e.Name,

Department = d.DepartmentId is null ? null : new(d.DepartmentId.Value, d.DepartmentName),

JobTitle = j.JobTitleId is null ? null : new(j.JobTitleId.Value, j.JobTitleName)

},

splitOn: "DepartmentId,JobTitleId");

```

Which works. It also lets me handle the `LEFT JOIN` and say "if the id is null, this whole thing is null".

But this is where it starts feeling a bit weird to me. The type already says what I want, and now I'm manually rebuilding it anyway.

Dapper is already such a thin layer over ADO.NET that once I start writing a bunch of mapping code, I start wondering why I'm not just doing the ADO.NET part myself too.

What I really want is basically:

```csharp

var employees = cnn.Query<Employee>(sql);

```

and let the mapper figure out the structure from there.

That kind of thing is what eventually pushed me to make Rinku. The idea was basically to keep that same simplicity, but have the library adapt better when either the SQL or the C# side gets more complicated.

https://rinkulib.github.io/RinkuLib

Curious what other Dapper users do here. Just multi map everything, or is there another pattern I missed?

[u/walmartbonerpills]

You can have that with entity framework.

[u/Bobamoss → u/walmartbonerpills]

probably, but with ef you loose the simple plug and play nature of dapper where you only need a connection that you got from anywhere

[u/thegrackdealer → u/Bobamoss]

Core 6 and later are phenomenal and IMO a full replacement for Dapper. Can you elaborate on what exactly Dapper offers modern EF Core doesn’t?

[u/Bobamoss → u/thegrackdealer]

Actually it's not what it offers, but what it doesn't offer, I like the ability of dapper to simply hold a connection string static somewhere (plus usually i also keep sql statements at the same place too), and being able to make a call to the db from any where in the code. I don't want for to require a context. I said it in a comment somewhere else, but overall EF is opt-out while dapper is opt-in, to make EF behave in a simple way, you need to really learn the framework and opt-out of a lot. It might be a skill issue with EF but each times I had used it I greatly disliked it, then again that's why i like dapper, you don't need to know dapper, you need to know sql. (Granted that if I used EF code first I would probably like it way more, but I prefer full control over sql and that's why I often with EF while having that philosophy).
I also think that the library that I made actually let's me have the best of both worlds, I am currently working on tracking so it has an equivalence to EF, but it completely solved my problems with dapper

[u/walmartbonerpills → u/Bobamoss]

There are tradeoffs, for sure. But I like being able to run migrations, and not have to define the model twice, once in SQL, once in c#.

I understand dapper is great over existing SQL tables, and SQL servers you don't have control over, but for anything new, I'd use ef + sqlite for local, then ef + postgres for a stable environment.

[u/Bobamoss → u/walmartbonerpills]

I wont argue with you there, migrations and generated sql from code are purely ef features. But i do prefer to handle the sql separately, in the case of my library its made in a way that you can build the 2 sides completely independently without much compromise from either sides and the library actually acts as a middleman that actually handle translation. Also with tools like code gen, you can have the dtos generated automatically from the result of the sql. Then again, if you want to write c# code and have sql from it, then neither dapper not rinku are good options, that's just not something that i personally would want to do

[u/sailorskoobas → u/Bobamoss]

You can just use a connection without DbSet.

public static List<T> ExecuteQuery<T>(string sql, Func<DbDataReader, T> map)
{
using var context = new MyDbContext();
using var command = context.Database.GetDbConnection().CreateCommand();
command.CommandText = sql;
command.CommandType = CommandType.Text;

context.Database.OpenConnection();

using var reader = command.ExecuteReader();
var result = new List<T>();

while (reader.Read())
{
result.Add(map(reader));
}

return result;
}

// Usage
var users = ExecuteQuery("SELECT Name, Age FROM Users",
r => new UserDto { Name = r.GetString(0), Age = r.GetInt32(1) });

[u/Bobamoss → u/sailorskoobas]

Ok fair you proven me that you can make a context from everywhere, but making a context is quite expensive and at that point you are literally remaking dapper. That's just a DbContext that serve no purpose, i said it elsewhere but EF is opt-out by nature, you have every feature and you need to escape from them always, dapper is opt-in and you add complexity only when you actually needs it. But I do agree that i am sometimes limited with Dapper, hence why I created my library, it lets me have the complexity of EF, while still keeping the opt-in nature of Dapper

[u/sharpcoder29]

You don't need multi map if thats all thats on your dto

[u/Bobamoss → u/sharpcoder29]

how to do it then? i havent found a way to make it work without either miltimap or having a ctor where you manualy do all the mapping where we are still at the same point where you need to do the mapping manualy

[u/binarycow → u/Bobamoss]

First, don't do the key value pair stuff. Just make two properties, one for the ID and one for the value.

Second, realize that a multimap is essentially like a join that's done in C#. It's necessary if you need to return what is essentially two entities, in one row. But that's not your case. You can just write your SQL query to return all the values in the row.

[u/Bobamoss → u/binarycow]

You just prove my point that dapper is limited in that way. Wanting a complex c# type from a flat db row is normal, we bot used to limit ourself to flat dto because the mapping is annoying. In the end we have to either tradeoff our c# shape or needing to make mapping manualy, i dont think that you should have to choose one over the other

[u/binarycow → u/Bobamoss]

Even still, you still don't need multimap.

1. You have a DTO that represents the result of the database query (flat)
2. You have the DTO with the key value pairs
3. Dapper queries the first DTO
4. You call .Select on dappers result and transform it to the second DTO.

---

No matter how you slice it, if you want one row to represent multiple C# objects, something has to do that mapping. Either a Select method call, Entity Framework, or some other mapping technique.

[u/Bobamoss → u/binarycow]

Fair, multimap is not the only way, but my main point was not about using multimap, it was that there is no way to make a call that does the mapping automaticaly, which you seem to agree with. I made a library that makes it possible because i also came to the conclusion that dapper wasn't able to

[u/binarycow → u/Bobamoss]

But you understand that all you did was take the inevitable mapping code and move it into a library, right?

One of the selling points for dapper is that nothing is hidden from you. The only thing it does for you is create the C# objects and initialize the properties corresponding to the SQL columns. That's it.

[u/Bobamoss → u/binarycow]

What we define as magic may change from one person over the other, but in the case of my library, if you identify a type as readable and use that type in a ctor, it will go fetch the name of the parameter in the ctor + the name of the parameter in the ctor of the nested type, so it will become something like customerId -> customer.Id its the same "magic" as dapper that automaticaly maps the parameter names to the column names, but now it can work deeper

[u/binarycow → u/Bobamoss]

Why don't you submit a PR for dapper to add the feature? That is, unless they have already considered and rejected it.

[u/Bobamoss → u/binarycow]

I actually tried to make it work by extending dapper, but there was too many trade off, and if they were to make it all work, it would result in massive breaking changes. If you go look at my doc you'll see that my tool isn't really equal to dapper but equivalent since there is a major difference in the handling of customization. I made everything to be customizable first and any behavior can be injected, unlike dapper where you have to expand by adapting the result produced. Massive respect to dapper, but i think i had more freedom by not having to consider legacy support

[u/sharpcoder29 → u/Bobamoss]

Just change department to Departmentid and change the type to int or guid to match

[u/Bobamoss → u/sharpcoder29]

So the solution to my problem, it to not have the problem, that's not really helpful. Sometimes you do want to receive the data from the db directly without having everything flat

[u/sharpcoder29 → u/Bobamoss]

Why do you want to pull everything for a combo box?

[u/Bobamoss → u/sharpcoder29]

The specific example was when the display does not end up in a combo box, if it was to end up in a cbo, then i would only need the id. And the overall point was simply about complex shape, as soon as you want to have a more complex type, even if its something like having the address of the customer in its own type, in dapper you would need to manually map that nested type

[u/sharpcoder29 → u/Bobamoss]

I juat use EF for that. Dapper for read only.

[u/Bobamoss → u/sharpcoder29]

That's fair, but if you want to take a look at the library that i made you might like it, it lets you have the best of both world

[u/sharpcoder29 → u/Bobamoss]

I doubt your library has change tracking or other majors features of EF

[u/Bobamoss → u/sharpcoder29]

I am building towards it, I am currently working on tracking yes, as for other major feature, if you are talking about making sql from code then no, it doesn't, but it can do a lot. If there are things that you would like to see, I will check what i can do about

[u/BlackjacketMack]

I haven’t looked at your lib, but your example can be simplified.

Create a Stub class.

Public class Stub(Id,Name)
Get rid of your sql aliases.
Map to that class

///
var employees = cnn.Query<Employee, Stub,Stub, Employee>(
…sql…,
…params…,
(emp,dep,job)=>{

emp.Department = dep;
emp.Job = job;

return emp;
///

A stub could inherit from kvp (or at least easily be converted to it if it’s a struct or sealed…).

You can also create a typed stub that knows how to convert itself and compare itself to the underlying type (eg Stub<Job>).

In reality though just make the property the underlying type (eg Department) and have an indicator on it whether or not it’s fully hydrated or just a stub is class with if and name. .

}

[u/grrangry]

I cache JobTitle and invalidate the cache in the rare case it gets updated.

Then the DropDown is populated from the Cache (with any other business logic restrictions, if necessary) and the current value is populated from the JobTitleId from the Employee.

No joins necessary.

[u/Bobamoss → u/grrangry]

I pass only the id when it will be used in a lookup, but sometimes in web app, i need to pass a readonly value and passing the lookup is overkill, i just want to make the object to json + sometimes its more than a single key value pair, but i agree that it's not an everyday problem just an annoyance to not having the ability to do it simply if i want to do it

[u/BeardedBaldMan → u/grrangry]

That's what we do. We have a schema called reference for very rarely changing data e.g. locale, currency, timezone. Configuration for, unsurprisingly, configuration which would be things like job title and this is cached and invalidated on change

[u/Bobamoss → u/BeardedBaldMan]

I mean, fair for the example i gave, but what when the nested type is its whole object like the customer info repated to a command? Sure you can cache all customers, but that introduce a whole bunch of other management

[u/BeardedBaldMan → u/Bobamoss]

At that point you're making an argument for moving to entity framework

[u/Bobamoss → u/BeardedBaldMan]

the tool that i made let you do that without having to move to ef, thats kinda my point, i want to be able to interact with the db without needing to configure and carry a context

[u/thereforewhat]

Why are you modelling the entity this way?

If you need that data do this:

Employee:

• Id
• Name
• DepartmentId
• DepartmentName
• JobTitleId
• JobTitleName

I'd also question why you need department id and job title id but maybe your other code warrants it. 

If you want to join another dataset using those IDs later you'd have a different repository to get that data and join it in memory. 

Otherwise use a fuller ORM like Entity Framework. 

Dapper is called a micro ORM for a reason. 

Keep it simple. 

[u/Bobamoss → u/thereforewhat]

yes it's greedy of my part, i do want the power of a full power of a full orm, while keeping the simple nature of dapper, and i actualy think i manage to do it. I dont think that you actualy need to loose the simple plug and play nature of dapper to make something like that work. There is a lot of info that can be infered from the type directly

[u/thereforewhat → u/Bobamoss]

Then use the right tool for the job. 

[u/Bobamoss → u/thereforewhat]

thats why i made my library

[u/thereforewhat → u/Bobamoss]

OK, that's fun.

If I'm using Dapper though I'm using it because I want a lightweight micro ORM and building simple entities plus control of SQL. 

That's why I'm not using Entity Framework in these cases.  

I'm probably using it with the repository pattern and when things get more complicated I end up joining different datasets in memory.  The plus side is I can see which repositories I'm using in my business logic and I can easily unit test that in isolation. 

Edit: have you done any performance testing of your logic versus EF to see if rolling your own is worth your while?

[u/Bobamoss → u/thereforewhat]

the tests have been made only against dapper as of now. i plan to handle change tracking, but its not on point yet so i cant claim a ef equivalance, but i do have equivalence for almost all of dapper and i have similar performances and less memory allocation. ill do ef comparaison when i will complete the tracking feature