How to make a dynamic LINQ query?

advertisements

There are two tables User and Role. I have to fetch the data with respect to the request in Entity frame work. This is my request class

public class UserRequestDTO
{
    public Int64? RoleId {get;set;}
    public Int64? DepartmentId {get;set;}
}

This is my Data Access Layer

public IList<User> GetUser(UserRequestDTO _oUserRequestDTO)
{
    ///This Implements the DbContext
    DataContext _odb=new DataContext();

    IQueryable<User> query=_odb.user.where(a=>a.IsDisable.equals(false));

    if(_oUserRequestDTO.RoleId.HasValue)
    {
        query=  from qu in query
                from role in _odb.Role.where(a=>a.Id.equals(qu.RoleId))
                Where role.Id.equals(RoleId)&& role.IsDisable.equals(false))
                Select qu;
    }

    if(_oUserRequestDTO.DepartmentId.HasValue)
    {
        query=  from qu in query
                from role in _odb.Role.where(a=>a.Id.equals(qu.RoleId))
                Where role.Department.Id.equals(DepartmentId)&& role.IsDisable.equals(false))
                Select qu;
    }
    IList<User> UserLst=query.ToList();
    return UserLst;
}

I check what query is firing in the SQL Server 2014 Profiler. If I am filtering with RoleId or DepartmentId in the request no problem in the query. But if I filter with both RoleId and DepartmentId. In the Profiler I saw two inner join where coming.

How can I make it into a single inner join?

Is there any way to learn about how query will be generated through Linq?


Select common part of query in one variable:

public IList<User> GetUser(UserRequestDTO _oUserRequestDTO)
{
    DataContext _odb = new DataContext();

    var users =_odb.user.where(a=>a.IsDisable.equals(false));

    var query = from qu in users
                from role in _odb.Role.where(a=>a.Id.equals(qu.RoleId))
                select new { User = qu, Role = role };

    if(_oUserRequestDTO.RoleId.HasValue)
    {
        query = from o in query
                where o.Role.Id.equals(RoleId) && o.Role.IsDisable.equals(false))
                select o;
    }

    if(_oUserRequestDTO.DepartmentId.HasValue)
    {
        query = from o in query
                where o.Role.Department.Id.equals(DepartmentId) && o.Role.IsDisable.equals(false))
                select o;
    }
    return query.Select(o => o.User).ToList();
}