search

cc946079647 / dapper-dot-net

Automatically exported from code.google.com/p/dapper-dot-net
Other
0 stars 0 forks source link

Suggestion for automatic multi-mapping #50

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago 
Is any work planned to optionally auto-multi-map based off the 
AssociationAttribute (or similar)? Or is this completely off the table in order 
to stay with POCO or for some other reason?

Nature of relation would be relatively easy (for 1-1, 1-n at least) to 
determine (check for IEnumerable<T>/T on involved types)

Manually doing this is kind of tedious (foot work, identity tracking, LOC), 
especially for multiple joins. Even though it may be reusable in a limited 
fashion in the simple cases.

Example for the attribute usage:

[Association("OtherEntityName", "ThisKey", "OtherKey")]

A first step would not even need caching as it pays in terms of productivity.

I'll do it myself eventually if no one else will do it. But not in the coming 
months. Just asking :-)

PS:
AssociationAttribute is stashed away in various DLLs and various forms across 
framework versions. But why not just copy it into Dapper for .NET < 4 and keep 
to the System.ComponentModel.DataAnnotations namespace for .NET 4, which seems 
useful in and of itself.

Original issue reported on code.google.com by skarmans...@gmail.com on 20 Jul 2011 at 10:36

GoogleCodeExporter commented 9 years ago 
I have been thinking about this, can you provide a comprehensive sample on how 
you think this should work

Original comment by sam.saff...@gmail.com on 25 Jul 2011 at 6:03

GoogleCodeExporter commented 9 years ago 
What about limitless multi mapping?!?

Because it seems that you are doing something that should be done in a query or 
stored procedure in the database

In my project I have a HUGE quantity of data that must be returned from the 
database for every query.

So, I have developed, with the help of reflection, some extensions to the 
IDataReader and IDataRecord that can transform this kind of SELECT:

 SELECT
       T.TeamID
      ,T.Name
      ,T.FullName
      ,M.MediaID    AS Emblem_MediaID
      ,M.Url        AS Emblem_Url
      ,C.AreaID     AS Country_AreaID
      ,C.Name       AS Country_Name
      ,F.MediaID    AS Country_Photo_MediaID
      ,F.Url        AS Country_Photo_Url
      ...
 FROM Team T
 INNER JOIN Media M...
 INNER JOIN Area C...
 INNER JOIN Media F...
...

Into a POCO like this one:

    public partial class Team
    {
      public int TeamId { get; set; }
      public string Name { get; set; }
      public string FullName { get; set; }

      public Area Country { get; set; }
      public Media Emblem { get; set; }

    }

How? By splitting the strings with "_" and discovering on-the-fly if the 
property exists, its type, and if it is a simple or complex one like another 
POCO.

And has you can see, there are other POCO's involved like Area or Media.

Can you do this FASTER? Because its done every single time I get data from the 
database, and the example is just that. Multiply the columns returned by 10 or 
20 and add other result sets and you can start to see the real picture.

And to be honest, I'm not that brilliant to change dapper...

If nobody answers this here I'm going to escalate it into a new issue.

Thanks in advance dudes

Original comment by sdsilve...@gmail.com on 2 Dec 2011 at 12:01

GoogleCodeExporter commented 9 years ago 
Add unlimited multimapping and you'll have the perfect micro orm!

Original comment by toledoma...@gmail.com on 18 Jul 2012 at 4:18

GoogleCodeExporter commented 9 years ago 
If anybody's interested, I can submit a pull request for my implementation of 
"unlimited" multimapping...

Original comment by mal.earn...@btinternet.com on 20 Jul 2012 at 2:37

GoogleCodeExporter commented 9 years ago 
Definitely interested...that would be a great addition...

Original comment by abertagn...@gmail.com on 20 Jul 2012 at 2:39

GoogleCodeExporter commented 9 years ago 
Ok, it's different to sdsilve's implementation as it still users Dapper's 
internals methods. I'll get a pull request submitted within the next 24 hours.

If Sam/Marc are watching, they may want to take a look as I could do with their 
judgment as to any potential perf issues etc.

Cheers

Original comment by mal.earn...@btinternet.com on 20 Jul 2012 at 2:43

GoogleCodeExporter commented 9 years ago 
Same as #5!! Very interested!

Original comment by toledoma...@gmail.com on 20 Jul 2012 at 2:44

GoogleCodeExporter commented 9 years ago 
Me to, although, as mal.earnest wrote and well, my problem is slightly 
different, because I must be able to UNFLAT unlimited column names, into 
Objects and Props, by splitting them on a '_' char, instead of splitting by any 
given column.

Using reflection in real time becomes slower than desired.

Another example: HomeTeam_Stadium_Country_AreaId

I'm looking at ValueInjector because its a bit faster than my own code, but it 
still has the same problem, it uses real time refection mapping.

Trying to figure out if I can cache something.

Original comment by sdsilve...@gmail.com on 20 Jul 2012 at 3:26

GoogleCodeExporter commented 9 years ago 
Is the purpose of aliasing and splitting on the '_' character purely to get 
around running out of generic parameters (i.e. <TFirst, TSecond, ..., TFifth, 
TReturn>)?

