Solved

query ienumerable results

Posted on 2008-10-03
5
337 Views
Last Modified: 2013-11-11
I have the linq query below which returns a bunch of results. Now I want to narrow down that query more. There are certain columns the user can choose to group by and I need to narrow it down until I have looped through all the columns that the user chose to group by.

I am not real sure how to explain it but basically with qry that gets returned below I need to be able to  do this:

qry=qry.select("A column from one of the tables" = "A specified value")

the number of times and names of the columns that it narrows by can change all the time.
Dim qry = From m In dsMaterial.tbMaterial Join s In dsMaterial.tbMatInventory On m.MaterialKey Equals s.MaterialKey

Open in new window

0
Comment
Question by:stevejung
  • 2
  • 2
5 Comments
 
LVL 11

Expert Comment

by:Salim Fayad
ID: 22643791
This is what i suggest. Let's suppose you have 3 possible columns that the user can filter by one, none, 2 or all of them. They are Col1, Col2 and Col3 in table dsMaterial.tbMaterial. And you have 3 textboxes TxtCol1, TxtCol2 and TxtCol3 for the user to do a search with whatever he wants. Your query will be as following:

Dim qry = From m In dsMaterial.tbMaterial Join s In dsMaterial.tbMatInventory On m.MaterialKey Equals s.MaterialKey

Where (TxtCol1.Text = "" Or TxtCol1.Text = m.Col1)

    AND (TxtCol2.Text = "" Or TxtCol2.Text = m.Col2)

    AND (TxtCol3.Text = "" Or TxtCol3.Text = m.Col3)

Open in new window

0
 
LVL 4

Expert Comment

by:novynov
ID: 22645688
This may be overkill, but you may consider utilizing one of the "dynamic query" methods that are out there, such as the work of Tomas Petricek (http://tomasp.net/blog/dynamic-linq-queries.aspx - see especially section "Dynamic Queries #2")  or a similar (I believe more recent) approach (based on his original code) in LinqKit (http://www.albahari.com/nutshell/linqkit.aspx) which also includes PredicateBuilder - a LINQ extension that allows for the dynamic creation of predicates. Either of these might help in the development of a much more generalized solution - such as one that would allow for richer user interaction with respect to building the dynamic query.

Both of these approaches utilize the approach of building and evaluating Expressions on the fly, depending on user specified criteria.

PredicateBuilder will be really valuable if you decide to let the user decide whether his/her selected and submitted multi-valued criteria ought to be ANDed or ORed.

IMO, this approach involves some really cool stuff...and worth the effort of wrapping your head around. If it doesn't help solve a problem today, it likely will in the future.

I hope this helps.
0
 

Author Comment

by:stevejung
ID: 22649337
The first comment won't work because I don't know the names of the columns because they are based on the way a grid is grouped. I will look closer at the second but what I really wanted to be able to do was run a query against the results of another query. Is there any way to do that? In my example above, once the qry is returned I would like to run a query against the qry results that were already returned.
0
 
LVL 4

Accepted Solution

by:
novynov earned 500 total points
ID: 22670203
Regarding your comment, "run a query against the reults of another query," it is important to note that when we create a Linq to Sql query, there are no results until the query is "hydrated" either by beginning to enumerate the results (e.g. foreach) or forcing "hydration" (e.g ToList()). Until that happens, the Linq query exists only as an expression tree - ready to be parsed and executed against the data source by the Linq to Sql provider.

So, certainly you could execute another query against the results of another. Just being iterating query #1, and issue another query against the db. You could concat and massage the results of each of these "sub" queries using standard Linq operators.

However, doing the above is almost always to be avoided, as it will result in multiple calls to the db.

So, if possible, you're better off creating a single query that retrieves all the data you need...and then massaging it to suit the need.

I'd love to be of more help, but may need more specifics.  Your example shows a "Select" statement, but the syntax seems to indicate a "Where" statement - i.e. filtering results. Whereas the column names and values for your predicates (i.e. Where statements - ) are dynamic, some kind of dynamic query approach seems in order.


0
 

Author Closing Comment

by:stevejung
ID: 31502753
Thank you I did not fully understand how the linq queries worked I did not realize that they are not executed right away.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Loop not working 29 47
Export import database 4 40
How would you add MULTITHREADING to the attached C# code? 4 49
Error on link 14 36
Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now