Dynamic Linq creation

Question

I am re-writing a query which is created in response to user's entry into text fields in order to offer some protection against SQL injection attack.

SELECT DISTINCT (FileNameID) FROM SurNames WHERE Surname IN
('Jones','Smith','Armitage') 
AND FileNameID IN ( SELECT DISTINCT (FileNameID) FROM FirstNames WHERE FirstName 
IN ('John','William') )

There can be up to 3 other tables involved in this process. The parameter lists can be up to 50-100 entries so building a parameterized query is tedious and cumbersome.

I am trying to create a Linq query which should take care of the parameterization and offer the protection I need.

This gives me what I need

  var surnameValues = new[] { "Jones","Smith","Armitage" };
  var firstnameValues = new[] { "John","William" };

  var result = (from sn in db.Surnames
                from fn in db.FirstNames
                where surnameValues.Contains(sn.Surname) &&
                firstnameValues.Contains(fn.FirstName)
                select fn.FileNameID).Distinct().ToArray(); 

I now need a way to dynamically create this depending upon whether the user has selected/entered values in the surname or firstname text entry boxes?

Any pointers will be gratefully received

Thanks Roger

Answer 1

you could combine all the logic into the query;

var surnameValues = new[] { "Jones","Smith","Armitage" };
var firstnameValues = null;

// Set these two variables to handle null values and use an empty array instead
var surnameCheck= surnameValues ?? new string[0];
var firstnameCheck= firstnameValus ?? new string[0];

var result = (from sn in db.Surnames
            from fn in db.FirstNames
            where
            (!surnameCheck.Any() || surnameCheck.Contains(sn.Surname)) &&
            (!firstnameCheck.Any() || firstnameCheck.Contains(fn.FirstName))
            select fn.FileNameID).Distinct().ToArray(); 

Your query doesn't seem to have a join condition betwwen the Surnames table and the firstNames table?

You could dynamically build the query (as you appear to be doing I cross join I've used SelectMany)

var query=db.Surnames.SelectMany(sn=>db.FirstNames.Select (fn => new {fn=fn,sn=sn}));
if (surnameValues!=null && surnameValues.Any()) query=query.Where(x=>surnameValues.Contains(x.sn.Surname));
if (firstnameValues !=null && firstnameValues.Any()) query=query.Where(x=>firstnameValues.Contains(x.fn.FirstName));
var result=query.Select(x=>x.fn.FileNameID).Distinct();