Linq left outer join with exclusion via Linq2db

Question

Using Linq2db and Ms Access I want to select all sectors that don't have targets ergo I want to execute left outer join with exclusion:

Dim q10 = From s In db.Sectors
          From t In db.Targets.Where(Function(f) f.id_sector = s.Id).DefaultIfEmpty
          Where t Is Nothing
          Select s

Linq2db resolves this as:

--  Access
SELECT
    [t2].[Id],
    [t2].[Name]
FROM
    [tblSector] [t2]
        LEFT JOIN [tblTarget] [t1] ON ([t1].[id_sector] = [t2].[Id])
WHERE
    [t1].* IS NULL <=========== HERE

which obviously is wrong.

I tried also:

Dim q10 = From s In db.Sectors
          From t In db.Targets.Where(Function(f) f.id_sector = s.Id And f Is Nothing).DefaultIfEmpty
          Select s

receiving:

--  Access
SELECT
    [t2].[Id],
    [t2].[Name]
FROM
    [tblSector] [t2]
        LEFT JOIN [tblTarget] [t1] ON ([t1].[id_sector] = [t2].[Id] AND [t1].* IS NULL) <=========== HERE

Summarize, I need:

SELECT
    [t2].[Id],
    [t2].[Name]
FROM
    [tblSector] [t2]
        LEFT JOIN [tblTarget] [t1] ON ([t1].[id_sector] = [t2].[Id])
WHERE
    [t1].[id_sector] IS NULL

How to write in condition Where t1.id_sector Is Nothing (id_sector is FK so it is Integer so it can't be Nothing.

Perhaps you really need RIGHT JOIN. Whatever it's called, you probably need "All records from Sector and only those from Target that match". — June7, Feb 04 '18 at 19:46
I am not sure what is the difference between the first option and the option you need - if `id_sector` is null then the entire raw is null, so there is no actual difference between the two queries — gilmishal, Feb 05 '18 at 11:52
@gilmishal Exactly. No difference. The problem is that `Linq2db` can't resolve proper linq query to SQL query. So my question is how to write linq query in `linq2db` to achieve result as in last SQL example. — yarecky, Feb 05 '18 at 15:54

score 0 · Accepted Answer · answered Feb 05 '18 at 16:34

I found an answer. It's a little workaround but it works.

Because this query doesn't work in linq2db:

Dim q10 = From s In db.Sectors
          From t In db.Targets.Where(Function(f) f.id_sector = s.Id).DefaultIfEmpty
          Where t Is Nothing
          Select s

Instead we can write like this:

Dim q10 = From s In db.Sectors 
          Where Not (From t In db.Targets Select t.id_sector).Contains(s.Id)
          Select s

However generated SQL instead looking like this:

SELECT
    [t2].[Id],
    [t2].[Name]
FROM
    [tblSector] [t2]
        LEFT JOIN [tblTarget] [t1] ON ([t1].[id_sector] = [t2].[Id])
WHERE
    [t1].[id_sector] IS NULL

It looks like this:

SELECT
    [t2].[Id],
    [t2].[Name]
FROM
    [tblSector] [t2]
WHERE
    NOT (EXISTS(
        SELECT
            *
        FROM
            [tblTarget] [t1]
        WHERE
            [t1].[id_sector] = [t2].[Id]
    ))

I don't use VB.Net, but in c# I can achieve a similar result by simply checking if `t.id_sector == (int?)null`. In other words, casting null as a nullable integer (casting the field also works). The IDE will complain this this is a redundant cast, but linq2db will correctly generate a SQL expression that checks for a null value based on this cast. — Daniel, Sep 24 '22 at 20:43

Linq left outer join with exclusion via Linq2db

1 Answers1