LINQ OUTER JOIN Query - Null in Where clause not working

Question

In my following sample data tables the customer C1 has ordered Vegetables and C2 has ordered Fruits. I want to display name of potential customers C3 and C4 who have not orders yet. So, I use an Outer Join but query always returns the first cutomer C1. It seems, something wrong with my where clause.

Customers table:

CustomerID  CustName
1           C1
2           C2
3           C3
4           C4

Orders table:

OrderID CustomerID  OrderType
1       1           V
2       2           F
3       1           V

LINQ Query to display potential customers with no orders yet:

    public class TestDbController : Controller
    {

       public async Task<IActionResult> TestAction(List<CustomersViewModel> list)
       {
          var Qry = from c in Customers
                     join ord in Orders on c.CustomerId equals ord.CustomerId into c_o
                     from t in c_o.DefaultIfEmpty()
                     where t == null
                     select new  CustomersViewModel() {CustName = c.Name};
          return View(qry.ToList());
        }
    }

UPDATE:

According to @IvanStoev, this seems to be an EF core bug. I should have pointed out that I'm using the following technologies: ASP.NET CORE, EF Core, Visual Studio 2015 - UPDATE 3, and SQL Server Express 2014. I'm including a tag for EF Core in the post.

If someone can find a solution or a workaround, please let me know.

UPDATE 2:

SQL Server Profiler captures the following following SQL:

exec sp_executesql N'SELECT [c].[CustNumber], @__Empty_0
FROM [Customers] AS [c]
LEFT JOIN [Orders] AS [ord] ON [c].[CustomerID] = [ord].[CustomerID]
ORDER BY [c].[CustomerID]',N'@__Empty_0 nvarchar(4000)',@__Empty_0=N''

But my view is showing only one record. Also, when I place a breakpoint at @for (int t=0; t<Model.Count; t++) in my view shown below, it shows only 1 at the Model.Count:

@model List<MyProject.Models.MyViewModels.CustomersViewModel>

    <form asp-controller="TestDb" asp-action="TestAction" method="post">
    <table class="table">
      <thead>
         <tr>
             <th><label asp-for="@Model.First().CustName"></label></th>
         </tr>
       </thead>
       <tbody>
             @for (int t=0; t<Model.Count; t++)
                {
                  <tr>
                     <td><input type="hidden" asp-for="@Model[t].CustomerId"/ </td>
                     <td><input type="text" asp-for="@Model[t].CustName"/></td>
                  </tr>
                }
       </tbody>
      </table>
      <button type="submit">Save</button>
  </form>

Should be `where t == null` to find rows in c without a t (ie. empty right-side of left join). This differs from normal SQL where a specific column/value would be checked for null. See http://stackoverflow.com/a/3413732/2864740 as a simple reference. — user2864740, Sep 08 '16 at 17:03
@user2864740 Yes, that's what I meant. I corrected the typo you mentioned. But still the same issue. — nam, Sep 08 '16 at 17:06
My next step in debugging would be to inspect the resulting SQL. (This is super easy / non-intrusive with SQL Server and an attached SQL Profiler session.) — user2864740, Sep 08 '16 at 17:12
Something else must be going on here. The code works fine with POCOs in [dotnetFiddle](https://dotnetfiddle.net/GrlldZ) — stephen.vakil, Sep 08 '16 at 17:15
@user2864740 probably could just hover over the `Qry` variable during execution and see the SQL as well. — stephen.vakil, Sep 08 '16 at 17:19
Modify your resultant select to get all columns, and then try the query *without* your where clause and see what it returns. Compare that to the expected result and the where clause issue, if that's the problem, may become self-evident. — David W, Sep 08 '16 at 17:24
There isn't c.Name but c.CustName. Other than that it simply works. — Cetin Basoz, Sep 08 '16 at 17:35

score 1 · Answer 1 · answered Sep 08 '16 at 17:13

1

This is actually your query and works (against say Northwind):

var Qry = from c in Customers
              join ord in Orders on c.CustomerID equals ord.CustomerID into c_o
              from t in c_o.DefaultIfEmpty()
              where t == null
              select c;

But simplier way to say that is:

var Qry = from c in Customers
          where !c.Orders.Any()
          select c;

answered Sep 08 '16 at 17:13

Cetin Basoz

22,495
3
31
39

The OP reports the query is not working as desired - the only apparent change looks to be the select clause :} – user2864740 Sep 08 '16 at 17:13
And I say it works as desired, what is "not desired"? – Cetin Basoz Sep 08 '16 at 17:14
1

The OP reports that it is not returning the missing rows, but rather a single row of a successful join ("..but query always returns the first cutomer C1"). While I am skeptical of these results, and I suspect something else is amiss, it is the reported behavior. – user2864740 Sep 08 '16 at 17:15
"I want to display name of potential customers C3 and C4 who have not orders yet" and it simply does this. Please explain what is wrong with a sample. Did you really try it? – Cetin Basoz Sep 08 '16 at 17:16
1

I'm simply reiterating the problem posed and statements made in the question. – user2864740 Sep 08 '16 at 17:16
Yes it is not your question and you are simply assuming it doesn't answer the OP's question without even understanding or trying. – Cetin Basoz Sep 08 '16 at 17:17
1

I am saying is "the only apparent change [in this answer] looks to be the select clause" :} – user2864740 Sep 08 '16 at 17:18
@stephen.vakil In this case I'd consider accepting this answer and/or closing the question until a more refined problem with updated assertions can be established. Could it be a different query that is run (ie. old build or different code-path)? – user2864740 Sep 08 '16 at 17:19
What doesn't it solve? Try it against say Northwind sample. It simply returns those without orders yet, and that is what OP wants. – Cetin Basoz Sep 08 '16 at 17:19
@CetinBasoz The OP claims he *is* using that query modified by his where clause, and it is *not* returning the desired results - thus he's asking if his where clause is to blame. – David W Sep 08 '16 at 17:23
and I am saying it is working. Why wouldn't you think his is a misperception that it doesn't work. – Cetin Basoz Sep 08 '16 at 17:24
@CetinBasoz You're just not even trying to listen, and that's your prerogative. If you don't understand the situation after two different people tried to explain it to you, I'm beginning to suspect you really don't *want* to understand, so I'm going to stop trying to explain. Have a great day. – David W Sep 08 '16 at 17:26
As an example, maybe the datatypes of the ID field are string and one of the entries has a space in it. There could be other reasons why the query fails, or maybe the query works fine and if the question had additional code posted to show where it doesn't seem to work, we could solve the issue. – stephen.vakil Sep 08 '16 at 17:26
@DavidW, you are simply making assumptions. You can create and try against data. – Cetin Basoz Sep 08 '16 at 17:29
@CetinBasoz I need to display only C3, and C4 - the ones with no orders. I'm using ASP.NET Core with EF Core, and VS2015 with SQL Server 2014. In your LINQ query, what is Orders in `!c.Order.Any()` since intellisense in VS does not recognize it. Please note: I do not have FK relationship in my example. – nam Sep 08 '16 at 17:56
@nam You just shared the most important information which you should have done at the first place. There is even a special tag for it. And it's called **EF Core**. That's the cause of your problem, go to their GitHub and search for left outer join bugs. And please delete the question here because it is incorrect and misleading. – Ivan Stoev Sep 08 '16 at 18:41

LINQ OUTER JOIN Query - Null in Where clause not working

1 Answers1