Howto solve Sql Statement with Projection

[codenessah]

Hello, I am very new to Mapster and of course c# and I have a little problem with master/detail projection. Let's say I have the following entities. There are lot of more properties/columns in the database. This is just a short view.

`

public partial class Master
{
    public Master()
    {
        Detail1 = new HashSet<Detail1>();
        Detail2 = new HashSet<Detail2>();
    }

    public Guid Rowid { get; set; }
    public Guid Mandant { get; set; }
    public DateTime Created { get; set; }
    public DateTime Changed { get; set; }
    public bool Deleted { get; set; }
    public string Description { get; set; }
    public string DescriptionShort { get; set; }
    public int Summary { get; set; }
    public bool Calculated { get; set; }
    ...
    public virtual ICollection<Detail1> Detail1 { get; set; }
    public virtual ICollection<Detail2> Detail2 { get; set; }
}

public partial class Detail1
{
    public Detail1()
    {
    }

    public Guid Rowid { get; set; }
    public Guid Mandant { get; set; }
    public Guid Master { get; set; }
    public DateTime Created { get; set; }
    public DateTime Changed { get; set; }
    public bool Deleted { get; set; }
    public int Id{ get; set; }
    public string IdString { get; set; }
    public string IdDescription { get; set; }
    ...
    public virtual Master MasterNavigation { get; set; }
}

`

The Detail2 is another entity which looks similar. Although there are more properties/columns here. And those Dto's

`

public class MasterDto
{
    public Guid Rowid { get; set; }
    public string Description { get; set; }
    public string DescriptionShort { get; set; }
    public List<Detail1Dto> Detail1Dto { get; set; }
}

public partial class Detail1Dto
{
    public Guid Rowid { get; set; }
    public int Id{ get; set; }
    public string IdString { get; set; }
    public string IdDescription { get; set; }
}

`

When you perform a simple include query like this without using Mapster

var _query = _context.Master.Include(i => i.Detail1).ToList()

then the sql output shows something like this with all selected columns (not all involved here). Leads to a very bad performance when you select all columns and only needed a few.

SELECT [k].[rowid], [k].[Mandant], [k].[Created], [k].[Changed], [k].[Deleted], [k].[Description], [k].[DescriptionShort], [k].[Summary], [k].[Calculated], [t].[rowid], [t].[Created], [t].[Changed], [t].[Deleted], [t].[Id], [t].[IdString], [t].[IdDescription], [t].[Mandant], [t].[Master], ... FROM [dbo].[Master] AS [k] LEFT JOIN ( SELECT [k0].[rowid], [k0].[Created], [k0].[Changed], [k0].[Deleted], [k0].[Id], [k0].[IdString], [k0].[IdDescription], [k0].[Mandant], [t].[Master], ... FROM [SpeiseplanSchnittstelle].[Kategorie] AS [k0] WHERE (([k0].[mandant] = '77b0af67-48fb-40c6-b84f-5ae4a22f5573') AND ([k0].[deleted] = CAST(0 AS bit)) ) AS [t] ON [k].[rowid] = [t].[Master] WHERE ([k].[mandant] = '77b0af67-48fb-40c6-b84f-5ae4a22f5573') AND ([k].[deleted] = CAST(0 AS bit)) ORDER BY [k].[rowid]

My query with Mapster will look like this and the output query looks like expected.

`

        var _config = TypeAdapterConfig<Master, MasterDto>.NewConfig()
            .PreserveReference(true)
            .Map(d => d.Detail1Dto, s => s.Detail1.Adapt<List<Detail1Dto>>())
            .Config;

        var _query = _context.Master
            .Include(i => i.Detail1)
            .ProjectToType<MasterDto>(_config)
            .ToList();

`

The Sql output is similar to the above one with all columns from Detail1. Only the Master columns matched with the MasterDto! It is the generated Sql query that I could not managed.

`

SELECT [k].[rowid], [k].[Description], [k].[DescriptionShort], [t].[rowid], [t].[Created], [t].[Changed], [t].[Deleted], [t].[Id], [t].[IdString], [t].[IdDescription], [t].[Mandant], [t].[Master], ... FROM [dbo].[Master] AS [k] LEFT JOIN ( SELECT [k0].[rowid], [k0].[Created], [k0].[Changed], [k0].[Deleted], [k0].[Id], [k0].[IdString], [k0].[IdDescription], [k0].[Mandant], [t].[Master], ... FROM [SpeiseplanSchnittstelle].[Kategorie] AS [k0] WHERE (([k0].[mandant] = '77b0af67-48fb-40c6-b84f-5ae4a22f5573') AND ([k0].[deleted] = CAST(0 AS bit)) ) AS [t] ON [k].[rowid] = [t].[Master] WHERE ([k].[mandant] = '77b0af67-48fb-40c6-b84f-5ae4a22f5573') AND ([k].[deleted] = CAST(0 AS bit)) ORDER BY [k].[rowid] `

My goal is to have only the columns selected that are matched in the Dto's. like this

SELECT [k].[rowid], [k].[Description], [k].[DescriptionShort], [t].[rowid], [t].[Id], [t].[IdString], [t].[IdDescription] ...

I know I could do that if I make a specialized ".Select(...)", but I thought that might be possible with Mapster "ProjectToType()" or something else. What am I doing wrong. How is this scenario solved. It would be nice if someone can point me to the right solution.

LG codenessah

[codenessah]

I made a big mistake in the TypeAdapterConfig. It should be:

        var _config = TypeAdapterConfig<Master, MasterDto>.NewConfig()
            .PreserveReference(true)
            .Map(d => d.Detail1Dto, s => s.Detail1)
            .Config;

And you don't need the Include!

var _query = _context.Master
            .ProjectToType<MasterDto>(_config)
            .ToList();

And now, everything works perfect. Thank me (you) ;-)

[andrerav]

Good to hear @codenessah. Be aware that if you change this query to an async query in the future, you might run into issue with calling ProjectToType() before materializing the query (i.e calling ToList() or ToListAsync()).