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:
- 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 usingTask Manager
of windows os and re-open it to make it work. This is a known issue in VS2015). - Open
startup.cs
file and in the Configuration() method change the database instance name fromMyComputer\SQLServerInstance
to whatever instance you are using. Do the same in theappsettings.json
file in the root directory. - In VS2015 PM window, run PM> update-database -context BloggingContext [Make sure SQL Server is running]
- Then run: PM> update-database -context ApplicationDbContext
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:
Click on the link
Blog Create
to create 4 blogs as: Blog1@test.com, Blog2@test.com, Blog3@test.com, Blog4@test.com- Click on the link
Blogs Index
to verify all the above 4 blogs were created - Click on the
Test
link. This view is called by theGET
action methodTest
. On the corrsponding view (Test.cshtml
) you will seeUrl
column on the page is showing all 4 above blogs. AndTitle
andContent
columns are blanks. Fill theTitle
column as: Title1, Title2, Title3, Title4. Fill theContent
column as: Content1 ,Content2 ,Content3 ,Content4 - Now, go to the corresponding SQL Server DB called
ASPCore_BlogsNAxis
and openPosts
table inEdit
mode to manually change the PostYear column values to: 1998,1999,1998,2001 respectively (note: 1998 is repeated on purpose) - Now, go to
Blogs
table in the same SQL Server DB and enter an extra blogBlog5@test.com
- Now, run the web app and click on
Test
link (on the left side of the Home page) again. You'll see that theGet
action methodTest
is using left outer join to display all 5 blogs but the right side columns (Title
andContent
) values are blanks in the 5th row, as expected, since left outer join does not satisfy the join condition onBlogId
for the 5th blog. So far so good. - Now, on the
Year
dropdown on theTest.cshtml
view, select year as 1998 and click onGO
button. According to the firstif
condition of thePOST
action methodTest
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 GO
button, 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:
- Added step 2 asking users to change the connection string
- Removed new Post() from the bp.DefaultIfEmpty(new Post()) in the GET/POST action methods of
Test()
. But the same error is still there.