create EF query step by step

Question

Hello !

is possible to create an EF query  step by step , and after to execute-it?

I have some situations where i have  a default EF query like " From x to context.mytable  select x"

But before execute this query , i should  test some conditions , and dependong of them the query may ( or may not) change like

"From x to context.mytable where condition1

  select x

... and after

From x to context .mytable where condition1 and condition2

select x

 

........

And at the end , after i have check all the conditions , i want to execute the query and get results.

 

It's this possible ?

 

thank you.

 

Answer 1

Hi dcode25;

Yes it is possible although I would use method syntax because it may be a little less verbose.

Dim query1 = context.mytable.Select(Function(x1) x1)

If someCondition Then
    query1 = query1.Where(Function(x2) x2.SomeField SomeOperator SomeValue)
End If

If someOtherCondition Then
    query1 = query1.Where(Function(x3) x2.SomeField SomeOperator SomeValue And x2.SomeOtherField SomeOtherOperator SomeOtherValue)
End If

Dim result = query1.ToList()

 

 

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 2

ok , but i think to create the query and after to get from database. The initial query may get a lot of records from database , but the last may have only some records.

and i have some situations where after some conditions my query should return to a previous state .

for example :

step1 : from x in context.mytable select x

step2 : from x in context.mytable where condition 1 select x

step3 : from x in context.mytable where condition1 , condition2 select x

step4: from x in context.mytable where condition 1 select x

step 5 : from x in context.mytable where condition1 , condition 3 select x

...................

 

 

Answer 3

Hi dcode25;

Linq queries work in what is called deferred execution. This means that the query is NOT executed until it is enumerated over. For example in this query :

Dim query1 = context.mytable.Select(Function(x1) x1)

the query itself is assign to the variable query1 when executed at run time but the query is not executed. The same is true with the next two queries in my last post. When the runtime gets to this section of code :

Dim result = query1.ToList()

Then the query that was composed into the variable query1 gets executed because the ToList() method causes the query to be enumerated over to create the result list.

 

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 4

is this true ? because in my application (in other section) i have this code :

Dim query= From t in context.tabl34 where t.id>5
           select t

listbox1.datasource=query

... And the listbox display all the records , but i don't have any Tolist() method and the query is executed.

Answer 5

Hi dcode25;

To Your question: 

is this true ? because in my application (in other section) i have this code :

Dim query= From t in context.tabl34 where t.id>5
select t

listbox1.datasource=query

... And the listbox display all the records , but i don't have any Tolist() method and the query is executed.

Yes it is true. If you look at your code when you assign the query variable query to the DataSource of the ListBox control, the list box control will iterate / enumerate the data source and fill its list. This process of iterate / enumerate causes the query to execute.

From Microsoft Documentation:
Deferred and Immediate Query Execution
 
Query execution is separate from creating a query. After a query is created, its execution is triggered by a separate mechanism. A query can be executed as soon as it is defined (immediate execution), or the definition can be stored and the query can be executed later (deferred execution).
 
By default, when you create a query, the query itself does not execute immediately. Instead, the query definition is stored in the variable that is used to reference the query result. When the query result variable is accessed later in code, such as in a For…Next loop, the query is executed. This process is referred to as deferred execution.
 
Queries can also be executed when they are defined, which is referred to as immediate execution. You can trigger immediate execution by applying a method that requires access to individual elements of the query result. This can be the result of including an aggregate function, such as Count, Sum, Average, Min, or Max. For more information about aggregate functions, see Aggregate Clause (Visual Basic).

Using the ToList or ToArray methods will also force immediate execution. This can be useful when you want to execute the query immediately and cache the results. For more information about these methods, see Converting Data Types.

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 6

thank you very much ! i have understand now.

i'm using now in my code your version of queries.

But i have a last problem.

i have some situation where i need to declare the variable query1 before using.

So the line :

Dim query1 = context.mytable.Select(Function(x1) x1)

can be converted as :

Dim query1 as ???????

query1 = context.mytable.Select(Function(x1) x1)

 What type can i declare query1 ?  i try just  " dim query"  but this doesn't work.

Thank you. 

Answer 7

Hi dcode25;

To your question, "i have some situation where i need to declare the variable query1 before using.", then you meed to declare it of type IQueryable(Of Type that will be returned from query), for example using the statement from your question:

Dim query1 = context.mytable.Select(Function(x1) x1)

It would be as follows:

Dim query1 As IQueryable(Of mytable)
query1 = context.mytable.Select(Function(x1) x1)

As you found out this will not work, dim query, because that declares a variable of type Anonymous and when you declare a variable of this type you MUST initialize it on the same line of code because the compiler must assign a type to the variable.

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 8

Dim query1 As IQueryable(Of mytable)     -  doesn't work

after create my queries , i have this instruction :

listbox1.datasource=query1   ( i have try also listbox1.datasource=query1.tolist)

and this produce an error:

NotSupportedException was unhandled

if i use  : Dim query1 As IEnumerable(Of mytable)    -   everything works fine.

Can i use Ienumerable as type for this query ?

thank you.

 

Answer 9

Hi dcode25;

Don't know what you mean this is not working, "Dim query1 As IQueryable(Of mytable) - doesn't work", it is just a declaration of a veriable. What error message are you getting?

To your question, "Dim query1 As IEnumerable(Of mytable) - everything works fine. Can i use Ienumerable as type for this query ?", yes can use this.

 

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 10

the error is not in declaration but after , in the line :

listbox1.datasource=query1   ( i have try also listbox1.datasource=query1.tolist)

and this produce an error:

NotSupportedException was unhandled

if i declare : Dim query1 As IEnumerable(Of mytable) , no error and everything is OK.

there is a difference between   IQueryable and IEnumerable ??

Answer 11

 

Either way this works Dim query1 As IEnumerable(Of mytable) which is fine.

 

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".