Ignored DTO properties order breaks DataSourceLoader with Automapper

[DemoBytom]

When using Automapper with .ProjectTo<DTO>, with DTO that has ignored mapping properties (or unmapped), if those properties are at the top of the DTO class then DataSourceLoader.Load(..) breaks with System.InvalidOperationException: 'The LINQ expression [...] could not be translated.

We use views in the database and Automapper to map them to a DTO, and then DataSourceLoader.Load to load them from the DB with grouping/paging/sorting. In some cases it works fine, in others breaks.

XUnit test that shows the problem:

using System;
using System.Linq;
using System.Threading.Tasks;
using AutoMapper;
using AutoMapper.QueryableExtensions;
using DevExtreme.AspNet.Data;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Xunit;

namespace SomeNamespace
{
    public class AutomapperDevExtremeTests
    {
        [Fact]
        public async Task AutomapperTest()
        {
            using var context = new BloggingContext(new DbContextOptionsBuilder<BloggingContext>()
                .UseSqlServer(@$"Server=(localdb)\mssqllocaldb;Database={Guid.NewGuid()};Trusted_Connection=True;")
                .UseLoggerFactory(LoggerFactory.Create(o =>
                {
                    o.AddDebug();
                    o.AddConsole();
                }))
                .EnableSensitiveDataLogging(true)
                .Options);

            try
            {
                await context.Database.EnsureCreatedAsync();
                var result = await context.Database.ExecuteSqlRawAsync(
                    "CREATE VIEW VBlog AS SELECT BlogId, Url FROM Blogs");

                var mapperConfig = new MapperConfiguration(cfg => cfg.AddProfile<AutomapperProfile>());
                var loadOptions = new DataSourceLoadOptionsBase
                {
                    Sort = new[]
                        {
                            new SortingInfo
                            {
                                Selector = "Url", Desc = false
                            }
                        },
                    Skip = 0,
                    Take = 40
                };

                var data1 = context.VBlogs
                    .Where(b => b.Url == "http://www.SomeBlog.com")
                    .ProjectTo<VBlogDTO1>(mapperConfig);
                var loadedDataOk = DataSourceLoader.Load(data1, loadOptions);

                var data2 = context.VBlogs
                    .Where(b => b.Url == "http://www.SomeBlog.com")
                    .ProjectTo<VBlogDTO2>(mapperConfig);
                var loadedDataNotOk = DataSourceLoader.Load(data2, loadOptions);

                //Works
                var workingResult = await loadedDataOk.data.Cast<VBlogDTO1>().AsQueryable().ToArrayAsync();

                //Throws exception
                var brokenResult = await loadedDataNotOk.data.Cast<VBlogDTO2>().AsQueryable().ToArrayAsync();
            }
            finally
            {
                await context.Database.EnsureDeletedAsync();
            }
        }

        public class AutomapperProfile : Profile
        {
            public AutomapperProfile()
            {
                CreateMap<VBlog, VBlogDTO1>()
                    .ForMember(source => source.SomeIgnoredProperty1, dest => dest.Ignore())
                    .ForMember(source => source.SomeIgnoredProperty2, dest => dest.Ignore());

                CreateMap<VBlog, VBlogDTO2>()
                    .ForMember(source => source.SomeIgnoredProperty1, dest => dest.Ignore())
                    .ForMember(source => source.SomeIgnoredProperty2, dest => dest.Ignore());
            }
        }

        public class VBlogDTO1
        {
            public int BlogId { get; set; }
            public string Url { get; set; }
            public int SomeIgnoredProperty1 { get; set; }
            public string SomeIgnoredProperty2 { get; set; }
        }

        public class VBlogDTO2
        {
            public int SomeIgnoredProperty1 { get; set; }
            public string SomeIgnoredProperty2 { get; set; }
            public int BlogId { get; set; }
            public string Url { get; set; }
        }

        public class BloggingContext : DbContext
        {
            public BloggingContext(DbContextOptions<BloggingContext> options)
                : base(options)
            { }

