Dapper multilevel nesting

Question

Dapper multilevel nesting

c# dapper

Question

I am trying to use Dapper in my project to speed up data loading (currently using EF6)

Here is my SQL

String SQL = @"select vwArtikli_Grid_V2.ArtikalID
    ,vwArtikli_Grid_V2.ArtikalNaziv
    ,Artikli_TagLista.ArtikalTagListaID
    ,Artikli_TagLista.ArtikalTagID
    ,Artikli_Stanje.ArtikalStanjeID
    ,Artikli_Stanje.ObjekatID
    ,Artikli_Stanje.Stanje
    ,Artikli_Tagovi.GrupaID
    ,Artikli_Tagovi.ArtikalTagGrupaID
    ,Artikli_Tagovi.ArtikalTagNaziv
    ,Artikli_Tagovi.ArtikalTagPrint
    ,Artikli_Tagovi.ArtikalTagSlika
    ,Artikli_Tagovi.ArtikalTagID
    ,vwArtikli_Grid_V2.ArtikalID
from Artikli_Tagovi
inner join Artikli_TagLista on Artikli_Tagovi.ArtikalTagID = Artikli_TagLista.ArtikalTagID
right outer join vwArtikli_Grid_V2 on Artikli_TagLista.ArtikalID = vwArtikli_Grid_V2.ArtikalID
left outer join Artikli_Stanje on vwArtikli_Grid_V2.ArtikalID = Artikli_Stanje.ArtikalID;

I am using my Entity Framework Entities as POCOs and they are

VwArtikliGridV2, Artikli_TagLista, Artikli_Tagovi, Artikli_Stanje

VwArtikliGridV2 has two properties

public virtual ICollection<Artikli_TagLista> Artikli_TagLista { get; set; }
public virtual ICollection<Artikli_Stanje> Artikli_Stanje { get; set; }

and Artikli_TagLista has

public virtual Artikli_Tagovi Artikli_Tagovi { get; set; }

What is the easiest way to execute the query and map my data to the POCOs or Entities ?

I tried

Dapper.Mapper

var Artikli = cn.Query<VwArtikliGridV2, Artikli_TagLista, Artikli_Stanje, Artikli_Tagovi> (SQL);

but it didnot work

I also tried Slapper.AutoMapper

List<dynamic> ArtikliUM = cn.Query<dynamic>(SQL).ToList();

Slapper.AutoMapper.Configuration.AddIdentifiers(typeof(Artikli_Tagovi), new List<string> { "ArtikalTagID" });
Slapper.AutoMapper.Configuration.AddIdentifiers(typeof(Artikli_TagLista), new List<string> { "ArtikalTagListaID" });
Slapper.AutoMapper.Configuration.AddIdentifiers(typeof(Artikli_Stanje), new List<string> { "ArtikalStanjeID" });


Artikli = (Slapper.AutoMapper.MapDynamic<VwArtikliGridV2>(ArtikliUM) as IEnumerable<VwArtikliGridV2>).ToList();

But it also did not work.

I can map VwArtikliGridV2 but i cant map any of the nested objects. They are always null.

What can I try ?

exe.bat

Popular Answer

As BlackjacketMack said in his answer, I think multiple data sets are the way to go.

You might want to look in the Dapper documentation at the features Multi Mapping to split single rows into multiple objects, and QueryMultiple to read multiple result sets from a single query.

Obviously, it would mean modifying your query to return multiple result sets, but could achieve what you are looking for.

Examples can be found here: https://github.com/StackExchange/Dapper/blob/master/Dapper.Tests/MultiMapTests.cs

https://github.com/StackExchange/Dapper/blob/master/Dapper.Tests/QueryMultipleTests.cs

In particular, you might want to take a look at method public void TestMultiMapThreeTypesWithGridReader() in MultiMapTests.cs. I found that very useful to help me understand a similar problem of parent with child collections, where the child contains different object types returned in a single row.

Stainy

View more on Stack Overflow

BlackjacketMack · Accepted Answer · 5/11/2017 8:03:46 PM

Dapper maps things flat. At first this feels like a big pain because EF nests things so easily. But once you get over the initial pain you realize how simple, predictable, and performance-oriented it is. Maybe an extra 15 min. of query-crafting for untouchable performance.

I answered a similar question here return a list of data via stored proc to dapper.

You should be able to return multiple datasets (one for the parent item, one for the underlying tags, etc.) and combine them in your app tier.

Dapper multilevel nesting

Question

Fastest Dapper Plus Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator