Avoid IS NULL AND IS NULL in generated SQL INNER Statement #4227
Replies: 4 comments
-
BTW, how did you create your data model? If you use one of the linq2db tools, it should add [NotNull] attribute to your column. |
Beta Was this translation helpful? Give feedback.
-
I added NotNull and it doesn't help.
Is any way to save origin linq2sql mapping without any patches and get good sql for inner join? |
Beta Was this translation helpful? Give feedback.
-
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 |
Beta Was this translation helpful? Give feedback.
-
Looked into it. To fix your mapping you need to add |
Beta Was this translation helpful? Give feedback.
-
Hi
I use the linq2sql mapping and [email protected]
I write the below linq query
and get the sql
How to avoid OR [_].[submission_id] IS NULL AND [s].[submission_id] IS NULL ? Both columns are not null in DB.
Thanks
Beta Was this translation helpful? Give feedback.
All reactions