LINQ to SQL ad hoc filter with where clause

[Plissskin]

I need to create an "ad-hoc" filtering page in a web app we are
building. The page would display a number of drop down lists, text
boxes, and radio lists and allow the user to select (enter) some data
that would be used to filter the dataset we return. STANDARD STUFF.

Below is simplified version (not compete) of how we solved this with
ADO using javascript on the client to build a where clause that we
would use on our SQL SELECT statement.

//build SQL statement with element values - % means don't add to
select statement
MyForm.SQL.value = "Select DISTINCT XMID, Description, RecordType,
TagDesc, SZGROUPNAME, KSZTAG, VENDOR_NAME, DEVICE_NAME,KSZSERIALNUM
from " +
FromClause + " Where ("

// Partial Tag
if (MyForm.TagPrefixes.value != "%"){
MyForm.SQL.value = MyForm.SQL.value + "SubString(KSZTAG,1,3) = '" +
MyForm.TagPrefixes.value + "' And ";
lRetVal = true;
}

// Transmitter Vendor
if (MyForm.Manufacturers.value != "%"){
MyForm.SQL.value = MyForm.SQL.value + "VENDOR_CODE = " +
MyForm.Manufacturers.value + " And ";
lRetVal = true;
}

// Transmitter Model
if (MyForm.ShortModels.value != "%"){
MyForm.SQL.value = MyForm.SQL.value + "Substring(DEVICE_NAME,1,4) =
'" + MyForm.ShortModels.value + "' And ";
lRetVal = true;
}

// Group
if (MyForm.Groups.value != "%"){
MyForm.SQL.value = MyForm.SQL.value + "WGROUPCODE = " +
MyForm.Groups.value + " And ";
lRetVal = true;
}

// Span - Range
if (MyForm.RangeUnits.value != "%"){
MyForm.SQL.value = MyForm.SQL.value + "(RRANGELOWER >= " +
MyForm.LRange.value + " And " +
"RRANGEUPPER <= " + MyForm.URange.value + ") And ";

// Units
MyForm.SQL.value = MyForm.SQL.value + "UNIT_NAME = '" +
MyForm.RangeUnits.value + "' And ";
lRetVal = true;
}

As you can see we are just building a string depending on whether the
user chose a particular piece of data to use in the filtering. We
would then pass this SQL "statement" to our .asp page in a POST and
run aqainst the data connection.

Is it possible to build a where clause somehow using LINQ to SQL on
the fly? I am new to LINQ, and have seem mostly simple examples of
filtering with LINQ. The LINQ I need to run would look something
like:

CStoneDataContext cs = new CStoneDataContext();
var query = from q in cs.CalServices
where q.VENDOR_CODE ==
Convert.ToInt16(ddlVendor.SelectedValue)
select new
{
q.XMID,
q.KSZTAG,
q.Description,
q.SZGROUPNAME,
q.VENDOR_NAME,
q.DEVICE_NAME,
q.KSZSERIALNUM
};

grdInstList.DataSource = query;
grdInstList.DataBind();

However I need to make the where clause dynamic so I can build it
depending on the user's choices. Any ideas, anyone?

Thanks

 

[Marc Gravell]

Holy SQL Injection Batman! Building SQL in javascript at the client is
generally a very bad thing to do...

Re the LINQ aspect, you can compose (i.e. combine) LINQ queries like so:

IQueryable<CalService> query = cs.CalServices;

if(name != null) { // string
query = query.Where(s=>s.Name == name);
}
if(activeOnly) {
query = query.Where(s=>s.IsActive);
}
if(deciveName != null) {
query = query.Where(s=>s.DeciveName == deviceName);
}
//..etc

You can do more powerful things, but you'd need to give a more specific
question. There is a "dynamic LINQ" sample available as well (for
parsing queries as strings into LINQ expressions).

Marc

Marc

 

[Marc Gravell]

Oh, I forgot to add - you can still do the anon-type at the end:

.... previous post ...

var actualQuery = from q in query
select new {... anon type ...};

and use actualQuery for the data-bind. I would also recommed doing any
processing outside of the query, as it is hard to predict what will get
done immediately vs inside the query - for example:

var query = from q in cs.CalServices
where q.VENDOR_CODE ==
Convert.ToInt16(ddlVendor.SelectedValue)

I would write as:

short vendorCode = Convert.ToInt16(ddlVendor.SelectedValue);
var query = from q in cs.CalServices
where q.VENDOR_CODE == vendorCode

These two formations can actually works very differently (or one works,
one doesn't) under some circumstances.

Marc

 

[Plissskin]

Oh, I forgot to add - you can still do the anon-type at the end:

... previous post ...

var actualQuery = from q in query
                  select new {... anon type ...};

and use actualQuery for the data-bind. I would also recommed doing any
processing outside of the query, as it is hard to predict what will get
done immediately vs inside the query - for example:

  var query = from q in cs.CalServices
                         where q.VENDOR_CODE ==
Convert.ToInt16(ddlVendor.SelectedValue)

I would write as:

short vendorCode = Convert.ToInt16(ddlVendor.SelectedValue);
var query = from q in cs.CalServices
                         where q.VENDOR_CODE == vendorCode

These two formations can actually works very differently (or one works,
one doesn't) under some circumstances.

Marc

Thanks Marc,

SQL Injection is not the big boogey man for us, all this is on an
intranet, very secure inside a hardened firewall.

Good info, looking at examples I thought I would need to do
the .Where() method, just couldn't seem to get started on it. Nice
boost...

 

[Marc Gravell]

SQL Injection is not the big boogey man for us, all this is on an

intranet, very secure inside a hardened firewall.

Yes, because people "on the inside" would *never* abuse a system....

Marc

 

[Plissskin]

Yes, because people "on the inside" would *never* abuse a system....

Marc

If you only knew. Outside this office I doubt if there are 2 other
people here who even know what SQL injection is much less how to pull
it off

Good info on the processing (short vendorCode =
Convert.ToInt16(ddlVendor.SelectedValue);
var query = from q in cs.CalServices
where q.VENDOR_CODE == vendorCode )

did not realize that using the Convert (or others) in the LINQ would
cause problems

 

[Marc Gravell]

did not realize that using the Convert (or others) in theLINQwould

cause problems

It isn't a "would" - it is a "could"; both with this and you other
(direct e-mail) question, it depends hugely on the provider. LINQ-to-
Objects, for example, will work with delegates, and it won't matter
much* either way. But if the provider is using query expressions
("Expression") instead of lambdas, then it depends entirely on what
the provider supports. For example, some string operations will work
(including Substring usually), but others (such as overloads accepting
a specific culture) won't - either because the notion of different
cultures makes no sense for that provider (most database engines have
a fixed culture per db), or because it simply hasn't been implemented
(every operation takes work to translate - not everything is supported
by every provider).

Marc

*=give or take some subtleties about "captured variables" and mutating
the variable after query construction but before execution...