数据过滤关系法

Question

我有两个表叫做“用户”和“订阅”。使用这些控制器，我能够检索所有用户数据，但我只想检索订阅列" status“中具有订阅状态=1的用户数据。请告诉我如何过滤我的结果

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Mvc;
using WebApplication2.Models;

namespace WebApplication2.Controllers
{
    public class EmailCampaignController : Controller
    {
        private db_likEntities db = new db_likEntities ();


        // GET: Only Subscribed Users info
        public ActionResult Index()
        {
            var users = db.Users.Include(u => u.Countries);
            return View(users.ToList());
        }

     }
}

Answer 1

试试这个编辑。希望您的users表中有一个名为status的列，否则这将无法工作。

public class EmailCampaignController : Controller
{
    private db_likEntities db = new db_likEntities ();


    // GET: Only Subscribed Users info
    public ActionResult Index()
    {
        //first get all subscriptions whose status is 1
    var trueSubscriptions = db.Subscriptions.Where(s => s.status == 1).ToList();

    //use this variable to filter users by Id
    List<User> users = new List<User>();

    var allUsers = db.Users.Include(u => u.Countries);

    for (int i = 0; i < trueSubscriptions; i++)
    {
        users.Add(allUsers.FirstOrDefault(x => x.Id == trueSubscriptions[i].UserId));
    }


    return View(users.ToList());
    }

 }

为了更简单地实现这一点，您必须像这样编辑模型。

模型

public class  User
{
   public int id{get;set;}
   public virtual Subscription subscription{get;set;}
}

public class Subscription
{
   public int id{get;set;}
   public bool status{get;set;}
   public int UserId{get;set;}
}

使用这种方法，您只需获得这样的订阅用户即可。

`var users = db.Users.Include(u => u.Countries)
                        .Where(u => u.subscription.status == true)
                        .ToList();

    return View(users);`