ASP.NET MVC Core with EF Core - LEFT Outer Join in LINQ always returning top 1 record if a right side column in the WHERE clause is set to null

[saf-itpro]

ISSUE: In my ASP.NET MVC Core app with EF Core, LEFT OUTER Join with a Where rightColumnName == null clause is always returning the one record that is the top row of the result. I'm using VS2015-Update3. And this is Code First project (not db first). Moreover, there is no FK relationship implemented between two tables.

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 a bug or something wrong with my where clause.

Customers Table data:

CustomerID  CustName
1                    C1
2                    C2
3                    C3
4                    C4

Orders Table data:

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.CustName};
          return View(qry.ToList());
        }
    }

SQL Server Profiler captures the following following SQL that returns all customers (instead of C3, C4 only):

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>

EF version installed on the project:
I followed the instruction from this ASP.NET official article to install the EF Core. Following is the image from my Project Reference Directory:

[smitpatel]

Based on data above I tried to make a stand-alone repro. I am getting 2 results - "C3", "C4" as expected.

public class Program
{
    public static void Main(string[] args)
    {
        using (var db = new MyContext())
        {
            var serviceProvider = db.GetInfrastructure();
            var loggerFactory = serviceProvider.GetService<ILoggerFactory>();
            loggerFactory.AddConsole(LogLevel.Information);
        }

        using (var db = new MyContext())
        {
            db.Database.EnsureDeleted();
            db.Database.EnsureCreated();

            var cust1 = new Customer {CustomerName = "C1"};
            var cust2 = new Customer {CustomerName = "C2"};
            var cust3 = new Customer {CustomerName = "C3"};
            var cust4 = new Customer {CustomerName = "C4"};

            var order1 = new Order {Customer = cust1, OrderType = "V"};
            var order2 = new Order {Customer = cust2, OrderType = "F"};
            var order3 = new Order {Customer = cust1, OrderType = "V"};

            db.AddRange(cust1, cust2, cust3, cust4, order1, order2, order3);
            db.SaveChanges();
        }

        using (var db = new MyContext())
        {
            var list = (from c in db.Customers
                        join ord in db.Orders on c.CustomerId equals ord.CustomerId into c_o
                        from t in c_o.DefaultIfEmpty()
                        where t == null
                        select new CustomersViewModel() { CustName = c.CustomerName }).ToList();

        }
    }
}


public class MyContext : DbContext
{
    public DbSet<Order> Orders { get; set; }
    public DbSet<Customer> Customers { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=_ModelApp;Trusted_Connection=True;")
            .EnableSensitiveDataLogging();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
    }

}

public class Customer
{
    public int CustomerId { get; set; }
    public string CustomerName { get; set; }
}

public class Order
{
    public int OrderId { get; set; }
    public int CustomerId { get; set; }
    public Customer Customer { get; set; }
    public string OrderType { get; set; }
}

public class CustomersViewModel
{
    public string CustName { get; set; }
}

Following is the SQL query generated

SELECT [ord].[OrderId], [ord].[CustomerId], [ord].[OrderType], [c].[CustomerName]
FROM [Customers] AS [c]
LEFT JOIN [Orders] AS [ord] ON [c].[CustomerId] = [ord].[CustomerId]
ORDER BY [c].[CustomerId]

Since the generated SQL is somewhat different, which version of EF Core are you using? I used version 1.0.0 to generate above.

Also can you put break point inside controller action method to see if EF query is returning correct data or not? From results returned by EF to generating view there are other components which could contribute to bug. So it would be better to pin-point from where the bug is coming from.

[saf-itpro]

@smitpatel I'm also using EF Core 1.0. I've included a snapshot of Reference directory in my project (if that can help) in the last section of my post above. Also I've included a note in the post that I'm using Code First approach (not Db first). The query from the profiler you are getting is the same except that my profiler is using sp_executesql. Could it be a SQL Server version difference? I'm using SQL EXPRESS 2014. Moreover,

1. Your SQL generated query (when run in SSMS) will also display all rows with matching CustomerId and the rows with right side columns to be null. I thought the profiler would return your query with a Where clause as Where **ord**.CustomerId is NULL
2. I see you using db.Database. I'm not using EF Database Class. Could that make a difference?

[smitpatel]

@saf-itpro
I have also used code first approach.
Query profiler shows sp_executesql when there are parameters to be passed to the query. Parameter-less query would show up as T-Sql only. I am also using Sql Server 2014.

1. Generated Sql fetches all rows from server but EF filters them out on client. I am looking more into it why the translation of where clause is blocked but regardless, the query you showed also lacked where clause. So EF should be returning correct results at the end of the query.
2. db.Database is internal class in EF and not related to Database class you posted link about. I used it to delete & create database. (basically make a fresh copy of database and insert data before running query)

