Unnecessary where clause logic grouping and ignored IN queries

Start by reducing the posted LINQ query to a runnable EF Core 5.0.8 reproduction and compare the generated SQL with the two reported concerns: predicate grouping and array Contains translation. Done means determining whether both behaviors are expected and documenting the result or identifying a focused fix scope.

Questions

1.) In the Linq below only two of the AND clauses are grouped with parenthesis however the generated SQL nests each AND clause (except the last one for unknown reasons). I can't say this will generate incorrect results however it does make the query much more difficult to read and use for debugging purposes. Is this the expected behavior?

2.) The lookups using the string arrays are ignored. The expected SQL is something like ...CASE WHEN left(substring(rn.nativeaddress, 8, LEN(rn.nativeaddress) - 8), CHARINDEX('.', substring(rn.nativeaddress, 8, LEN(rn.nativeaddress) - 8))- 1) IN ('64','65','66','67','68','69','70','71','72','73','74','75','76','77','78','79') THEN 'Vendor1'.... I can't make sense of the generated SQL. Arguably this lookup can be done client side but it's a bit more efficient for me I can just get back a ready-to-use object from the server.

If either of these questions warrant further investigation please let me know and I will put together a runnable example to reproduce it.

Thanks.

Linq

DateTime now = DateTime.UtcNow;
string[] shopSerialNumbers = { "11", "53098" };
string[] vendor1Address = { "64", "65", "66", "67", "68", "69", "70", "71", "72", "73", "74", "75", "76", "77", "78", "79" };
string[] vendor2Address = { "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15" };
string[] vendor3Address = { "172" };
decimal[] hardwareVersions = { 1m, 3m, 0m };
			
var query = from acr in db.OwceCommRates
join jk in db.InterrogationJobs on acr.JobKey equals jk.JobKey
join rn in db.RelayNodes on acr.CellRelayId equals rn.CellRelayId
join m in db.Nodes on rn.NodeKey equals m.RelayNodeKey.Value into nodesGraph
from node in nodesGraph.DefaultIfEmpty()
let tmp = string.IsNullOrEmpty(rn.NativeAddress) ? null : rn.NativeAddress.Substring(8, rn.NativeAddress.Length - 8) 
let carrierAddress = string.IsNullOrEmpty(tmp) ? null : tmp.Substring(0, tmp.IndexOf('.'))
where
node != null
&& jk.SubmittedTime.Value.Date > now.Date
&& jk.SubmittedTime.Value.Hour < 12
&& jk.JobStatus == "W"
&& (acr.TotalEndpoints - acr.SuccessfulEndpoints) > 30
&& acr.CellRelayId > 0
&& (!shopSerialNumbers.Contains(rn.SerialNumber))
&& (hardwareVersions.Contains(node.HardwareVersion.Value) || Convert.ToInt32(node.SerialNumber) > 9999999)
&& node.Registered == 1m
select new RelayView
{
	JobKey = acr.JobKey,
	InterrogationDate = jk.StartTime.Value,
	FAR_ID = acr.CellRelayId,
	CellMaster = node.SerialNumber,
	NativeAddress = rn.NativeAddress,
	MCHInstanceId = acr.MchinstanceId,
	Carrier =   vendor1Address.Contains(carrierAddress) ? "Vendor1" : 
				vendor2Address.Contains(carrierAddress) ? "Vendor2" : 
				vendor3Address.Contains(carrierAddress) ? "Vendor3" : 
				"UNKNOWN",
	TotalEndpoints = acr.TotalEndpoints,
	NoResponse = acr.TotalEndpoints - acr.SuccessfulEndpoints
};

Generated SQL

DECLARE @__now_Date_0 datetime = '2021-10-11T00:00:00.000';

SELECT [o].[JobKey], [i].[StartTime], [o].[CellRelayId], [n].[SerialNumber], [r].[NativeAddress], [o].[MCHInstanceId], CASE
	WHEN CASE
		WHEN [r].[NativeAddress] IS NULL OR ([r].[NativeAddress] = '') THEN NULL
		ELSE SUBSTRING([r].[NativeAddress], 8 + 1, CAST(LEN([r].[NativeAddress]) AS int) - 8)
	END IS NULL OR ((CASE
		WHEN [r].[NativeAddress] IS NULL OR ([r].[NativeAddress] = '') THEN NULL
		ELSE SUBSTRING([r].[NativeAddress], 8 + 1, CAST(LEN([r].[NativeAddress]) AS int) - 8)
	END = '') AND CASE
		WHEN [r].[NativeAddress] IS NULL OR ([r].[NativeAddress] = '') THEN NULL
		ELSE SUBSTRING([r].[NativeAddress], 8 + 1, CAST(LEN([r].[NativeAddress]) AS int) - 8)
	END IS NOT NULL) THEN CAST(1 AS bit)
	ELSE CAST(0 AS bit)
END, CASE
	WHEN [r].[NativeAddress] IS NULL OR ([r].[NativeAddress] = '') THEN NULL
	ELSE SUBSTRING([r].[NativeAddress], 8 + 1, CAST(LEN([r].[NativeAddress]) AS int) - 8)
END, [o].[TotalEndpoints], [o].[TotalEndpoints] - [o].[SuccessfulEndpoints]
FROM [Analytics].[OwceCommRates] AS [o]
INNER JOIN [OWCE].[InterrogationJob] AS [i] ON CAST([o].[JobKey] AS numeric(9,0)) = [i].[JobKey]
INNER JOIN [OWCE].[RelayNode] AS [r] ON [o].[CellRelayId] = [r].[CellRelayId]
LEFT JOIN [OWCE].[Node] AS [n] ON [r].[NodeKey] = CAST([n].[RelayNodeKey] AS numeric(9,0))
WHERE ((((((([n].[NodeKey] IS NOT NULL 
AND (CONVERT(date, [i].[SubmittedTime]) > @__now_Date_0)) 
AND (DATEPART(hour, [i].[SubmittedTime]) < 12)) 
AND ([i].[JobStatus] = 'W')) 
AND (([o].[TotalEndpoints] - [o].[SuccessfulEndpoints]) > 0)) 
AND ([o].[CellRelayId] > 0)) AND [r].[SerialNumber] NOT IN (N'11', N'53098')) 
AND ([n].[HardwareVersion] IN (1.0, 3.0, 0.0) OR (CONVERT(int, [n].[SerialNumber]) > 9999999))) 
AND ([n].[Registered] = 1.0)

Versions

.net core 5.0
EF Core 5.0.8