如何使用另一个表中的值作为过滤器从一个表中获取记录 [英] How to get records from one table using a value in another table as the filter

问题描述

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

        }

    }
}

推荐答案

尝试：

Try:

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

这篇关于如何使用另一个表中的值作为过滤器从一个表中获取记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！