[saf-itpro]

@smitpatel Thank you for clarifying (1) and (2) above. Also, I've created a sample project for testing the issue. The project can be download from GitHub here.

In this ASP.NET MVC Core app the POST action method Test(...) is not returning the expected results. This POST action method should return the rows with selected year and the rows where join does not meet (right columns are null). The web app was created using this official ASP.NET Core site and was modified slightly.

If you download the project, you will need to do the following steps to test the above unexpected results:

Note: Order of these steps is important. This is a very small test project. Step2 will create a small SQL Server Db called ASPCore_Blogs. So make sure SQL Server is runing:

1. After you download the project, make sure you delete the .vs folder from the project directory before opening the project in VS2015 (if project hangs, you may have to force close it using Task Manager of windows os and re-open it to make it work. This is a known issue in VS2015).
2. Open startup.cs file and in the Configuration() method change the database instance name from MyComputer\SQLServerInstance to whatever instance you are using. Do the same in the appsettings.json file in the root directory.
3. In VS2015 PM window, run PM> update-database -context BloggingContext [Make sure SQL Server is running]
4. Then run: PM> update-database -context ApplicationDbContext
5. Run the web app. Register by entering a login/password info. login need to be in an Email (test@test.com) form. On the left side of Home page do the following:
6. Click on the link Blog Create to create 4 blogs as: Blog1@test.com, Blog2@test.com, Blog3@test.com, Blog4@test.com
7. Click on the link Blogs Index to verify all the above 4 blogs were created
8. Click on the Test link. This view is called by the GET action method Test. On the corrsponding view (Test.cshtml) you will see Url column on the page is showing all 4 above blogs. And Title and Content columns are blanks. Fill the Title column as: Title1, Title2, Title3, Title4. Fill the Content column as: Content1 ,Content2 ,Content3 ,Content4
9. Now, go to the corresponding SQL Server DB called ASPCore_BlogsNAxis and open Posts table in Edit mode to manually change the PostYear column values to: 1998,1999,1998,2001 respectively (note: 1998 is repeated on purpose)
10. Now, go to Blogs table in the same SQL Server DB and enter an extra blog Blog5@test.com
11. Now, run the web app and click on Test link (on the left side of the Home page) again. You'll see that the Get action method Test is using left outer join to display all 5 blogs but the right side columns (Title and Content) values are blanks in the 5th row, as expected, since left outer join does not satisfy the join condition on BlogId for the 5th blog. So far so good.
12. Now, on the Year dropdown on the Test.cshtml view, select year as 1998 and click on GO button. According to the first if condition of the POST action method Test the app should display only three records (two for 1998 and the 5th one that does not satisfy join condition): first, 3rd, and 5th record.

But that's not what happening. When you repeat this action by selecting various years years from the dropdown and click GObutton, you'll see the output is not as expected. Moreover, the error is not related to dropdown either since if you hard code a year (e.g. 1998) in the Test() action method you will still get the unexpected results.

Example Data:

Blogs Table Data:

BlogId  Url
1       test1.com
2       test2.com
3       test3.com
4       test4.com
5       test5.com

Posts Table Data:

PostId  BlogId  Content       PostYear  Title
1         1    Content1       1998     Title1
2         2    Content2       1999     Title2
3         3    Content3       1998     Title3
4         4    Content4       2001     Title4

LEFT Outer JOIN in Test Action GET Method Should return:

BlogId    Url       PostId    Content        PostYear   Title
    1     test1.com  1        Content1          1998    Title1
    2     test2.com  2        Content2          1999     Title2
    3     test3.com  3        Content3          1998    Title3
    4     test4.com  4        Content4          2001    Title4
    5     test5.com NULL        NULL            NULL     NULL

And when you select year 1998 in the dropdown and click on Go button, the Test(...) Post action method query should return. But it's randomly returning any rows:

BlogId    Url         PostId        Content       PostYear  Title
    1      test1.com       1       Content1         1998    Title1
    3      test3.com       3       Content3         1998    Title3
    5      test5.com       NULL    NULL              NULL    NULL

Models:

public class BloggingContext : DbContext
    {
        public BloggingContext(DbContextOptions<BloggingContext> options)
            : base(options)
        { }

        public DbSet<Blog> Blogs { get; set; }
        public DbSet<Post> Posts { get; set; }
    }

    public class Blog
    {
        public int BlogId { get; set; }
        public string Url { get; set; }

        public List<Post> Posts { get; set; }
    }

    public class Post
    {
        public int PostId { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }
        public int PostYear { get; set; }
        public int BlogId { get; set; }
        public Blog Blog { get; set; }
    }

BlogsController:

public class BlogsController : Controller
{
    private readonly BloggingContext _context;

