Joins

Apr 17, 2013 at 7:34 AM
Hello,

I'm having some issues with a join I'm trying to do between two classes, it seems to work if I try to join one way, but not the other.

Here are my two classes:
public class Series
{
    public int Id { get; set; }
    public string Title { get; set; }
    public List<Episode> Episodes { get; set; }
}

public class Episode
{
    public int Id { get; set; }
    public int SeriesId { get; set; }
    public string Title { get; set; }
    public Series Series { get; set; }
}
If I run this query:
Query<Series>().Join<Series, Episode>(JoinType.Inner, s => s.Episodes, (s, e) => s.Id == e.SeriesId)
I get the Series with the episodes populated, but running this query:
Query<Episode>().Join<Episode, Series>(JoinType.Inner, e => e.Series, (e, s) => e.SeriesId == s.Id)
I get the episode without the Series being populated.

If I run the SQL that MDM is building directly against the database I get the expected result in the output. Is this type of join supported on MDM?

Thanks for the great work, I'm absolutely loving working with MDM.
Coordinator
Apr 17, 2013 at 5:19 PM
Hi Markus,

Currently, the best way of accomplishing your goal of having the child reference its parent would be to have the child lazy load the parent:

Mappings:
FluentMappings mappings = new FluentMappings();
mappings
    .Entity<Series>()
        .Columns.AutoMapSimpleTypeProperties()
            .For(s => s.Id)
                .SetPrimaryKey()
        .Relationships.AutoMapICollectionOrComplexProperties()

    .Entity<Episode>()
        .Columns.AutoMapSimpleTypeProperties()
            .For(e => e.Id)
                .SetPrimaryKey()
                .SetAltName("EpisodeId")
            .For(e => e.Title)
                .SetAltName("EpisodeTitle")
        .Relationships.MapProperties<Episode>()
            .For("_series")
                .LazyLoad<Series>((db, e) => db.Query<Series>().Where(s => s.Id == e.SeriesId).ToList().FirstOrDefault());
Models:
    public class Series
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public List<Episode> Episodes { get; set; }
    }

    public class Episode
    {
        private dynamic _series;

        public int Id { get; set; }
        public int SeriesId { get; set; }
        public string Title { get; set; }
        public Series Series
        {
            get
            {
                return _series;
            }
        }
    }
Note that if you don't want to use the dynamic keyword (or if you are using an older .NET framework) you can use this instead for your _series backing field:
        //private dynamic _series;
        private LazyLoaded<Series> _series;
Coordinator
Apr 17, 2013 at 5:35 PM
After thinking about your problem some more, it would be nice if MDM had an easier way of handling parent references like this.
Ideally, any child relationship should be able to get a reference to its direct parent, or maybe even to any parent in the graph hierarchy.

I will look into adding this ability. Until then, you can use the lazy load feature.

Alternatively, if you require that the graph is eagerly loaded, then you could not mark Episode -> Series as a relationship, and then loop through your results and set the parent of each child manually.
List<Series> series = db.Query<Series>().Join<Series, Episode>(JoinType.Inner, s => s.Episodes, (s, e) => s.Id == e.SeriesId);
series.ForEach(s => s.Episodes.ForEach(e => e.Series = s));
Apr 17, 2013 at 6:14 PM
I'm currently doing the joins manually (in memory), similar to how you describe, because LazyLoad would likely be a performance hit, due to the fact that there may be 50 episodes that span across multiple series, multiple episodes may have the same series, but its not guaranteed.

To give some context, for this particular query I'm using the results to build a calendar, the query grabs episodes that aired between two dates and returns them to the user, but the user also needs to know what the series title is (as well as some other information thats held in the series).

Thanks for considering this.
Coordinator
Apr 17, 2013 at 8:52 PM
I was able to modify the code to automatically detect and copy references to a parent.
Hopefully I can polish this off and test it with some different use case scenarios this evening when I have some more time, but it looks promising so far!

Jordan
Coordinator
Apr 18, 2013 at 12:17 AM
Edited Apr 18, 2013 at 12:20 AM
I just pushed the latest changes to GitHub:
https://github.com/JordanMarr/Marr.DataMapper

To summarize, MDM now detects if you have a Relationship that references an already created parent. If so, it will automatically copy a reference of the parent to the Relationship property.

So in the case of your first example (below), MDM will detect that the Series has already been created, and will then automatically copy a reference of it to each Episode's "Series" property.
db.Query<Series>().Join<Series, Episode>(JoinType.Inner, s => s.Episodes, (s, e) => s.Id == e.SeriesId)
In the case of your second example (below), Episode is the root object in your graph, so MDM will create the Series object from the data reader and then set it to your Series property.
db.Query<Episode>().Join<Episode, Series>(JoinType.Inner, e => e.Series, (e, s) => e.SeriesId == s.Id)
Coordinator
Apr 19, 2013 at 2:30 AM
A test release with these changes is available here:

https://marrdatamapper.codeplex.com/releases/view/105341
May 10, 2013 at 6:10 AM
Hey Jordan,

Sorry for not getting back to you sooner, I was able to get this tested and its working quiet well. I didn't realize at first that I needed to use aliases for columns common between the two, but once that was sorted everything looks great.

I ended up needing paging for SQLite as well, so I'll be making a Pull Request on Github shortly.