外部联接未在EF Core中返回预期结果 [英] OUTER JOIN not returning expected results in EF Core

问题描述

在我的ASP.NET MVC Core应用程序中，POST操作方法Test没有返回预期的结果.该Web应用是使用此ASP.NET Core官方网站，并稍作修改.可以从此处下载，并使用最新版本的VS2015.该应用程序正在使用EF Core. 如果您下载该项目，则需要执行以下步骤来测试上述意外结果:

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:

注意:这些步骤的顺序很重要.这是一个非常小的测试项目.第二步将创建一个名为ASPCore_Blogs的小型SQL Server Db.因此，请确保SQL Server正在运行:

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. 下载项目后，请确保从项目目录中删除.vs文件夹，然后再在VS2015中打开项目(如果项目挂起，则可能必须使用Windows OS的Task Manager强制关闭它，然后重新将其打开以使其正常工作.这是VS2015中的已知问题.
2. 打开startup.cs文件，然后在Configuration()方法中将数据库实例名称从MyComputer\SQLServerInstance更改为您正在使用的任何实例.在根目录的appsettings.json文件中执行相同的操作.
3. 在VS2015 PM窗口中，运行PM> update-database -context BloggingContext [确保SQL Server正在运行]
4. 然后运行:PM> update-database -context ApplicationDbContext

5. 运行Web应用程序.通过输入登录名/密码信息进行注册.登录名必须为电子邮件([e-mail])形式.在主页左侧:

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 ([e-mail]) form. On the left side of Home page:

单击链接Blog Create创建四个博客，分别为:[e-mail]，[e-mail]，[e-mail]，[e-mail]

Click on the link Blog Create to create 4 blogs as: [e-mail], [e-mail], [e-mail], [e-mail]

但这不是正在发生的事情.当您通过从下拉列表中选择不同年份来重复此操作并单击GO按钮时，您将看到输出与预期不符.

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.

示例数据:

博客表数据:

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

发布表格数据:

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

Test操作GET方法中的左外部联接应返回:

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

然后，当您在下拉列表中选择"1998年"并单击执行"按钮时，应该返回Test(...)Post操作方法查询.. 但是它会随机返回任何行:

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

模型:

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);
    }
}

更新:

1. 添加了第2步，要求用户更改连接字符串
2. 从Test()的GET/POST操作方法中的bp.DefaultIfEmpty(new Post())中删除了新的Post().但是仍然存在相同的错误.

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.

推荐答案

在linq查询中，您可以在其中执行DefaultIfEmtpy调用:

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)

您使用了重载，其中DefaultIfEmtpy将在new Post()实例为空时返回它，而不是返回null.但是随后您的逻辑期望它返回null.用返回null的重载替换快照程序的第一行:

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()

这篇关于外部联接未在EF Core中返回预期结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！