Solved

query ienumerable results

Posted on 2008-10-03
5
336 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

758 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

20 Experts available now in Live!

Get 1:1 Help Now