Where clause on optional navigation causes client-side evaluation

[Ettery]

Steps to reproduce

Looking at this...

The issue

I have 3 related tables: Transaction, Instrument and Currency.

Transaction.InstrumentID -> FK -> Instrument,ID
Instrument.CurrencyID -> FK -> Currency.ID

I want a DISTINCT list of instruments which have one or more transactions, with some instrument detail.

This code:

var ins = DbContext.TradeTransactions
               .Where(t => t.RecordType == "POS" && t.Nominal != 0)
               .Select(t => new InstrumentMin(t.Instrument.Id, t.Instrument.Code, "USD"))
               .Distinct();

Produces this SQL, (which is fine except for the unnecessary second join to Instrument):

SELECT DISTINCT [t].[InstrumentID], [t.Instrument].[Code]
FROM [trade].[Transaction] AS [t]
INNER JOIN [core].[Instrument] AS [t.Instrument] ON [t].[InstrumentID] = [t.Instrument].[ID]
INNER JOIN [core].[Instrument] AS [t.Instrument0] ON [t].[InstrumentID] = [t.Instrument0].[ID]
WHERE ((([t].[RecordType] = 'POS') AND [t].[RecordType] IS NOT NULL) AND (([t].[Nominal] <> 0.0) OR [t].[Nominal] IS NULL)) 

BUT when I add a reference to retrieve the currency code:

var ins = DbContext.TradeTransactions
               .Where(t => t.RecordType == "POS" && t.Nominal != 0)
               .Select(t => new InstrumentMin(t.Instrument.Id, t.Instrument.Code, t.Instrument.Currency.Code))
               .Distinct();

the SQL generated does not have the DISTINCT keyword (major problem - returns 20x too many records) and retrieves all fields on all tables (which I only see when looking at the SQL). Also adds an ORDER BY for some reason.

SELECT [t.Instrument].[ID], [t.Instrument].[ClassCode], [t.Instrument].[Code], [t.Instrument].[CountryID], [t.Instrument].[CurrencyID], [t.Instrument].[Description], [t.Instrument].[ExpiryDate], [t.Instrument].[FpmID], [t.Instrument].[HoldingCompanyCode], [t.Instrument].[InstrumentID], [t.Instrument].[IsActive], [t.Instrument].[IsCurrent], [t.Instrument].[IsDualListed], [t.Instrument].[IsLocal], [t.Instrument].[Isin], [t.Instrument].[IssuerID], [t.Instrument].[LastUpdateTime], [t.Instrument].[OrbisFpmInstrumentId], [t.Instrument].[RefID], [t.Instrument].[Sedol], [t.Instrument].[SourceSystem], [t.Instrument].[TypeCode], [t.Instrument].[Unlisted], [t.Instrument.Currency].[ID], [t.Instrument.Currency].[Code], [t.Instrument.Currency].[IsCurrent], [t.Instrument.Currency].[IsLocal], [t.Instrument.Currency].[LastUpdateTime], [t.Instrument.Currency].[Name], [t.Instrument.Currency].[RefID], [t.Instrument.Currency].[ShortName], [t.Instrument.Currency].[Symbol], [t].[InstrumentID]
FROM [trade].[Transaction] AS [t]
INNER JOIN [core].[Instrument] AS [t.Instrument] ON [t].[InstrumentID] = [t.Instrument].[ID]
LEFT JOIN [core].[Currency] AS [t.Instrument.Currency] ON [t.Instrument].[CurrencyID] = [t.Instrument.Currency].[ID]
WHERE (([t].[RecordType] = 'POS') AND [t].[RecordType] IS NOT NULL) AND (([t].[Nominal] <> 0.0) OR [t].[Nominal] IS NULL)
ORDER BY [t.Instrument].[CurrencyID]

Further technical details

EF Core version: 1.0.0
Operating system: Win 7 Pro
Visual Studio version: 2015 Update 3

[divega]

@smitpatel we believe this is a dupe. Can you please find the other bug?

[smitpatel]

Based on LEFT JOIN generated, Instrument.Currency is optional navigation. This issue matches with description of #6199 which is duplicate of #4588

[smitpatel]

@Ettery - Can you post SQL generated for 2nd case, without using Distinct() in linq query?

[Ettery]

@smitpatel - apologies for the slow response. When I remove the Distinct() from the second case the SQL generated code is identical.

I have now added a where clause to this query and am seeing similar discrepancies between the generated SQL with and without the reference to the child entity (Currency).

Added (classCodes is a string[]):

  .Where(t => classCodes.Contains(t.Instrument.ClassCode))

Resulting SQL with no reference to t.Instrument.Currency.Code has an additional filter:

   AND [t.Instrument0].[ClassCode] IN ('TRADEDCALL')

Resulting SQL when referencing to t.Instrument.Currency the SQL is unchanged. However, EF logs an additional SQL call and appears to be applying the filter in-memory (obviously not going to be as efficient). This is what is logged for the additional call:

   SELECT [t.Instrument0].[ID], [t.Instrument0].[ClassCode] FROM [core].[Instrument] AS [t.Instrument0]

I see these lines in the debug which are not there when I don't reference currency:

Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory:Warning: The LINQ expression 'DefaultIfEmpty()' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory:Warning: The LINQ expression 'DefaultIfEmpty()' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory:Warning: The LINQ expression 'from Currency t.Instrument.Currency in {[t.Instrument.Currency_group] => DefaultIfEmpty()}' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory:Warning: The LINQ expression 'join Instrument t.Instrument in value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[Mercury.BaseModel.Core.Instrument]) on Property([t], "InstrumentId") equals Property([t.Instrument], "Id")' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory:Warning: The LINQ expression '{__classCodes_0 => Contains([t.Instrument].ClassCode)}' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory:Warning: The LINQ expression 'Contains([t.Instrument].ClassCode)' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.

[smitpatel]

dupe of #4588