CODEWITHSUNDEEP
dappermulti-mapping
Miha
Miha

Reputation: 313

Multimapping in Dapper Without Custom SQL

Is there a way to use multimapping in Dapper in a generic way, without using custom SQL embedded in C# code?

See for example Correct use of Multimapping in Dapper

Is there a generic way to query the data from 2 related entities, where common fields are determined automatically for join?

Upvotes: 1

Views: 1350

Answers (2)

bbsimonbb
bbsimonbb

Reputation: 29002

Don't do this. Don't even think this way! Databases are long lasting and normalized. Objects are perishable and frequently denormalized, and transitioning between the two is something to do thoughtfully, when you're writing your SQL. This is really not a step to automate. Long, painful experience has convinced many of us that database abstractions (tables and joins) should not just be sucked into (or generated out of) code. If you're not yet convinced, then use an established ORM.

If, on the other hand, you absolutely want to be in control of your SQL, but its the "embedding" in string literals in C# that bugs you, then I couldn't agree more. Can I suggest QueryFirst, a visual studio extension that generates the C# wrapper for your queries. Your SQL stays in a real SQL file, syntax validated, DB references checked, and at each save, QueryFirst generates a wrapper class with Execute() methods, and a POCO for the results.

By multi-mapping, I presume you want to fill a graph of nested objects. A nice way to do this is to use one QueryFirst .sql per class in your graph, then in the partial class of the parent, add a List of children. (QueryFirst generated POCOs are split across 2 partial classes, you control one of them, the tool generates the other.)

So, for a graph of Customers and their orders... In the parent sql

select * from customers where name like @custName

The child sql

select * from orders where customerId = @customerId

In the parent partial class, for eager loading...

    public List<Orders> orders;
    public void OnLoad()
    {
        orders = new getOrders().Execute(customerId); // property of the parent POCO
    }

or for lazy loading...

    private List<Orders> _orders;
    public List<Orders> orders
    {
        get
        {                
            return _orders ?? _orders = new GetOrders().Execute(customerId);                
        }
    }

5 lines of code, not counting brackets, and you have a nested graph, lazy loaded or eager loaded as you prefer, the interface discoverable in code (intellisense for the input parameter and result). Their might be hundreds of columns in those tables, whose names you will never need to re-type, and whose datatypes are going to flow transparently into your C#.

Clean separation of responsibilities. Total control. Disclaimer : I wrote QueryFirst :-)

Upvotes: 2

Metro Smurf
Metro Smurf

Reputation: 38335

Multimapping with Dapper is a method of running multiple SQL queries at once and then return each result mapped to a specific object.

In the context of this question, Multimapping is not even relevant, re: you're asking for a way to automatically generate a SQL query from the given objects and creating the correct joins which would result in a single SQL query which is not related to Multimapping.

I suspect what you're looking for is something along the lines of the Entity Framework. There are a couple of Dapper extension projects you may want to look into which will generate some of your SQL. See: Dapper.Rainbow VS Dapper.Contrib

Upvotes: 0

Related Questions