    public BlogsController(BloggingContext context)
    {
        _context = context;    
    }

    // GET: Blogs
    public async Task<IActionResult> Index()
    {
        return View(_context.Blogs.ToList());
    }

    // GET: /Blogs/Test
    [HttpGet]
    public async Task<IActionResult> Test(string returnUrl = null)
    {
        ViewData["ReturnUrl"] = returnUrl;
        ViewBag.YearsList = Enumerable.Range(1996, 29).Select(g => new SelectListItem { Value = g.ToString(), Text = g.ToString() }).ToList();

        //return View(await _context.Blogs.Include(p => p.Posts).ToListAsync());
        var qrVM = from b in _context.Blogs
                    join p in _context.Posts on b.BlogId equals p.BlogId into bp
                    from c in bp.DefaultIfEmpty()
                    select new BlogsWithRelatedPostsViewModel { BlogID = b.BlogId, PostID = (c == null ? 0 : c.PostId), Url = b.Url, Title = (c == null ? string.Empty : c.Title), Content = (c == null ? string.Empty : c.Content) };
        return View(await qrVM.ToListAsync());
    }

    // POST: /Blogs/Test
    [HttpPost]
    [ValidateAntiForgeryToken]
    public async Task<IActionResult> Test(List<BlogsWithRelatedPostsViewModel> list, string GO, int currentlySelectedIndex, string returnUrl = null)
    {
        ViewData["ReturnUrl"] = returnUrl;
        ViewBag.YearsList = Enumerable.Range(1996, 29).Select(g => new SelectListItem { Value = g.ToString(), Text = g.ToString() }).ToList();

        if (!string.IsNullOrEmpty(GO))
        {
            var qrVM = from b in _context.Blogs
                        join p in _context.Posts on b.BlogId equals p.BlogId into bp
                        from c in bp.DefaultIfEmpty()
                        where c == null? true : c.PostYear.Equals(currentlySelectedIndex)
                        select new BlogsWithRelatedPostsViewModel { BlogID = b.BlogId, PostID = (c == null ? 0 : c.PostId), Url = b.Url, Title = (c == null ? string.Empty : c.Title), Content = (c == null ? string.Empty : c.Content) };
            return View(await qrVM.ToListAsync());
        }
        else if (ModelState.IsValid)
        {
            foreach (var item in list)
            {
                var oPost = _context.Posts.Where(r => r.PostId.Equals(item.PostID)).FirstOrDefault();
                if (oPost != null)
                {
                    oPost.Title = item.Title;
                    oPost.Content = item.Content;
                    oPost.PostYear = currentlySelectedIndex;
                    oPost.BlogId = item.BlogID; //according to new post below the blogId should exist for a newly created port - but just in case
                }
                else
                {
                    if (item.PostID == 0)
                    {
                        Post oPostNew = new Post { BlogId = item.BlogID, Title = item.Title, Content = item.Content, PostYear = currentlySelectedIndex }; //need to use currentlySelectedIndex intead of item.FiscalYear in case of adding a record
                        _context.Add(oPostNew);
                    }

                }
            }
            await _context.SaveChangesAsync();
            //return RedirectToLocal(returnUrl);
            return View(list);
        }

        // If we got this far, something failed, redisplay form
        return View();
    }

    // GET: Blogs/Details/5
    public async Task<IActionResult> Details(int? id)
    {
        if (id == null)
        {
            return NotFound();
        }

        var blog = await _context.Blogs.SingleOrDefaultAsync(m => m.BlogId == id);
        if (blog == null)
        {
            return NotFound();
        }

        return View(blog);
    }

    // GET: Blogs/Create
    [HttpGet]
    public IActionResult Create()
    {
        return View();
    }

    // POST: Blogs/Create
    // To protect from overposting attacks, please enable the specific properties you want to bind to, for 
    // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
    [HttpPost]
    [ValidateAntiForgeryToken]
    public async Task<IActionResult> Create([Bind("BlogId,Url")] Blog blog)
    {
        if (ModelState.IsValid)
        {
            _context.Blogs.Add(blog);
            await _context.SaveChangesAsync();
            return RedirectToAction("Index");
        }
        return View(blog);
    }

    // GET: Blogs/Edit/5
    public async Task<IActionResult> Edit(int? id)
    {
        if (id == null)
        {
            return NotFound();
        }

        var blog = await _context.Blogs.SingleOrDefaultAsync(m => m.BlogId == id);
        if (blog == null)
        {
            return NotFound();
        }
        return View(blog);
    }

