Having problems converting conditional where clause in LINQ back over to SQL

Question

Hello all.

I've got myself in a bit of a pickle!

I've done a snazzy LINQ statement that does the job in my web app, but now I'd like to use this in a stored procedure:

var r = (from p in getautocompleteweightsproducts.tblWeights
                     where p.MemberId == memberid &&
                              p.LocationId == locationid

                     select p);

            if (level != "0")
                r = r.Where(p => p.MaterialLevel == level);

            if (column == "UnitUserField1")
                r = r.Where(p => p.UnitUserField1 == acitem);

            if (column == "UnitUserField2")
                r = r.Where(p => p.UnitUserField2 == acitem);

return r.OrderBy(p => p.LevelNo).ToList();

However, I can't for the life of me get the conditional where clause to work!!

If someone can point me in the right direction, I'd be most grateful.

Kind regards

Answer 1

Have you tried LinqPAD, I'm pretty sure last time I played with that you could enter "LINQ to SQL" code and see the resulting SQL that produced. Failing that, place a SQL trace/profiler on your code running the LinqTOSQL and find the query being executed in the trace.

Answer 2

Maybe something like this?

SELECT *
FROM dbo.weights
WHERE member_id = @memberid
    AND location_id = @locationid
    AND material_level = CASE WHEN @level = '0' THEN material_level
                              ELSE @level END
    AND @acitem = CASE @column WHEN 'UnitUserField1' THEN unit_user_field_1
                               WHEN 'UnitUserField2' THEN unit_user_field_2
                               ELSE @acitem END
ORDER BY level_no

Answer 3

LukeH's answer will give you the correct rows, but there is something lost when you try to replace a query-generating-machine with a single query. There are parts of that query that are opaque to the optimizer.

If you need the original queries as-would-have-been-generated-by-linq, there are two options.

• Generate every possible query and control which one runs by IF ELSE.
• Use Dynamic sql to construct each query (although this trades away many of the benefits of using a stored procedure).

If you do decide to use dynamic sql, you should be aware of the curse and blessings of it.