Avoid IS NULL AND IS NULL in generated SQL INNER Statement

[alexey-tyulkin]

Hi
I use the linq2sql mapping and linq2db@5.2.2

I write the below linq query

from s in ctx.t_ws_submissions
from p in ctx.t_ws_policies.Where(_ => _.submission_id == s.submission_id && _.policy_nbr == policyNumber)

and get the sql

FROM [dbo].[t_ws_submissions] [s]
INNER JOIN [dbo].[t_ws_policy] [_] ON 
   ([_].[submission_id] = [s].[submission_id] OR [_].[submission_id] IS NULL AND [s].[submission_id] IS NULL) AND [_].[policy_nbr] = @policyNumber

How to avoid OR [_].[submission_id] IS NULL AND [s].[submission_id] IS NULL ? Both columns are not null in DB.

Thanks

[global::System.Data.Linq.Mapping.TableAttribute(Name="dbo.t_ws_submissions")]
public partial class t_ws_submission : INotifyPropertyChanging, INotifyPropertyChanged {
	[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_submission_id", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
		public int submission_id {...}
}		

[global::System.Data.Linq.Mapping.TableAttribute(Name="dbo.t_ws_policy")] {
	public partial class t_ws_policy : INotifyPropertyChanging, INotifyPropertyChanged
[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_submission_id", DbType="Int NOT NULL", UpdateCheck=UpdateCheck.Never)]
		public int submission_id {....}
}

[igor-tkachev]

1. You need to make sure that at least one of your submission_id columns is not nullable.
2. Use Sql.NotNull(submission_id) to make it not nullable right in your expression.

BTW, how did you create your data model? If you use one of the linq2db tools, it should add [NotNull] attribute to your column.

[alexey-tyulkin]

1. Correct
   I'm working on legacy project and I use linq2sql mapping (https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/) and there is

var reader = new LinqToDB.Metadata.SystemDataLinqAttributeReader();
LinqToDB.Mapping.MappingSchema.Default.AddMetadataReader(reader);

I added NotNull and it doesn't help.

[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_submission_id", DbType="Int NOT NULL", UpdateCheck=UpdateCheck.Never)]
[LinqToDB.Mapping.NotNull]
public int submission_id

Is any way to save origin linq2sql mapping without any patches and get good sql for inner join?

[igor-tkachev]

class t_ws_submissions
{
	public int submission_id;
}

class t_ws_policies
{
	public int    submission_id;
	public string policy_nbr = null!;
}

[Test]
public void JoinTest([DataSources] string context)
{
	using var db = GetDataContext(context, interceptor: DoNotExecuteCommandInterceptor.Instance);

	var policyNumber = "111";

	var q =
		from s in db.GetTable<t_ws_submissions>()
		from p in db.GetTable<t_ws_policies>().Where(_ => _.submission_id == s.submission_id && _.policy_nbr == policyNumber)
		select s;

	_ = q.ToList();
}

Generated SQL:

-- SqlServer.2022
DECLARE @policyNumber NVarChar(4000) -- String
SET     @policyNumber = N'111'

SELECT
	[s].[submission_id]
FROM
	[t_ws_submissions] [s]
		INNER JOIN [t_ws_policies] [_] ON [_].[submission_id] = [s].[submission_id] AND [_].[policy_nbr] = @policyNumber

[MaceWindu]

Looked into it.
NotNull attribute will override column nullability only if ColumnAttribute doesn't have it set. LinqToDB's ColumnAttribute support optional nullability, but not System.Data.Linq.Mapping.ColumnAttribute. What is event worse - it use CanBeNull = true as default.

To fix your mapping you need to add CanBeNull = false to System.Data.Linq.Mapping.ColumnAttribute attributes where it is missing for non-nullable fields.