CODEWITHSUNDEEP
c#linqentity-framework.net-4.5
user3731783
user3731783

Reputation: 718

Dynamic LINQ statement

I am trying to query database using EF and LINQ. I have multiple queries to filter data based on user permissions with in the application.

from p in db.EmployeeDetails
join i in db.EmployeeDept on p.DeptId equals i.DeptId into inst
from i in inst.DefaultIfEmpty()
join s in db.Employee on p.EmpId equals s.EmpId into manager
from s in manager.DefaultIfEmpty()
join e in db.EmpStatus on p.EnrollmentStatusID equals e.StatusID into estatus
from e in estatus.DefaultIfEmpty()
where p.SomeId== id && (p.IsActive == true || p.SomeStatus == null)
select new EmployeeBase
{
  //select list common to all queries. 
};

This is the example of a base query. To this I need to add one more where clauses if user belongs to a specific role and a new table if user belongs to some other role. I was able to do this with five different queries with if...else condition checks. But a new requirement brings more conditions and more tables and more where clauses to be added which makes the code so repetitive that turns out to be a nightmare for maintenance .

I tried PredicateBuilder but I am not sure how can I apply PredicateBuilder with multiple entities and outer join's.

I found about Dynamic LINQ Library from ScottGu's block but I am not sure if it has a .Net 4.5 version and is helpful in this case.

Is there a way that I can construct the query dynamically so that I don't need to change every query when there is a small change in the requirement.

Upvotes: 0

Views: 147

Answers (1)

Robert McKee
Robert McKee

Reputation: 21492

public IQueryable<EmployeeBase> Employees() {
    return (

    from p in db.EmployeeDetails
    join i in db.EmployeeDept on p.DeptId equals i.DeptId into inst
    from i in inst.DefaultIfEmpty()
    join s in db.Employee on p.EmpId equals s.EmpId into manager
    from s in manager.DefaultIfEmpty()
    join e in db.EmpStatus on p.EnrollmentStatusID equals e.StatusID into estatus
    from e in estatus.DefaultIfEmpty()
    where p.SomeId== id && (p.IsActive == true || p.SomeStatus == null)
    select new EmployeeBase
    {
      //select list common to all queries. 
    });
}

...

var awesomeEmployee=Employees().Single(x=>x.name=="Me");
var listToFire=Employees().OrderByDescending(x=>x.Salary).Take(3);
var listToPromote=Employees().OrderByDescending(x=>x.Performance).Take(1);

Although, I'd recommend reusing the EF objects, and making sure you have navigation properties set up correctly. From your code, it looks like you aren't using them at all -- which leads you to the mess you have. You really should just have something like these extension methods:

public static IQueryable<EmployeeDetails> IsActive(this IQueryable<EmployeeDetails> eb) {
    return eb.Where(p=>p.IsActive == true || p.SomeStatus == null);
}

Then you can access your data like:

var employee=db.EmployeeDetails
  .IsActive()
  .Include(x=>x.Manager);

foreach(var e in employee) { 
  Console.Writeline("{0}'s manager is {1}",
    e.name,
    e.Manager.name);
}

Upvotes: 2

Related Questions