CODEWITHSUNDEEP
c#asp.net-mvclinqentity-framework-6repository-pattern
K.Z
K.Z

Reputation: 5075

Use multiple conditions in join LINQ. i,e AND

How to use multiple condition in LINQ joins, i.e. in my scenario I need to get all the users from table User where group ID = 4 from table UserInGroup, where UserInGroup is intermediate table between User and Group table as in SQL-T we use join as

select * 
from user 
where user.userID = userIngroup.userID AND userIngroup.groupID == 4
....

In another approach I am using lambda expression along with LINQ, how I can apply where groupID = 4 in following one??

public IEnumerable<User> GetUsersByGroupID(int _groupID)
    {

        List<User> _listedUsersByGroupID = new List<User>();

        using(var _uow = new UserManagement_UnitOfWork())
        {

            _listedUsersByGroupID = (from _users in _uow.User_Repository.GetAll()
                                    .Include(s=>s.UserInGroup.Select(r=>r.Group))
                                    select _users).ToList();

            return _listedUsersByGroupID;

        }
    }

User Model

[Table("User")]
public class User
{
    public User() { }

    [Key]
    public int UserID { get; set; }

    [StringLength(250)]
    [Required]
    public string FirstName { get; set; }

    [StringLength(250)]
    [Required]
    public string LastName { get; set; }

    [Required]
    public int Age { get; set; }

    [StringLength(250)]
    [Required]
    public string EmailAddress { get; set; }

    public ICollection<UserInGroup> UserInGroup { get; set; }
}

UserInGroup Model

 [Table("UserInGroup")]
public class UserInGroup
{
    public UserInGroup() { }

    [Key]
    public int UserGroupID { get; set; }

    [Required]
    public int UserID { get; set; }

    [Required]
    public int GroupID { get; set; }

    public User User { get; set; }
    public Group Group { get; set; }
}

Group Model

public class Group
{
    public Group() { }

    [Key]
    public int GroupID { get; set; }

    [StringLength(250)]
    [Required]
    public string GroupName { get; set; }

    public ICollection<UserInGroup> UserInGroup { get; set; }
}

Upvotes: 3

Views: 725

Answers (2)

ocuenca
ocuenca

Reputation: 39326

You only need to add a condition to filter the users that belong to the group 4. Try this:

  _listedUsersByGroupID = (from _user in _uow.User_Repository.GetAll()
                                    .Include(s=>s.UserInGroup.Select(r=>r.Group))
                           where user.UserInGroup.Any(ug=>ug.groupID==4)
                           select _user).ToList();

Upvotes: 3

Margus
Margus

Reputation: 20048

Lambda query would look something like:

ctx.User.Where(user=>
    ctx.UserInGroup.Any(userIngroup=>
        user.userID == userIngroup.userID && userIngroup.groupID == 4
    )
)

That however is just query, if you want to get results add .AsList() or .AsEnumerable() to end.

However you can write silly and inefficient code if you do not fully understand what you are doing. I would reccomend you try this instead:

var publications = ctx.Database.SqlQuery<UserResults>(String.Format(@"
    select UserID, FirstName,LastName,Age,EmailAddress,UserInGroup    
    from user 
    where user.userID = userIngroup.userID AND userIngroup.groupID == {0}
    order by UserID
", Config.Group));

Where Config.Group is 4; UserResults can be User table as well, if you do not want other fields. You need to execute or enumerate over the sql query to use the data and like before you can use .AsList() or .AsEnumerable() for that.

Variable ctx is database context. For example:

using (var ctx = new toxicEntities())
{

}

Upvotes: 1

Related Questions