1

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?

Community
  • 1
  • 1
Miha
  • 303
  • 3
  • 19

2 Answers2

2

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

bbsimonbb
  • 27,056
  • 15
  • 80
  • 110
  • Use eager loading with great care. If you let QueryFirst manage your connection, your parent result set will be converted to a List, the whole result set instantiated, with all the children if you have them, and potentially their children. Manage your own connection, so you can treat one row at a time, then forget about it, or make sure that your eager loading only goes 1 level deep, or that your result sets are small, perhaps paginated. The whole idea of QueryFirst is that we shouldn't bring the whole DB into memory, and I've just given you the keys to do exactly that! – bbsimonbb Nov 18 '16 at 11:18
0

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

Community
  • 1
  • 1
Metro Smurf
  • 37,266
  • 20
  • 108
  • 140
  • the restriction is not to use EF! – Miha Nov 14 '16 at 19:08
  • 1
    @Miha I'm not sure how familiar you are with Dapper, but the easiest way to get over the fact Dapper not generating your SQL is to understand Dapper is nothing more than a Type Mapper (ORM is misleading since the term now typically means generating SQL as well). By Type Mapper, I mean it will simply map the fields of a SQL result to the properties of a Type. – Metro Smurf Nov 14 '16 at 19:20
  • Many thanks, Metro Smurf, for clarifying the concepts and helping me understand the limitations of Dapper as a type mapper as opposed to an ORM. I'm trying to get familiarized with Dapper because its use is required in my case. – Miha Nov 14 '16 at 19:40