Dynamic queries in LINQ to Entities

Question

Using TSQL stored procedures, dynamic queries were a cinch. For example, say I had a reporting application that optionally asked for archived records. The stored procedure would look like so:

DECLARE @sql nvarchar(MAX)
DECLARE @join nvarchar(MAX)
DECLARE @where nvarchar(MAX)

IF @optionalvar1 IS NOT NULL
    SET @where = COALESCE(@where, '') +
    'AND SomeColumn = ' + @optionalvar1 + ' '

IF @optionalvar2 IS NOT NULL
    BEGIN
    SET @join = COALESCE(@join, '') +
   'LEFT JOIN SomeTable s 
    ON st.Column = s.Column '

    SET @where = COALESCE(@where, '') +
    'AND s.SomeColumn = ' + @optionalvar2 + ' '
    END

SET @sql =
'
SELECT
    *
FROM
    StaticTable st
    ' + COALESCE(@join, '') + '
WHERE
    1=1
    ' + COALESCE(@where, '') + '
'

Barring any silly typos, that's how I've done dynamic queries before. For each new optional parameter I add another conditional block and add the necessary join and where code (and adapt the model if I need to also add ordering, etc.). I'm trying to work out how to do this in Entities but am having a rough time of it.

Most links I've found (http://naspinski.net/post/Writing-Dynamic-Linq-Queries-in-Linq-to-Entities.aspx in particular) show how to look for a dynamically changing string using this bit of code:

var data = ctx.table.Where(b => b.branch_id == 5 || b.display == "Hello");

I don't think this works in my example, as I need to dynamically add n-number of additional where clauses and possibly joins depending on what variables are passed in.

I had hoped I could do something simple like:

    var query =
        (from t in ctx.Table
         select t);

    if (optionalvar1)
    {
        query = query.Join('etc');
        query = query.Where('etc');
    }

But didn't make much progress (can't quite figure out the syntax of either to get them to do what I want.

Any ideas? Am I approaching this wrong? Is there a better, simpler solution? I know at the end of the day I could always have a slew of conditionals checking for each possible set of combinations, then generating the entire LINQ query within that block, but the amount of copy-pasta required there is frustrating.

Answer 1

The problem is that you're not using the result of the Where clause. Just calling Where and ignoring the return value won't change what's in query at all. With the joins it could be somewhat tricky, but without them it's easy:

if (someCondition)
{
    query = query.Where(x => x.Text == "Some value");
}

If you can give more information about what your joins need to do, we can probably sort that out too.

Note that as far as I can see, your dynamic SQL version would have been vulnerable to SQL injection attacks, btw - something which isn't the case using LINQ.

Answer 2

you are doing it right actually. remember that the sql will be generated and executed when you request the data.

you can do something like

var v = (from p in Context.User select p);

if (txtLastName.Text.Lenght > 0)
{
    v = (from p in v where p.LastName.Contains(txtLastName.Text) select p);
}

if (txtCity.Text.Lenght > 0)
{
    v = (from p in v
         join q in Context.City on p.City equals q.CityId
         where q.CityName.Contains(txtCity.Text) select p);
}