If so, then I think your issue is the same as mine where I ran out because of 
many JOINs to related entities.

In a nutshell, my solution is (excusing any typos):

SELECT
       T.TeamID
      ,T.Name
      ,T.FullName
      ,M.MediaID
      ,M.Url
      ,C.AreaID
      ,C.Name
      ,F.MediaID
      ,F.Url
      ...
 FROM Team T
 INNER JOIN Media M...
 INNER JOIN Area C...
 INNER JOIN Media F...
...

var team = Query<Team, Team>(
    sql,
    new Type[] { typeof(Media), typeof(Area), ..., typeof(Media) },
    (team, entities) =>
    {
        team.TheMedia1 = (Media)entities[0];
        team.TheMedia1.TheArea = (Area)entities[1];
        ...
        team.TheMedia1 = (Media)entities[14];
        return team;
    }
).FirstOrDefault();

"team" would then be the "full" object-graph.

Does that sound like it fits your needs? As I say, it's using all of the Dapper 
internals and so *shouldn't* add any extra overhead - apart from the 
"late-binding" required casting in the mapping function which should be 
negligible, but necessary for the "unlimited" nature...

Mal

Original comment by mal.earn...@btinternet.com on 20 Jul 2012 at 3:52

GoogleCodeExporter commented 9 years ago 
Sorry... forgot:

    splitOn: "MediaID,AreaId,...,MediaId"

Mal

Original comment by mal.earn...@btinternet.com on 20 Jul 2012 at 3:54

GoogleCodeExporter commented 9 years ago 
Reading through skarmans initial post at the top of the issue, I think it 
should then be relatively easy for him to apply his own attribute based 
automatic mapping generation by passing the required types and employing some 
custom mapper code...

Mal

Original comment by mal.earn...@btinternet.com on 20 Jul 2012 at 3:58

GoogleCodeExporter commented 9 years ago 
I'm sorry for this :)

Real case scenario example bellow:

 SELECT
      CompetitionID,
      M.MatchID,
      FixtureDate,
      FixtureTime,
      StartTime,
      Status_Name,
      Status_Value,
      Period_Name,
      Period_Value,
      WinnerTeamID,
      HomeTeamScore,
      HomeTeamPenaltiesScore,
      AwayTeamScore,
      AwayTeamPenaltiesScore,
      Attendance,
      Stadium_StadiumID,
      Stadium_Name,
      Stadium_Country_AreaID,
      Stadium_Country_Name,
      Stadium_Country_Initials,
      Stadium_Location,
      HomeTeam_TeamID,
      HomeTeam_Name,
      HomeTeam_FullName,
      HomeTeam_OfficialName,
      HomeTeam_Initials,
      HomeTeam_Tags,
      HomeTeam_Organization_OrganizationID,
      HomeTeam_HomeColor,
      HomeTeam_AwayColor,
      HomeTeam_Emblem_MediaID,
      HomeTeam_Emblem_Width,
      HomeTeam_Emblem_Height,
      HomeTeam_Emblem_Url,
      HomeTeam_Emblem_AlternateUrl,
      HomeTeam_Country_AreaID,
      HomeTeam_Country_Name,
      HomeTeam_Country_Initials,
      HomeTeam_Country_Photo_MediaID,
      HomeTeam_Country_Photo_Width,
      HomeTeam_Country_Photo_Height,
      HomeTeam_Country_Photo_Url,
      HomeTeam_Country_Photo_AlternateUrl,
      HomeTeam_HomeKit_MediaID,
      HomeTeam_HomeKit_Width,
      HomeTeam_HomeKit_Height,
      HomeTeam_HomeKit_Url,
      HomeTeam_HomeKit_AlternateUrl,
      AwayTeam_TeamID,
      AwayTeam_Name,
      AwayTeam_FullName,
      AwayTeam_OfficialName,
      AwayTeam_Initials,
      AwayTeam_Tags,
      AwayTeam_Organization_OrganizationID,
      AwayTeam_HomeColor,
      AwayTeam_AwayColor,
      AwayTeam_Emblem_MediaID,
      AwayTeam_Emblem_Width,
      AwayTeam_Emblem_Height,
      AwayTeam_Emblem_Url,
      AwayTeam_Emblem_AlternateUrl,
      AwayTeam_Country_AreaID,
      AwayTeam_Country_Name,
      AwayTeam_Country_Initials,
      AwayTeam_Country_Photo_MediaID,
      AwayTeam_Country_Photo_Width,
      AwayTeam_Country_Photo_Height,
      AwayTeam_Country_Photo_Url,
      AwayTeam_Country_Photo_AlternateUrl,
      AwayTeam_HomeKit_MediaID,
      AwayTeam_HomeKit_Width,
      AwayTeam_HomeKit_Height,
      AwayTeam_HomeKit_Url,
      AwayTeam_HomeKit_AlternateUrl,
      HomeTeamSlot_CompetitionID,
      HomeTeamSlot_Position,
      HomeTeamSlot_Name,
      HomeTeamSlot_MatchID,
      AwayTeamSlot_CompetitionID,
      AwayTeamSlot_Position,
      AwayTeamSlot_Name,
      AwayTeamSlot_MatchID,
      MatchSet_Value,
      MatchSet_Name,
      LiveCoverage,
      Aggregate_MatchID,
      Aggregate_WinnerTeamID,
      Aggregate_HomeTeamScore,
      Aggregate_AwayTeamScore
  FROM   Views.Match M
         INNER JOIN @Matches FM ON FM.MatchID = M.MatchID

