OUTER JOIN not returning expected results in EF Co

2019-08-02 06:59发布

问题:

In my ASP.NET MVC Core app the POST action method Test is not returning the expected results. The web app was created using this official ASP.NET Core site and was modified slightly. The real app can be downloaded from here and is using latest version of VS2015. The app is using EF Core. 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:

  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.

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     test3com      3     Content2    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);
    }
}

UPDATE:

  1. Added step 2 asking users to change the connection string
  2. Removed new Post() from the bp.DefaultIfEmpty(new Post()) in the GET/POST action methods of Test(). But the same error is still there.

回答1:

in the linq query, where you do the DefaultIfEmtpy call:

from c in bp.DefaultIfEmpty(new Post())
where c == null? true : c.PostYear.Equals(currentlySelectedIndex)

you used the overload where DefaultIfEmtpy will return the new Post() instance when it is empty, instead of returning null. But then your logic expects it to return null. replace the first line of the snipper with the overload that returns null instead:

from c in bp.DefaultIfEmpty()