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 ?
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.
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.