Any example of using join in query applyable for npoco

Question

Could you give me any example of querying and mapping result set from joined tables? I have following code:

DTO:

public class FormCatalogRecord
{
    [Column("MFormId")]
    public int Id { get; set; }

    [Column("SGroupId")]
    public int? GroupId { get; set; }

    [Column("SGroupName")]
    public string GroupName { get; set; }

    [Column("STypeFormId")]
    public int TypeId { get; set; }

    [Column("MFormKod")]
    public string Code { get; set; }

    [Column("MFormName")]
    public string Name { get; set; }

    [Column("SFormPeriodName")]
    public string Period { get; set; }

    [Column("MFormDateStart")]
    public DateTime? DateStarts { get; set; }

    [Column("MFormDateEnd")]
    public DateTime? DateEnds { get; set; }

    [Column("MFormPublish")]
    public DateTime? PublishDate { get; set; }
}

Query:

string formsQueryStr =
                    "FROM MForm " +
                    "INNER JOIN SFormPeriod ON MForm.SFormPeriodId = SFormPeriod.SFormPeriodId " +
                    "INNER JOIN SGroup ON MForm.SGroupId = SGroup.SGroupId " +
                    "ORDER BY MForm.SGroupId";

var test = db.Fetch<FormCatalogRecord>(formsQueryStr);

The result is exception: "Ambiguous column name 'SGroupId'". I tried to change attribute:

[Column("MForm.SGroupId")]
public int? GroupId { get; set; }

Now I get an exception

Invalid column name 'MForm.SGroupId'

Surprisingly I didn't found any examples in official documentation. Any help valid for npoco will be useful.

UPDATE

For my first case I'm getting following SQL generated:

SELECT 
    [MFormId] AS [Id], [SGroupId] AS [GroupId],
    [SGroupName] AS [GroupName], [STypeFormId] AS [TypeId], 
    [MFormKod] AS [Code], [MFormName] AS [Name], 
    [SFormPeriodName] AS [Period], [MFormDateStart] AS [DateStarts], 
    [MFormDateEnd] AS [DateEnds], [MFormPublish] AS [PublishDate] 
FROM 
    MForm 
INNER JOIN 
    SFormPeriod ON MForm.SFormPeriodId = SFormPeriod.SFormPeriodId 
INNER JOIN 
    SGroup ON MForm.SGroupId = SGroup.SGroupId 
ORDER BY 
    MForm.SGroupId

I think I need some way to add "MForm." before "[SGroupId] AS [GroupId]". Would be nice if column attribute could support syntax like [Column("TableName.ColumnName")]

Answer 1

NPooc and PetaPoco are different projects now it's hard to relate each with the other these days.

As for your question. Have you tried

string formsQueryStr = 
                    "SELECT MForm.*" +
                    "FROM MForm " +
                    "INNER JOIN SFormPeriod ON MForm.SFormPeriodId = SFormPeriod.SFormPeriodId " +
                    "INNER JOIN SGroup ON MForm.SGroupId = SGroup.SGroupId " +
                    "ORDER BY MForm.SGroupId";

var test = db.Fetch<FormCatalogRecord>(formsQueryStr);