How to utilize EF and LINQ to add filters and specify tables, columns, filters and order by dynamically

Question

I have database structure like this (all Parent and Childs are tables),

What I am doing

Now I want to create ad hoc reporting page, which will let user select tables and columns in these tables.

When user will select tables and columns, then they can add filters (is in list or contains or filter by etc..) to columns.

I will send all this information as json to a web service will create, then in web service I am planning to use EntityFramework to get required dataset.

What I already have done

I am able to create HTML UI, web services, database layer with repositories and UOW, database etc..

Question

I mean I can do this,

var result = context.ParentA.Include("Child1.SubChild1").Include(....).Where(..

But I am not sure how can I specify columns I want or add filters.

score 0 · Answer 1 · answered Feb 05 '16 at 16:05

For Specifying Column Names you can use a select with your linq query like this :

var result = context.ParentA.Include("Child1.SubChild1").Include(....).Where(..).select(s=> new {Name = s.ParentName , SubName = s.SubChild.Name });

For adding Filters you need to define them in the where clause

.Where(p=>p.Name.contains("someValue"))

score 0 · Answer 2 · edited May 23 '17 at 12:01

If you could create some class where you can define the filters then you can create an IQueryable<T> adding filters:

class Filters
{
    public bool FilterByColumnA { get; set; }

    ...

    public bool FilterByColumnN { get; set; }


    public int FromColumnA { get; set; }

    public int ToColumnA { get; set; }

    ...

    public string FromColumnN { get; set; }

    public string ToColumnN { get; set; }
}

Then you can build the query:

IQueryable<Entity> query = context.Set<Entity>();

if (filters.FilterByColumnA)
    query = query.Where(e => e.ColumnA > filters.FromColumnA && e.ColumnA < filters.ToColumnA);

...

if (filters.FilterByColumnN)
    query = query.Where(e => e.ColumnN > filters.FromColumnN && e.ColumnN < filters.ToColumnN);

You can see this question for more details.

To select the properties dynamically, you can choose which explicitly load using Include():

DbQuery<Entity> query = context.Set<Entity>();

foreach (var prop in properties)
    query = query.Include(prop);

If the properties are declared virtual, then they will be loaded into memory only when the value is needed. If you are using OData for example, you can create select queries from the url.

Another solution may be using Expressions.

But if you really need a dynamic approach you can check this nuget package.

How to utilize EF and LINQ to add filters and specify tables, columns, filters and order by dynamically

2 Answers2