How to get records from one table using a value in another table as the filter

Question

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.
https://docs.microsoft.com/en-us/aspnet/core/tutorials/razor-ages/search?view=aspnetcore-2.1

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

        }

    }
}

Posted 24-Jul-18 8:41am

Member 8668966

Updated 25-Jul-18 3:30am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Accepted Answer · 2018-07-25T03:31:00

Try:

C#

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