Click here to Skip to main content
15,881,248 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm trying to create an ASP.NET Razor App.  I am trying to select all records in one table (Publishers) based on a value in another table Users (aka AspNetUsers).  ASP.NET Identity is used to setup authentication.  The column FCode (facility code) is in both tables.

I use the code below to select the records in the Publishers table, it brings back all records rather than just the records where FCode matches in both tables. I have spent hours playing with the code and cannot figure what I’m doing wrong.  Your help will be greatly appreciated.  If there is a better way to accomplish that would be appreciated even more.

The tutorial I used as the basis for my code if found at this link.

What I have tried:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.AspNetCore.Mvc.Rendering;
using Microsoft.EntityFrameworkCore;
using PSA.Models;

namespace PSA.Pages.Publishers
    public class IndexModel : PageModel
        private readonly PSA.Data.ApplicationDbContext _context;

        public IndexModel(PSA.Data.ApplicationDbContext context)
            _context = context;
        public string PubNameSort { get; set; }
        public string CurrentSort { get; set; }
        public string CurrentFilter { get; set;
        public string CurrentFCode { get; set; }

        public IList<Publisher> Publisher { get; set;}

        public async Task OnGetAsync(string sortOrder, string searchFCode)
	// the following code is used to get the value of FCode in the Users 
// (AspNetUsers) table based on the UserID used to logins. This value is set 
// to a variable that is used to filters records in the Publishers table, the column name in the Publishers table // is FCode also.          

var UID = User.Identity.Name;	//get the UserIN (Name)
IQueryable<string> fcodeQuery = from f in _context.Users 
select f.FCode;

            		var facCode = from f in _context.Users
                            		         select f;
	// used to set the sort order
            	PubNameSort = string.IsNullOrEmpty(sortOrder) ? "Name_desc" : "";
	// query the Publishers table for all record where the FCode = FacCode
	IQueryable<Publisher> publisherIQ = from s in _context.Publisher
                                              Where(User.Identity.Name == facCode 
                                              select s;	
              publisherIQ = publisherIQ.OrderBy(s => s.PubName);
Publisher = await publisherIQ.AsNoTracking().ToListAsync(); 


Updated 25-Jul-18 3:30am

1 solution

public async Task OnGetAsync(string sortOrder, string searchFCode)
    string userName = User.Identity.Name;
    var userQuery = from u in _context.Users.AsNoTracking()
                    where u.UserName == userName
                    select u.FCode;

    string fCode = await userQuery.SingleAsync();
    var publisherQuery = from p in _context.Publisher.AsNoTracking()
                         where p.FCode == fCode
                         orderby p.PubName
                         select p;
    Publisher = await publisherQuery.ToListAsync();
Share this answer
Member 8668966 25-Jul-18 15:05pm    
Richard - Thank you!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900