Original comment by sdsilve...@gmail.com on 20 Jul 2012 at 4:04

GoogleCodeExporter commented 9 years ago 
I think both Mal and Sergio are talking about the same.
I think with both solutions you can build a graph of unlimited deep and 
extension, but I prefer Mal's one because it doesn't introduce the requirement 
of using underscores (If I understand...)

Original comment by toledoma...@gmail.com on 20 Jul 2012 at 4:08

GoogleCodeExporter commented 9 years ago 
As you can see Mal, the amount of flatten data is huge, and given that, your 
solution seems a bit cumbersome for me. I'm prototyping a solution with a 
colleague, that uses ValueInjecter to unflat the data. Maybe we can create an 
extension for Dapper. 

I'll keep you guys posted.

Original comment by sdsilve...@gmail.com on 20 Jul 2012 at 4:10

GoogleCodeExporter commented 9 years ago 
Yes, I think we are both along the same lines, although Sergio's issue is over 
and above "unlimited" mapping as it looks like he can't do anything about the 
aliased column names...

...or can you, segio? I mean, is ditching the view and just executing the SQL 
without the column aliases feasible? If so, then that would mean that my 
solution should work out-of-the-box for you...?

Mal

Original comment by mal.earn...@btinternet.com on 20 Jul 2012 at 4:14

GoogleCodeExporter commented 9 years ago 
Well toledoma... the real problem is the fact that the multimapping code would 
be so huge, that I would prefer not using dapper, to be honest.

Not to mention the fact that I do not have Table handles, I would have to use 
object names etc... to much complication.

Not to mention I have dozens of Stored Procs with different selects.

It's fine when you have simple objects to move around, but in my case it's just 
a complete overkill.

Original comment by sdsilve...@gmail.com on 20 Jul 2012 at 4:14

GoogleCodeExporter commented 9 years ago 
No worries, Sergio - keep us posted though! :)

Thanks,

Mal

Original comment by mal.earn...@btinternet.com on 20 Jul 2012 at 4:15

GoogleCodeExporter commented 9 years ago 
Here is my vote on limitless multi-mapping...really interested in unlimited 
mutimapping

Original comment by chintama...@gmail.com on 30 Jul 2012 at 8:12

GoogleCodeExporter commented 9 years ago 
I am looking forward for unlimited multi-mapping solution, I have started using 
it, and reach a point where I need more than five mappings. THe DB used in 
project is very flat in nature and cannot be normalized (product DB).I need 
more objects to be filled from SP.

Original comment by SumitSa...@gmail.com on 30 Jul 2012 at 8:19

GoogleCodeExporter commented 9 years ago 
Please see my pull request for my implementation for this. There's an issue 
with the .Query<> method at the moment, but the .Read<> method works fine.

https://github.com/SamSaffron/dapper-dot-net/pull/44

Original comment by mal.earn...@btinternet.com on 30 Jul 2012 at 12:01

GoogleCodeExporter commented 9 years ago 
Cool...this actually kills two of my tasks...easier to abstract dapper now. I 
will not have multiple overloaded methods and multi-mapping more than 5 types.
May we know when is this scheduled for release?
Thanks a ton.

Original comment by chintama...@gmail.com on 9 Aug 2012 at 5:15

GoogleCodeExporter commented 9 years ago 
That's great - and exactly why I made the changes as I needed to drive Dapper 
in an abstract way without having to hand-code the multi-mapping for each of my 
entities. In many cases the were > 10 entities involved.

I'm running the code in the pull request without issue now although I'm hoping 
Marc will cast his eye over it soon and hopefully merge it into the main branch.

Mal

Original comment by mal.earn...@btinternet.com on 9 Aug 2012 at 8:53

GoogleCodeExporter commented 9 years ago 
Hello - was this included in 1.12 release? I don't see it in release notes?

Thanks,
Mani

Original comment by chintama...@gmail.com on 6 Nov 2012 at 1:21

GoogleCodeExporter commented 9 years ago 
Im really interested as i have to multimap more than ten tables for my project. 
When will it be included?

Original comment by t...@icreativos.com on 28 Nov 2012 at 5:49

GoogleCodeExporter commented 9 years ago 
I've been successfully using the code in my pull request for some time now. 
Have a look at the tests in that pull request for an example of its use.

Maybe add a comment to my pull request to see if it'll get pushed into the main 
branch soon.

https://github.com/SamSaffron/dapper-dot-net/pull/44

Mal

Original comment by mal.earn...@btinternet.com on 28 Nov 2012 at 7:06