Is there a "not equal" in a linq join?-CodePudding

I am trying accomplish the LINQ query below but I need a "not equal" instead of equal, I want to add a list of clubs that members have not joined, instead of IDClub equals i.ID, I want IDClub "not equals" i.ID

int IdTvien = Convert.ToInt32(Session["UserId"]);
List<Member> memBer= db.memBer.ToList();
          List<Club> Club = db.Club.ToList();
          List<Member_Club> member_club= db.Member_Club.ToList();
          var memBRecord= from e in member_club
                               join d in memBer on e.IDmemBer equals IdTvien into table1
                               from d in table1.ToList()
                               join i in clb on e.IDClub equals i.ID into table2
                               from i in table2.ToList()
                               select new ViewModel
                               {
                                   Member_Club = e,
                                   Member= d,
                                   Club= i
                               };

CodePudding user response：

UserClubs_ clubs = (from a in this.db.Users
                                  join b in this.db.UserClub on a.UserNumber equals b.UserNumber into u_club
                                    where a.UserNumber == 852
                                  select new UserClubs_
                                  {
                                      assigned_clubs = (from r in this.db.ClubList
                                                        where u_club.Select(x => x.ClubId).Contains(r.Id)
                                                        select r).ToList(),
                                      user = a,
                                      not_assigned = (from r in this.db.ClubList
                                                      where !u_club.Select(x => x.ClubId).Contains(r.Id)
                                                      select r).ToList()
                                  }).FirstOrDefault();

Not so good but you can use something like this

some class:

public class UserClubs_
{
    public Users user { get; set; }
    public List<ClubList> assigned_clubs { get; set; }
    public List<ClubList> not_assigned { get; set; }
}

tables:

CREATE TABLE [dbo].[UserClub] (
[Id]         INT IDENTITY (1, 1) NOT NULL,
[UserNumber] INT NOT NULL,
[ClubId]     INT NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)

);

CREATE TABLE [dbo].[ClubList] (
[Id]   INT           IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (50) NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)

);

CodePudding user response：

What does it mean with not equal and join, will it really join? You can do ;

Join e.IDCLB equals i.ID into table2
Where e.IDCLB == null