    // POST: Blogs/Edit/5
    // To protect from overposting attacks, please enable the specific properties you want to bind to, for 
    // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
    [HttpPost]
    [ValidateAntiForgeryToken]
    public async Task<IActionResult> Edit(int id, [Bind("BlogId,Url")] Blog blog)
    {
        if (id != blog.BlogId)
        {
            return NotFound();
        }

        if (ModelState.IsValid)
        {
            try
            {
                _context.Update(blog);
                await _context.SaveChangesAsync();
            }
            catch (DbUpdateConcurrencyException)
            {
                if (!BlogExists(blog.BlogId))
                {
                    return NotFound();
                }
                else
                {
                    throw;
                }
            }
            return RedirectToAction("Index");
        }
        return View(blog);
    }

    // GET: Blogs/Delete/5
    public async Task<IActionResult> Delete(int? id)
    {
        if (id == null)
        {
            return NotFound();
        }

        var blog = await _context.Blogs.SingleOrDefaultAsync(m => m.BlogId == id);
        if (blog == null)
        {
            return NotFound();
        }

        return View(blog);
    }

    // POST: Blogs/Delete/5
    [HttpPost, ActionName("Delete")]
    [ValidateAntiForgeryToken]
    public async Task<IActionResult> DeleteConfirmed(int id)
    {
        var blog = await _context.Blogs.SingleOrDefaultAsync(m => m.BlogId == id);
        _context.Blogs.Remove(blog);
        await _context.SaveChangesAsync();
        return RedirectToAction("Index");
    }

    private bool BlogExists(int id)
    {
        return _context.Blogs.Any(e => e.BlogId == id);
    }
}

[smitpatel]

@saf-itpro - Thank you for repro code. I played around with changing years and concluded following:
When the table has x rows visible, you select a year and the result has y rows then,

1. If x > y, then table will show first y rows from existing table ignoring the result altogether.
2. If x < y, then table will show first x rows as it was present before and rows after that will come from results.
   Therefore when you select a year with no results, then the next year selection will give you correct results. Anything else is just incorrect results.

I have debugged queries to verify results generated by EF. EF queries are returning correct results only. You can store the results of ToListAsync() in a temporary variable before passing it to View() and check results generated by EF. You can also put break point in Test.cshtml file around model to analyze it.

There is some issue in the View which is causing incorrect data to be shown. From EF side everything is working as expected.
Here is the standalone repro, which also shows correct results.
https://gist.github.com/smitpatel/f6dbaf781a25c3311c55cb2f80969faa

[saf-itpro]

@smitpatel First I must acknowledge your sincere efforts to deeply look into the issue (as you must have other issues to tackle as well).

1. I agree with your observation about when correct results are displayed except that even in those cases it should display an extra row (when join does not meet) since the Where clause in the POST Action method Test(...) has two condition with one being when right column values are null as shown here: where c == null? true : c.PostYear.Equals(currentlySelectedIndex).
2. I don't see an issue with the Test.cshtml view. Could you or someone in your team point out what the issue is in the View?

[smitpatel]

@saf-itpro

1. I also got confused at this one at start. Given the code difference between what you posted above and in the repro code.
   Here is the culprit
   https://github.com/saf-itpro/ASP_Core_Blogs_GitHub/blob/master/src/ASP_Core_Blogs/Controllers/BlogsController.cs#L57
   The DefaultIfEmpty() has new Post() as argument. So whenever the columns are null it will initialize new post therefore c is never null actually. Since newly initialized post has PostYear as 0 the condition is not met hence record is not shown.
2. Based on my limited expertise on MVC, I also could not figure out what is the issue with view rendering. Perhaps you can file a bug on MVC repo and those folks can help you out.

[saf-itpro]

@smitpatel I had added new Post() after I encountered the original issue hoping that would make any difference. But, sorry, I forgot to remove that from the repo. But, actually removing that causes even another issue as follows:

1. It always includes the top row no matter what year you select even if select a year that doesn't have a record (e.g. 1996, 2000 etc.). If you select a year that has record(s), those record(s) will be displayed along with first top row.

[smitpatel]

@saf-itpro - Did you verify results returned by EF query using debugger? Above behavior seems like just different behavior of same issue happening in generating the View.
I would advice using debugger step-by-step in your app to see what parts are working correctly so that you can pin-point the part which is causing the issue.
If you want to play around with different queries only (just EF part) then I have posted repro code in a gist. Just put it in any normal console app, change connection string and you can try different queries and see results generated by EF.

[saf-itpro]

@smitpatel I did verify results returned by EF query using debugger and it indeed returns the correct data. But the viewis not returning the correct data. It seems using for loop in the viewis causing the issue since when I replaced the for loop with foreach loop it works fine. But I don't know if it is still true that sometimes the default model binder is unable to bind collection items correctly in foreach loop as described here. But since EF is returning the data correctly I'm going to close the issue and will open a new one related to using for loop in the view after doing some more investigation on my own. Maybe, it has something to do with using tag helpers with for loop, I'll investigate.