rewrite Linq to SQL

Question

I have a linq query in VB.NET:

Dim i = ( _
    From o In model.DUIMAINs _
        .Include("VIOLATOR") _
        .Include("ARRESTINGOFFICER") _
        .Include("DR15") _
    Where _
        o.BREATHRESULT Is Nothing _
    Order By _
        o.ARRESTDATETIME _
)

and I am checking the tables by the writing the corresponding SQL query

SELECT
    *

FROM
    "DUI" ."DUI_MAIN"           main,
    "DATA"."TBL_TRAFFIC_PERSON" violator,
    "HRIS"."TBL_PERSONNEL"      ARRESTINGOFFICER,
    "DUI" ."DR15"               dr15,
    "DUI" ."BREATHRESULTS"      BREATHRESULTS

WHERE
    main.Duiguid = 'deaee240-cdc3-4b50-b215-51307e7d96a5'
    and
    main.Duiguid = violator.seq_guid
    and
    main.arrestingofficerusername = ARRESTINGOFFICER.USERNAME
    and
    main.Duiguid = dr15.Duiguid

and the results are coming back correct (at least the tables are being populated correctly) however for the where clause I have question

QUESTION? How do i write the

Where o.BREATHRESULT Is Nothing

in my SQL Code where BREATHRESULT is a table. Common Key is Duiguid.

The SQL query is wrong. LINQ would never use the 1980's style join, it would use LEFT JOIN. `Where o.BREATHRESULT Is Nothing` means the right-hand table in a LEFT JOIN had no matching rows on the JOIN clause. The equivalent in SQL would be `WHERE BreathResult.ID is NULL` or whatever the PK is — Panagiotis Kanavos, Sep 22 '22 at 19:10
I'm not all that familiar with Visual Basic, but it seems like `Nothing` is the equivalent of `default` in C#. This is likely just checking if the column is null or not, in which case this would just be `WHERE o.BREATHRESULT = null` — Jesse, Sep 22 '22 at 19:10
The LINQ query is converted to SQL by your ORM, not LINQ itself. You can get the actual SQL query from the ORM. `Include` is an Entity Framework function but it's unclear whether you use EF or EF Core. In EF Core 5+ you can use `query.ToQueryString()` to get the SQL query — Panagiotis Kanavos, Sep 22 '22 at 19:12
@Jesse `Nothing` is the equivalent of `null`. That's not `default`, which is a concrete value for structs (value types). For eg an `int` the `default` is 0. — Panagiotis Kanavos, Sep 22 '22 at 19:13
Does this answer your question? [How do I view the SQL generated by the Entity Framework?](https://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework) — Panagiotis Kanavos, Sep 22 '22 at 19:15
@PanagiotisKanavos Yes I know, but `default` is also `null` for reference types. I found [this article](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/nothing) which implies `Nothing` does the same thing which is why I connected the two. — Jesse, Sep 22 '22 at 19:20

rewrite Linq to SQL

0 Answers0