Build a LINQ to SQL Query

[NvrBst]

I have an List/Array of strings and I want to query the database with
only items that start with a value in the List/Array. Example:

List<string> test1 = new List<string>() { "T", "B", "F" };
....
var myQuery = from i in myDB.myTables
where i.VALUE.StartsWith(test1[0]) || i.VALUE.StartsWith(test1[1])
|| i.VALUE.StartsWith(test1[2])
select i;

Problem: The List/Array (test1) is variable size and only available
at runtime.


What I attempted:
1. Extention Methods: where "SpecialStartsWith()" loops through test1
and returns true only if it is in the list.

var myQuery = from i in myDB.myTables
where i.VALUE.SpecialStartsWith()
select i;

Problem: I get a "SpecialStartsWith can't be translated to a SQL
Query" exception.


2. Trying to build the IQueryable with multiple Assignments:
IQueryable<myTable> total;
IQueryable<myTable> current;
foreach(string A in test1) {
current = from i in myDB.myTables
where i.VALUE.StartsWith(A)
select i;
total.Union(current);
}

Problem: current gets reset each time it is called; doesn't append.



Is there a way for me to Build a single query from all the strings in
an array (that start with)? Or am I going to have to perform a bunch
of query's and just dump each query into a List?

Thanks. The last dumping to a table works fine for me, this just
seemed like something I should be able to do but I haven't figured it
out *more so for future reference*.

 

[NvrBst]

...

Ahh I was searching more and I found Dynamic Queries. It basically
said I can use the AND operator but never the OR operator. It to me
thinking... I could probably do something like this:

var myQuery = myDB.myTables
foreach(string A in test1)
myQuery = myQuery.where(x => !x.VALUE.StartsWith(A));
myQuery = myDB.myTables.Except(myQuery);

I can't test it now (kind of away from a computer the long weekend),
but is this a really bad way to do it? Better way to do it? Wont
even work?

Thanks in advance, I'm pretty new to SQL in general

 

[Pavel Minaev]

I have an List/Array of strings and I want to query the database with
only items that start with a value in the List/Array.  Example:

List<string> test1 = new List<string>() { "T", "B", "F" };
...
var myQuery = from i in myDB.myTables
   where i.VALUE.StartsWith(test1[0]) || i.VALUE.StartsWith(test1[1])
|| i.VALUE.StartsWith(test1[2])
   select i;

Problem:  The List/Array (test1) is variable size and only available
at runtime.

What I attempted:
1. Extention Methods: where "SpecialStartsWith()" loops through test1
and returns true only if it is in the list.

You don't need such an extension method, because it already exists,
and chances are that LINQ to SQL can actually handle it. It's
Enumerable.Any:

var myQuery =
from i in myDB.myTables
   where test1.Any(s => i.VALUE.StartsWith(s))
   select i;

Try that, but I do not think that it is going to work.

2. Trying to build the IQueryable with multiple Assignments:
IQueryable<myTable> total;
IQueryable<myTable> current;
foreach(string A in test1) {
   current = from i in myDB.myTables
      where i.VALUE.StartsWith(A)
      select i;
   total.Union(current);

}

Problem: current gets reset each time it is called; doesn't append.

First of all, that's because Queryable.Union() doesn't update in-
place; it returns a new Queryable that is the result of the union. So,
inside the loop you should do:

total = total.Union(current);

Another problem is that you capture the range _variable_ A (and not
its current _value_ on a given iteration) in the query. This means
that all your queries will, once you exit the loop, use the same value
for A, which will be the last value from test1. To avoid this, you
need to copy the value of A to a local variable, and use that in the
query:

foreach(string A in test1) {
var currentA = A;
   var current =
from i in myDB.myTables
      where i.VALUE.StartsWith(currentA)
      select i;
   total = total.Union(current);
}

Finally, there is a much simpler way to do the same thing, without
Union at all. You can just keep applying Where clauses to the query:

var query = myDB.myTables;
foreach(string A in test1) {
var currentA = a;
   query =
from i in query
      where i.VALUE.StartsWith(currentA)
      select i;
}

 

[NvrBst]

    where test1.Any(s => i.VALUE.StartsWith(s))

Ahh, I was thinking there was one that did that, just couldn't see it
while I was looking at all the extension methods. I'll try it first
thing on Tuesday (Maybe today if I have time... I shouldn't of posted
right before I was about to leave for the lost weekend).

Another problem is that you capture the range _variable_ A (and not
its current _value_ on a given iteration)

I knew there was something strange going on there with the result I
was getting. I don't fully understand this but I can try to read up
on range variables a bit. Is it because each time "var currentA = A"
is called in the loop it is given a different address, and the
".StartsWith(currentA)" remembers that address? Compared to
".StartsWith(A)" where "A" is kept in the same spot in memory and just
written over?

The "total.Union(current);" was silly on my part, I knew I was suppose
to assign *I should of looked at it closer* The last queue I made
also had another mistake I think... Constantly calling the where
clause makes it "AND" together while I want it to "OR" together, so I
think only the ".Any(...)" will give me a hope of doing what I want.
That or maybe the "A || B || C" == !(!A ^ !B ^ !C)" thing I was
attempting in my 2nd post.

Thanks for the help

 

[Pavel Minaev]

The "total.Union(current);" was silly on my part, I knew I was suppose
to assign *I should of looked at it closer*  The last queue I made
also had another mistake I think...  Constantly calling the where
clause makes it "AND" together while I want it to "OR" together, so I
think only the ".Any(...)" will give me a hope of doing what I want.
That or maybe the "A || B || C" == !(!A ^ !B ^ !C)" thing I was
attempting in my 2nd post.

Actually, you're right, my last suggestion with chaining Where() does
AND, not OR. For OR, you have to use Union().

A problem there is that I'm not sure how well Union() will be
optimized. Maybe LINQ to SQL is smart enough to generate a single SQL
SELECT with multiple OR'd conditions in WHERE. Maybe it will actually
generate UNION ALL, but then SQL Server will optimize the query
correctly. And maybe it will be less efficient (but then again, maybe
the data set is small enough that you don't care).

Anyway, another option that you always have is the most general one -
use classes Expression and Expression<T> to manually build the
expression tree as needed, which you can then feed to Enumerable.Where
(). E.g.:

string[] testValues = { "T", "B", "F" };

var rowParam = Expression.Parameter(typeof(myTable), "row");

// Generate a sequence of expression trees, one for each value from
testValues,
// corresponding to "row.VALUE.StartsWith(value)"
var tests =
from value in testValues
select
Expression.Call(
Expression.Property(rowParam, "VALUE"),
"StartsWith",
new[] { typeof(string) },
new[] { Expression.Constant(value) });

// Combine expression trees generated on previous stage into a
single one with operator || (short-circuiting "or" aka OrElse)
var body = tests.Aggregate((t1, t2) => Expression.OrElse(t1, t2));

// Build the complete lambda expression from body and parameters it
uses
var condition = Expression.Lambda(body, rowParam)

// Use the lambda
var query = myDB.myTables.Where(condition);

 

[NvrBst]

  // Use the lambda
  var query = myDB.myTables.Where(condition);

Ahh building yes, building the condition was the other way I wanted to
think about it but didn't know how exactly Your example will help
me a lot.

Thank you both for the info and help