Projection from constants

[Marka83]

In 2.2 this code worked without problems. I've used code like this throughout whole solution:

short organizacijaId = 1;
            var artikalBrendId = 1;
            var naziv = "Test";
            var artikalBrend = _context.Set<Dual>().Select(d => new ArtikalBrendEntity() { ArtikalBrendId = artikalBrendId, OrganizacijaId = organizacijaId, Naziv = naziv });
            var joined = from a in artikalBrend
                         join o in _context.Set<OrganizacijaEntity>() on a.OrganizacijaId equals o.OrganizacijaId into ao
                         from org in ao.DefaultIfEmpty()
                         select new ArtikalBrendEntity()
                         {
                             ArtikalBrendId = a.ArtikalBrendId,
                             Naziv = a.Naziv,
                             OrganizacijaId = a.OrganizacijaId,
                             Organizacija = org
                         };

in 3.1.3 it doesn't work and error is this:

The LINQ expression 'DbSet<Dual>
    .LeftJoin(
        outer: DbSet<OrganizacijaEntity>, 
        inner: d => __p_0.OrganizacijaId, 
        outerKeySelector: o => o.OrganizacijaId, 
        innerKeySelector: (d, o) => new TransparentIdentifier<Dual, OrganizacijaEntity>(
            Outer = d, 
            Inner = o
        ))' could not be translated. Either rewrite the query in a form that can be translated

It does work if I use constants:

var artikalBrend2 = _context.Set<Dual>().Select(d => new ArtikalBrendEntity() { ArtikalBrendId = 1, OrganizacijaId = 1, Naziv = "Test" });
            var joined2 = from a in artikalBrend2
                         join o in _context.Set<OrganizacijaEntity>() on a.OrganizacijaId equals o.OrganizacijaId into ao
                         from org in ao.DefaultIfEmpty()
                         select new ArtikalBrendEntity()
                         {
                             ArtikalBrendId = a.ArtikalBrendId,
                             Naziv = a.Naziv,
                             OrganizacijaId = a.OrganizacijaId,
                             Organizacija = org
                         };

This is generated query:

SELECT 1 AS [ArtikalBrendId], N'Test' AS [Naziv], CAST(1 AS smallint) AS [OrganizacijaId], [o].[OrganizacijaId] ...
FROM [dbo].[Dual] AS [d]
LEFT JOIN [dbo].[Organizacija] AS [o] ON CAST(1 AS smallint) = [o].[OrganizacijaId]

but, unfortunately it doesn't work for DateTime properties.

We use queries like this to query all navigated properties of entity we receive through rest api. So, in example above I only use one navigated property, but in reality we would have to create 20 queries just to fetch navigated entities.

In 2.2 this is generated query:

exec sp_executesql N'SELECT [o].[OrganizacijaId], [o].[Artikal],
...
@__artikalBrendId_0 AS [ArtikalBrendId], @__naziv_2 AS [Naziv], @__organizacijaId_1 AS [OrganizacijaId0]
FROM [dbo].[Dual] AS [d]
LEFT JOIN [dbo].[Organizacija] AS [o] ON @__organizacijaId_1 = [o].[OrganizacijaId]',N'@__artikalBrendId_0 int,@__naziv_2 nvarchar(4000),@__organizacijaId_1 smallint',@__artikalBrendId_0=1,@__naziv_2=N'Test',@__organizacijaId_1=1

We have major issue now. In 2.2 our application sporadically crash (related to https://github.com/dotnet/efcore/issues/13392 https://github.com/dotnet/efcore/issues/12539)

and yet we cannot move to 3.1 because many queries are throwing 'cannot translate'. We can create a workaround for same queries, but this one we cannot because it would impact performance and it's simply to much work to go through every controller and write manual code to fetch individual navigated entities.

Steps to reproduce

This is definition for Dual (name is borrowed from Oracle). This is view with only one row:

CREATE view [dbo].[Dual] as Select 'X' as Dummy

GO

[Table("Dual", Schema = "dbo")] public class Dual { [Key] public string Dummy { get; set; } }

Further technical details

EF Core version: 3.1.3 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET Core 3.1 Operating system: Windows 10 IDE: Visual Studio 2019 16.5.4

[smitpatel]

May have been fixed in master.

[Marka83]

Does it mean that this will be fixed in next version and when could that be?

[ajcvickers]

@Marka83 Can you try using EF Core 5.0 preview 3 or the daily builds and determine if you still see this issue?

[Marka83]

@ajcvickers I've tested it with 5.0.0-preview.3.20181.2 and still doesn't work. I can send you test solution if you like, but you also have test example above.

[smitpatel]

The custom projection of artikalBrend does not depend on range variable and is fully dependent on other closure variables hence we make a parameter out of it in parameter extraction. So later it is replaced with member access over parameter. Which is not SQL hence translation failure. The query is performing GroupJoin where outer source is client side values, which is unusual.

• You can include one property from Dual in your projection and then it would stop making it a parameter and will work correctly. The property you project in artikalBrend does not need to appear in final result.
• You can rewrite this query differently since whole join is with client side objects without using a leftjoin pattern like that.

Fix: We could improve translation pipeline to add a runtime parameter (we already have infra for this) to be translation of member access over existing parameter.

[Marka83]

I cannot include property from dual because I use all properties from destination entity. I could project to intermediate anonymous type, but this is unnecessary step.

Second of all, this is not client side object. I used this type of query (worked on ef 2.2) to send values for server side processing.

Take for example some entity with 20 foreign keys. If someone sends you that entity with 20 foreign keys, you would have to use 20 queries to validate if those keys exist. Instead, I use view with single row (dual) and project it into required entity where every column is constant I send to server. After that, I can further join on whatever table I like. So, instead of 20 I have only one query.

[smitpatel]

_context.Set<OrganizacijaEntity>().Where(o => organizacijaId == o.OrganizacijaId).DefaultIfEmpty()
.Select(org => new ArtikalBrendEntity()
                         {
                             ArtikalBrendId = artikalBrendId,
                             Naziv = naziv,
                             OrganizacijaId = organizacijaId,
                             Organizacija = org
                         };

Gives same result. Doing join with a dummy table with one row from client side values is same as just writing where predicate on table directly.

[Marka83]

It can be done that way. I could further join rest of the navigation properties. I would have to do some refactoring but it can be done.