Is possible for your tool to cover a such scenario ?

[Tiku123]

Hello !
I want to know how is possible to do with your tool , the below scenario :
Article - has 2 child entities F1 and F2.

I want to construct the query step by step depending from some condition , and at the end I want to get data from database using Tolist.

Dim mylist as iQuerable(Of Article)

Mylist=Context.Set(Of Article)

If condition1 then ...."From Mylist filter the articles with ".vl1=1 ..." End if

If Condition2 then ..."From Mylist filter the articles with ".id>=5 ..." End if

If Condition3 then ....For all the articles in MyList , include the items on child F1 that have ".quantity>3".... End if

If condition4 then ....For all the articles in MyList , from the items on child F1 that are included from the previous condition3 , include only those that have ".value5=0.... end if

If condition5 then ....For all the Articles in MyList , include the items from child F2 that have .type=3....." End if

MyList.Tolist

How is possible to create this ? A solution would be very helpful.

Thank you !

[jcachat]

Hello. My project allows creating global filters on entities using simple boolean expressions.

What you are describing would not apply to my project but can be accomplished with Entity Framework out of the box. I think something like this (in C#, my language of preference) is what you are trying to do?

                var mylist = context.Set<BlogEntry>() as IQueryable<BlogEntry>;
                if (condition1)
                    mylist = mylist.Where(b => b.IntValue == 1);
                if (condition2)
                    mylist = mylist.Where(b => b.IntValue == 2);
                if (condition3)
                    mylist = mylist.Where(b => b.ChildCollection.Any(c => c.type == 3));

                var result = mylist.ToList();

Basically, you can just keep appending more .Where() clauses to your IQueryable. EF will not execute it until you do the ToList() at the end and the resulting sql query will contain all the conditions you have added to your query.

Hope that helps.

[Tiku123]

Hello !

The problem is on condition3 .

The way you construct this query would mean : " Select all the Articles that have on "childCollection" at least one child with c.type=3.

If you can read carefully my first post , this is not what I want.

I want : " For all articles in Mylist , Include only those childs from childsCollection that have c.type=3.

This is not supported from Entity Framework out of the box , because if I will translate my request , will be an Include() with filter that EF doesn't support out of the box.

For what I ask you if your product can do anything.

So , is there any solution ?

Thank you !

[jcachat]

Sorry, I misunderstood your question and didn't realize you were asking about filtering the child collection.

You can do that with this project. There are examples of doing this in the example project in the source. Look for how a filter is created on BlogEntries. That is a child collection on Accounts. Filters apply to all queries against an entity no matter how they are done - directly against the DbSet or via Include().

If you need to conditionally change the filter, you can do that by either disabling it or changing the parameter values. There are examples of both in the example project and on the main page.

Sorry, I can't give more specifics atm. I'm literally walking out the door for vacation.

[Tiku123]

Hello !
I have tried , but didn't find something similar on the examples.
I want to repeat that this query is constructed step by step , and if a condition is true another part of filter is applied.
So for Include childs for example the condition 3 , 4 and 5 have dependencies to each other.
Please when you have time , write some code on how can I do this ?
Thank you , in advance.

[Tiku123]

Please give me some help !
Thank you !

[jcachat]

Here is an example how you can accomplish this. I've tried to match it as closely to your VB/pseudo code as possible. I'm not a VB guy so you'll have to convert this from C#.

Models:

    public class Article
    {
        [Key]
        public int id { get; set; }
        public int vl1 { get; set; }
        public ICollection<F1Child> F1Children { get; set; }
        public ICollection<F2Child> F2Children { get; set; }
    }

    public class F1Child
    {
        [Key]
        public int id { get; set; }
        public int quantity { get; set; }
        public int value5 { get; set; }
    }

    public class F2Child
    {
        [Key]
        public int id { get; set; }
        public int type { get; set; }
    }

Filters:

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Filter("Condition3Filter", (F1Child c, int quantity) => c.quantity > quantity, () => 0);
            modelBuilder.Filter("Condition4Filter", (F1Child c, int value5) => c.value5 == value5, () => 0);
            modelBuilder.Filter("Condition5Filter", (F2Child c, int type) => c.type == type, () => 0);
        }

Run your query with condition checks and get results:

            using (var context = new TestContext())
            {
                var mylist = context.Set<Article>()
                    .Include(a => a.F1Children)
                    .Include(a => a.F2Children) as IQueryable<Article>;

                if (condition1)
                    mylist = mylist.Where(a => a.vl1 == 1);

                if (condition2)
                    mylist = mylist.Where(a => a.id >= 5);

                if (condition3)
                    context.SetFilterScopedParameterValue("Condition3Filter", "quantity", 3);
                else
                    context.DisableFilter("Condition3Filter");

                if (condition4)
                    context.SetFilterScopedParameterValue("Condition4Filter", "value5", 0);
                else
                    context.DisableFilter("Condition4Filter");

                if (condition5)
                    context.SetFilterScopedParameterValue("Condition5Filter", "type", 3);
                else
                    context.DisableFilter("Condition5Filter");

                var results = mylist.ToList();
            }

I have defined 3 global filters on the F!Child and F2Child entities. These will be enabled by default so you would need to disable them if they should not be used. I am also setting the values using SetFilterScopedParameterValue because I'm assuming those values may vary. 2 of the filters are on F1Child and can be used together or independently.

As I said, the filters are enabled by default. There is currently no way to disable them by default, but you could define the filters something like this:

            modelBuilder.Filter("Condition3Filter", (F1Child c, int quantity) => (quantity >= 0) && (c.quantity > quantity), () => -1);

Here, the default value I am setting for "quantity" is -1 so it will effectively return all records (and thus disable the filter) unless "quantity" is set via a call to SetFilterScopedParameterValue() or SetFilterGlobalParameterValue().

Remember that these filters will apply globally to all queries that involve F1Child and F2Child entities. It may be worthwhile for me to add an option to the filter creation that allows the filters to be disabled by default so that you can selectively opt-in to filters that should not be used globally. But until then, you can accomplish that as I have shown above.