OrderBy nullable navigation property - Linq Expression could not be translated

[ssteiner]

I used version 1.2.8 today to do dynamic sorting in my project. Some of my sortable properties are actually foreign keys- and since I don't want to sort by keys, but by a property on the linked object, I dynamically adapt the query - include the subtable in the IQueryable, and rewrite the property I sort by (instead of NavigationPropertyFK, I'm using NavigationProperty.Name so in the end I'm sorting by the name property of the navigation property).

All this works fine as long as my navigation property is non nullable. If it is nullable, I'm using the np operator on the property I'm odering by, and once the IQueryable gets evaluated, I get this

The LINQ expression 'DbSet<CiscoProfile> .Cast() .Join( outer: DbSet<Cluster>, inner: e => EF.Property<Nullable<Guid>>(e, "ClusterId"), outerKeySelector: c0 => EF.Property<Nullable<Guid>>(c0, "Id"), innerKeySelector: (o, i) => new TransparentIdentifier<CiscoProfile, Cluster>( Outer = o, Inner = i )) .OrderBy(e => e.Outer != null && e.Inner != null ? e.Inner.Name : null)' 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.

my IQueryable is from EFCore 3.1 (I first created a test project with POCO classes where it worked fine).

my parameter for SortBy is

"np(Cluster.Name)"

The tip to use AsEnumerable would be killer here - it bombs out on a count (I'm doing paging and returning just the page and the count of all items - so I absolutely want to avoid AsNumerable or I'm undoing the whole performance gain I get from paging). As I said, if it's a non nullable navigation property, it works (there I'm using just "Cluster.Name"), but I'd like this to work on both nullable and non nullable navigation properties.

[StefH]

Dear @ssteiner,

Can you please provide a full working (console-app) example which demonstrates your issue ? Else it's very difficult for us to analyze the problem.

[StefH]

Hello @ssteiner, did you have time to create a full working console-example-app which demonstrates this issue?

Because when I just add this code:

var orderByNullableInt = context.Cars.OrderBy("np(NullableInt)").ToList();
foreach (var x in orderByNullableInt)
{
    Console.WriteLine($"orderBy NullableInt = {x.Brand} {x.NullableInt}");
}

Here:
https://github.com/zzzprojects/System.Linq.Dynamic.Core/blob/master/src-console/ConsoleAppEF5/Program.cs#L39

It works fine:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (38ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [c].[Key], [c].[Brand], [c].[Color], [c].[DateDeleted], [c].[DateLastModified], [c].[Extra], [c].[NullableInt], [c].[Vin], [c].[Year]
      FROM [Cars] AS [c]
      ORDER BY [c].[NullableInt]
orderBy NullableInt = Ford 1
orderBy NullableInt = Alfa 2
orderBy NullableInt = Alfa 3

[StefH]

@ssteiner Can you please taker a look at my comment and provide / update your failing example code?

[ariannepeiso]

Hello,

I'm having a similar problem with the OrderBy when using the Null Propagating expression.
I'm using version 1.2.14.

When I have this:

var query = context.Brands.Select(b => new BrandModelWithStats
  {
      Id = b.Id,
      Name = b.Name,
      BrandStat = new BrandStat
      {
          ProductCount = context.Products.Count(p => p.BrandId == b.Id),
          PublishedProductCount = context.Products.Count(p => p.BrandId == b.Id && p.State == 1)
      }
  });

  query = query.OrderBy("np(BrandStat.ProductCount)");

  var result = query.ToList();

I get this error:

System.InvalidOperationException HResult=0x80131509 Message=The LINQ expression [...] 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'

I don't have any error if I don't use the np() expression.

I uploaded a sample console app where you can see the error: https://github.com/apw82/ConsoleAppDynamicLinq

Thanks in advance for your help.

[StefH]

@ariannepeiso
What happens if you just use normal LINQ? Does that work?

[StefH]

@ariannepeiso
Did you have time to verify this?

[StefH]

I did add some more tests, and all seem correct with latest version.
See
#865