Mapping Foreign Keys Error (3.1.0-beta3)

[ghost]

Hi,
I am trying to use version 3.X because of the new FETCH methods.
But I have not found any working example on how to map Foreign Keys.
Below you find my classes (from Chinnok Database) and what I am trying to do.
I have 2 questions:

• How do I map Relationships ( Album.Artist and Artist.Albums ) between tables correctly ?
  I prefer Fluent Mapping for this task.
• How do I map "Required" properties using Fluent Mapping instead of [Required] Data Annotation ?

Thanks

I am trying to FETCH Album with the Artist (please see code below)
[Reference] in the class does not work
.Reference() in the mapping generates an error "Value cannot be null. Name of parameter: key"

Console.WriteLine("\nAlbum + Artist");
sql = @"
SELECT
    Album.AlbumId
    ,Album.Title
    ,Album.ArtistId
    -- Artist    
    ,Artist.ArtistId Artist__ArtistId
    ,Artist.Name Artist__Name
FROM
    Album
    INNER JOIN Artist ON
        Artist.ArtistId = Album.AlbumId
";
albums = database
    .Fetch<Album>(sql)
    .GetRange(0, 3);
foreach (Album x in albums)
{
    Console.WriteLine("Id: {0} - Title: {1} - Artist: [{2}]",
        x.AlbumId, x.Title, (x.Artist == null ? "?" : x.Artist.Name));
}

Album

public partial class Album
{
    [Required]
    public virtual int AlbumId { get; set; }
    [Required]
    public virtual string Title { get; set; }
    public virtual int ArtistId { get; set; }

    //[Reference(ReferenceType.Foreign, ColumnName = "ArtistId", ReferenceMemberName = "ArtistId")]
    public virtual Artist Artist { get; set; } // ArtistId // ???
}

Artist

public partial class Artist
{
    [Required]
    public virtual int ArtistId { get; set; }
    public virtual string Name { get; set; }
    public virtual IList<Album> Albums { get; set; }
}

AlbumMapping

public class AlbumMapping : Map<Album>
{
    public AlbumMapping()
    {
        TableName("Album");
        PrimaryKey(x => x.AlbumId);
        Columns(x =>
        {
            x.Column(y => y.AlbumId)
                .WithDbType(typeof(Int32))
                .WithName("AlbumId");
            x.Column(y => y.Title)
                .WithDbType(typeof(String))
                .WithName("Title");
            x.Column(y => y.ArtistId)
                .WithDbType(typeof(Int32))
                .WithName("ArtistId");
            x.Column(y => y.Artist)
                .Reference(z => z.ArtistId, NPoco.ReferenceType.Foreign);
        });
    }
}

ArtistMapping

public class ArtistMapping : Map<Artist>
{
    public ArtistMapping()
    {
        TableName("Artist");
        PrimaryKey(x => x.ArtistId);
        Columns(x =>
        {
            x.Column(y => y.ArtistId)
                .WithName("ArtistId")
                .WithDbType(typeof(Int32));
            x.Column(y => y.Name)
                .WithName("Name")
                .WithDbType(typeof(String));
        });
    }
}

[kersane]

hi @siegmar i am trying to do the same with version dev3.1, as far as i understand ReferenceType.Foreign is constrained to only include the referenced object, therefore you can't add ArtistId property in your Album class. My workaround is to change the reference type to ReferenceType.OneToOne, if you would like to keep ArtistId property.
Edit:
Mark your Artist property as [ResultColumn]

hi @schotime, when i debugging PocoData.Build(), is it really necessary to traverse all chained properties across references? in pocoData.AllColumns = ...
from sample above, NPoco would generate PocoColumn for Album class as below:
AlbumId, Title, ArtistId, Artist__ArtistId, Artist__Name, Artist__Albums, Artist__Albums__AlbumId, Artist__Albums__Title, Artist__Albums__ArtistId

Is that a desired behavior or am i miss configured something?

[schotime]

@siegmar The workaround that @dwirawan suggested is correct. If you want both the object and the int column then OneToOne is the way to go.

@dwirawan That is the desired behavior, otherwise you wouldn't be able to map to them.

[kersane]

@schotime thanks for clarifying the behavior. However i noticed something interesting in FluentMappingsPocoDataBuilder. Column lookup key does not match for nested objects. Here is what i noticed.

Album mapping

public class AlbumMappings : Mappings<Album>
{
  AlbumMappings()
  {
    Columns( x=>
    {
        // column is generated with key 'Artist'.
        x.Column(c => c.Artist).Reference(f => f.ArtistId, ReferenceType.OneToOne).Result(); 
    }, true); // use explicit column definition
  }
}

FluentMappingPocoDataBuilder

protected override ColumnInfo GetColumnInfo(MemberInfo mi, MemberInfo[] memberInfos)
{
  // key is 'Artist__ArtistId'
  var key = PocoColumn.GenerateKey(memberInfos.Concat(new[] { mi }));
  bool explicitColumns = typeConfig.ExplicitColumns ?? false;
  // well, column not defined ??
  var isColumnDefined = typeConfig.ColumnConfiguration.ContainsKey(key);
  ...

  // resulting column being ignored for further column mapping aliasing
  if (explicitColumns && !isColumnDefined)
    columnInfo.IgnoreColumn = true;
}

Those problems does not occurs using Attributes, only with FluentMappings. Any clean work around for this problems?

[ghost]

Thanks @dwirawan and @schotime for the answers.