            public DbSet<Blog> Blogs { get; set; }

            public DbSet<VBlog> VBlogs { get; set; }

            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                base.OnModelCreating(modelBuilder);

                modelBuilder.Entity<VBlog>(entity =>
                {
                    entity.HasNoKey();
                    entity.ToView("VBlog");
                    entity.Property(o => o.BlogId).HasColumnName("BlogId").IsRequired();
                    entity.Property(o => o.Url).HasColumnName("Url").IsRequired();
                });
                modelBuilder.Entity<Blog>(entity =>
                {
                    entity.ToTable("Blogs");
                    entity.HasKey(o => o.BlogId);
                });
            }
        }

        public class VBlog
        {
            public int BlogId { get; set; }
            public string Url { get; set; }
        }

        public class Blog
        {
            public int BlogId { get; set; }
            public string Url { get; set; }
            public int Rating { get; set; }
        }
    }
}

The call:

 var data1 = context.VBlogs
                    .Where(b => b.Url == "http://www.SomeBlog.com")
                    .ProjectTo<VBlogDTO1>(mapperConfig);
var loadedDataOk = DataSourceLoader.Load(data1, loadOptions);
var workingResult = await loadedDataOk.data.Cast<VBlogDTO1>().AsQueryable().ToArrayAsync();

Results in a proper generated SQL:

SELECT TOP(@__p_0) [v].[BlogId], [v].[Url]
FROM [VBlog] AS [v]
WHERE [v].[Url] = N'http://www.SomeBlog.com'
ORDER BY [v].[Url], [v].[BlogId]

The call:

var data2 = context.VBlogs
                    .Where(b => b.Url == "http://www.SomeBlog.com")
                    .ProjectTo<VBlogDTO2>(mapperConfig);
var loadedDataNotOk = DataSourceLoader.Load(data2, loadOptions);
var brokenResult = await loadedDataNotOk.data.Cast<VBlogDTO2>().AsQueryable().ToArrayAsync();

Results in an exception when generating the SQL:

System.InvalidOperationException: 'The LINQ expression 'DbSet<VBlog>
    .Where(v => v.Url == "http://www.SomeBlog.com")
    .OrderBy(v => v.Url)
    .ThenBy(v => new VBlogDTO{ 
        BlogId = v.BlogId, 
        Url = v.Url 
    }
    .IdUrl)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'

The only difference is that VBlogDTO1 has mapped properties first, and ignored properties last, while VBlogDTO2 has ignored properties first.

Problem also appears when using DataSourceLoader.LoadAsync()

I tried applying workarounds mentioned in #367 but those, far as I understand, were designed to work with tables with keys, while we work with views that don't have keys.

We have few cases when two views are mapped to one DTO, and both supply different set of data/properties , so working around the problem with changing property order is not possible, in some cases.

Tested using
Entity Framework Core 3.1
DevExtreme.AspNet.Core v20.1.7
DevExtreme.AspNet.Data v2.7.1
Automapper v10.0.0

[AlekseyMartynov]

Thanks for the detailed description.

The error occurs because DataSourceLoader fails to correctly identify a property to use for stable SQL sorting. It falls back to the first sortable property that happens to be ignored.

Use the following configuration to resolve the error.

If your DTO has a property that can serve as a logical key:

loadOptions.PrimaryKey = new[] { "prop" };

otherwise

loadOptions.DefaultSort = "prop";

[DemoBytom]

Thank you for a quick reply and sorry for late response now.
I've tested the proposed fix and it does seem to work for the provided unit test.

Unfortunately the proper production code, which is a bit more complicated, still has the same issues, even after providing either/both PrimaryKey and DefaultSort.
I wasn't yet able to track that issue down, due to the task prioritization at work, and fact I was able to provide a workaround with duplicated models, which had properties set in order to not trigger the exception; but I hope to be back at it by end of this or next week, to hopefully provide a more proper fix.